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 in Tableau by Vaani
• 7,020 points
27 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

answered Apr 5 by Neha
• 8,560 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,010 points
946 views
0 votes
0 answers

Error Running python (forecasting) in Tableau

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

Mar 31 in Tableau by Vaani
• 7,020 points
39 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,230 points
8,054 views
0 votes
0 answers

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

My current formula is : DATEDIFF('day', MIN([Date]),MAX([Date])). How do ...READ MORE

Apr 7 in Tableau by Vaani
• 7,020 points
25 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,230 points
817 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 sdfg
• 5,550 points
474 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 in Tableau by Neha
• 8,560 points
61 views
0 votes
1 answer

How to upload data to Tableau Server in Python

https://www.tableautim.com/playlist-video/tableau-tds-and-tdsx-files/csv-to-tdsx Then install the tableau-api-lib utility for Python ...READ MORE

answered Mar 4 in Tableau by Neha
• 8,560 points
159 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP