3 Replies Latest reply on Sep 28, 2018 1:21 AM by Simon Runc

    Tableau Forecast Grand Total?

    Angela.Koh

      Hi all,  I have a case where I need to total up forecast volume, but it seems that tableau does not support that feature.

      Hence I have to do my own forecast calculation and would like some assistance from you guys...

       

      Take for example today is the 13 Sep and I have data up to 12 Sep.

      The goal is to be able to forecast for the remaining month (up to 30 Sep)... Taking data from the previous month ("weekday" and "week" has to match, i.e. Mon = Mon, Tue = Tue, Wk1=Wk1)

      For example, 13 Sep is a Thu and 3rd week of the month, so it should mirror the data of 16 Aug (Thu) which is also the 3rd week of the month. 

      Is this possible?

       

      Pinging some of the experts I see often

      Zhouyi Zhang Simon Runc

        • 1. Re: Tableau Forecast Grand Total?
          Simon Runc

          hi Marcus,

           

          So this is pretty complicated stuff...if this were me i'd look to get this computed in the database and just bring into Tableau. However for the fun of the challenge!

           

          So the first thing I need is to create the Month/Week

          [Week of Month]

          INT((DATEPART('day',[Date])-DATEPART('weekday',[Date])+7)/7)+1

           

          Once I have this I need to create a dimension that tags both the actual data date, and the forecast date with the same date, so I can use an LoD to bring one to another. By using Day of Week, and the Week of Month we created we can do this like so

           

          [Forecast DATE]

          DATE(IF ISNULL([Accepted]) THEN DATEADD('day',(7*([Week of Month]-1))+([Day of Week]-[First Day Month - Last Month]),MAKEDATE(year([Date]),MONTH([Date])-1,1))

          ELSE [Date]

          END)

           

          btw

          [Day of Week] is just

          DATEPART('weekday',[Date])

           

          and

          [First Day Month - Last Month]

          DATEPART('weekday',

          MAKEDATE(year([Date]),month([Date])-1,1)

          )

           

          Once we have both the 16/08/2018 and 14/09/2018 tagged with the same date (18/08/2018) we can use an LoD to bring the data forward with a FIXED LoD

          [Accepted Forecast - Forward]

          IF ISNULL([Accepted])

          THEN {FIXED [Forecast DATE]: SUM([Accepted])}

          ELSE [Accepted]

          END

           

          the ISNULL([Accepted]) is the formulas is just so we can treat actual and forecast dates differently.

           

          Hope some of that makes sense, but I would advise doing this in the datasource.

          • 2. Re: Tableau Forecast Grand Total?
            Angela.Koh

            hi Simon Runc

             

            Thanks for your help. This is exactly what I was looking for, didn't expect it to be possible in Tableau!

            I couldn't replicate what you have done though, even when i had done exactly the same as your calculation.

            Do you mind having a look pls?

             

            Edit* I realised that your working is based on the dates up to 30 Sep (from datasource).

            My actual case does not include these 'future dates' that has no data yet. Is there any tableau function to add these 'future dates'?

             

            Regards

            • 3. Re: Tableau Forecast Grand Total?
              Simon Runc

              hi Marcus,

               

              I realised that your working is based on the dates up to 30 Sep (from datasource).

               

              So yes that is the issue. When we use Tableau's forecast option it creates within the forecast these future dates, which we don't have access to outside the forecast-function. It shouldn't be too hard (via SQL or just Union-ing a CSV/Excel for future dates) so that you have these rows in the data.