4 Replies Latest reply on Dec 12, 2014 2:10 AM by Karthik Venkatraman

    Projections or simple forecasts in Tableau

      I’m trying to find a way in Tableau to show projections out to year end based on a rolling three month average.  I want to do the calculations in Tableau and avoid creating the projections outside of Tableau.  I’m not seeing a way to even get started on this, and so far haven’t found anything online that addresses the problem.  Can you give me some guidance?  Have attached a sample data set and workbook.

       

        • 1. Re: Projections or simple forecasts in Tableau
          Jonathan Drummey

          Hi Lyle,

           

          I thought this was going to be a relatively easy case of projection similar to this post here, http://community.tableau.com/thread/117629, however it's not. I was stubborn enough to figure something out, it's a hack but it works. Maybe someone like Richard Leeke would have another idea.

           

          The first part is to generate a list of months, I have one around that I use in other projection and reporting, you canuse your own or generate one in SQL. That becomes the primary datasource, with the revenue data in the secondary datasource. Now Tableau will have a list of months going out to the end of the year that it can use.

           

          The next part is where things get really messy. In the sample data you indicated that you wanted to return actuals when there was an actual, and then have the projections be based on the prior three months of actuals or projections. So the projection calculation is not a linear function like in the post I linked to, it's an iterative calculation.

           

          And Tableau doesn't have much for iterative functions.

           

          Tableau has a PREVIOUS_VALUE() function that returns the previous value of the same variable, so you can use it for a form of iteration. However, in your case you need a three month rolling average and PREVIOUS_VALUE() can't go two or three months back. There's a LOOKUP() function that will let you go back and forth across the data for fields, but it returns a circular reference error if you try to set up a calculated field to LOOKUP() itself.

           

          Since there are only twelve months in a year, it was feasible to create a series of iterative calculations, each feeding the next, to generate the correct set of values.

           

          Here's the first calc:

           

          // if there's only January, then return the rev for January for all rows

          IF [index of last date of rev] = 1 THEN

              PREVIOUS_VALUE(SUM([rev]))

          //for January & Feb, return the data for first two rows or the average of the 2 months

          ELSEIF [index of last date of rev] = 2 THEN

              IF INDEX() <= 2 THEN

                  SUM([rev])

              ELSE

                  WINDOW_SUM(SUM([rev]),FIRST(),FIRST()+1)/2

              END

          //otherwise we have 3 or more months of data, in that case return rev

          ELSEIF NOT ISNULL(ATTR([rev])) THEN

              SUM([rev])

          //now we're into the first month w/out data, so look back at three months to generate a projection

          ELSEIF NOT ISNULL(LOOKUP(ATTR([rev]),-1) ) THEN

              WINDOW_SUM(SUM([rev]),-3,-1)/3

          //next into the 2nd month w/out data

          //use the last two months with data and the previous value of iterate rev 1

          //unfortunately, we can't get the last N values of iterate rev 1, so we need more variables

          //move on to iterate rev 2

          ELSEIF NOT ISNULL(LOOKUP(ATTR([rev]),-2)) THEN

              (WINDOW_SUM(SUM([rev]),-3,-2) + PREVIOUS_VALUE(0))/3

          END

           

          Now, here's iterate rev 2:

           

          //we start with getting all the existing values of iterate rev 1

          IF NOT ISNULL([iterate rev 1]) THEN

              [iterate rev 1]

          //then for the first month without data or a projection, we make up a projection using the last three values

          ELSEIF NOT ISNULL(LOOKUP([iterate rev 1],-1) ) THEN

              WINDOW_SUM([iterate rev 1],-3,-1)/3

          //then we can generate a 2nd projection using the last two values and the previous value of iterate rev 2

          ELSEIF NOT ISNULL(LOOKUP([iterate rev 1],-2)) THEN

              (WINDOW_SUM([iterate rev 1],-3,-2) + PREVIOUS_VALUE(0))/3

          END

           

          Then we move on all the way up to iterate rev 5. That's the calculation that you'd use to show the actuals and projection, and set the Compute Using to date (i.e. the month). That way when you go to use the calc in a line chart like in the "line chart" view, the calc and all its subcalculations will still work.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Projections or simple forecasts in Tableau
            Richard Leeke

            I don't think you're missing an easy way, Jonathan - or at least I can't see it.

             

            The issue is that this really needs recursive functions, I think.

             

            I did have a quick think about whether I could do more of the work in a custom SQL connection, or with RAWSQL - but once again, the need for recursion makes that hard. About the only way I know of doing recursive calculations in SQL is in some circumstances you can do it with Common Table Expressions - but you can't use CTEs in a Custom SQL connection (unless perhaps if you hide them in a view).

             

            Unlike you, Jonathan, I gave up at that point.

             

            By the way - I like this service you're providing of picking out the gnarly questions and pinging me to have a look, Jonathan - I just don't have time to do more than skim the subject lines to see if something catches my eye at the moment. Thanks for that!

            • 3. Re: Projections or simple forecasts in Tableau
              Jonathan Drummey

              Richard - Thanks for the look, and you're welcome for the pings!

              • 4. Re: Projections or simple forecasts in Tableau
                Karthik Venkatraman

                Hi Jonathan,

                 

                I saw your reply for this post. I also need a assistance related to a forecasting technique i need. I have posted my question in this forum and below is the link for that.

                 

                Forecast Data for next 3 month using Moving Average Meathod

                 

                I tried to implement your codes that you have posted here but i was not able to achieve what i need.

                 

                Can you plese help me?