Tableau GAP forecasting formula displaying monthwise quarterly

0 votes

I've excel datasource (as greyed below) and I want to show 4 calculated fields (CF) as (red highlighted) for 4 "Team" as highlighted in yellow. Output required is green highlighted.

enter image description here

Output I got as shown in image:

image

Problems faced:

  1. Not able to find right answer for Forecast and GAP. Formuales used are below:
  2. How to represent ONLY (# of Sales) monthwise but display all 4 CFs at QUARTER levels as shown in the green above.

    # of sales = COUNT([Productname])
    
    Days Passed = DAY([Sale Date])-day(TODAY())
    
    Forecast = (([#ofSales]))/(avg([DaysPassed]))*avg([TotalDays])
    
    SalesTarget (copy) = { FIXED [Team], quarter([Sale Date])=1 : avg([SalesTarget])}
    
    GAP = (sum([SalesTarget (copy)]))-[Forecast]
    
    TotalDays = if quarter([Sale Date])=1 then 91 
        ELSEIF quarter([Sale Date])=2 then 92
        ELSEIF quarter([Sale Date])=3 then 92
        ELSEIF quarter([Sale Date])=4 then 92 end
    
    SalesTarget = if [Team]="USA Small" and quarter([Sale Date])=1 then 10
        ELSEIF [Team]="USA Big" and quarter([Sale Date])=1 then 5
        ELSEIF [Team]="UK Small" and quarter([Sale Date])=1 then 3
        ELSEIF [Team]="UK Big" and quarter([Sale Date])=1 then 3
        END
    
    SalesTarget (copy) = { FIXED [Team], quarter([Sale Date])=1 : avg([SalesTarget])}
    

I've tried multiple workarounds but couldn't find solution. I can't attach any file in Stack Overflow. So added just few code samples, I'd tried out of many.

Mar 29, 2022 in Tableau by Vaani
• 7,070 points
616 views

1 answer to this question.

0 votes

Please refer to the following steps for reference.

pastedImage_0.png

pastedImage_1.png

I hope this helps you

Master data visualization with our Tableau Training and unlock powerful insights from your data.

answered Apr 5, 2022 by Neha
• 9,020 points

Related Questions In Tableau

0 votes
1 answer
0 votes
1 answer

Displaying Aggregations across bar chart in Tableau

Hi Sindhu, 1.You can use average lines to ...READ MORE

answered Mar 8, 2019 in Tableau by Cherukuri
• 33,030 points
2,154 views
0 votes
0 answers

Error Running python (forecasting) in Tableau

I'm quite new to this system as ...READ MORE

Mar 31, 2022 in Tableau by Vaani
• 7,070 points
468 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
9,942 views
0 votes
1 answer

How to exclude weekend (Sunday only) to DATEDIFF in Tableau?

To exclude Sundays from the DATEDIFF calculation ...READ MORE

answered Jul 3, 2023 in Tableau by anonymous
• 1,420 points
1,167 views
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
1,621 views
0 votes
1 answer

How to calculate profit when cost and revenue are in same column?

You can create the following calculated fields: Profit: SUM(IIF([account_category] ...READ MORE

answered Apr 10, 2018 in Tableau by ffdfd
• 5,550 points
1,214 views
0 votes
1 answer

Looking for YOY YTD formula that works with fiscal years in tableau

If you computed what you're showing in ...READ MORE

answered Apr 12, 2022 in Tableau by Neha
• 9,020 points
1,382 views
0 votes
1 answer

How to upload data to Tableau Server in Python

Then install the tableau-api-lib utility for Python ...READ MORE

answered Mar 4, 2022 in Tableau by Neha
• 9,020 points
2,230 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP