
1. Re: Projections or simple forecasts in Tableau
Jonathan Drummey May 4, 2012 5:56 PM (in response to lyle.hild)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

Projection Sample jtd edit.twbx.zip 198.8 KB


2. Re: Projections or simple forecasts in Tableau
Richard Leeke May 4, 2012 10:43 PM (in response to Jonathan Drummey)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 May 5, 2012 8:39 PM (in response to Richard Leeke)Richard  Thanks for the look, and you're welcome for the pings!

4. Re: Projections or simple forecasts in Tableau
Karthik Venkatraman Dec 12, 2014 2:10 AM (in response to Jonathan Drummey)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?