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
//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
//otherwise we have 3 or more months of data, in that case return rev
ELSEIF NOT ISNULL(ATTR([rev])) THEN
//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
//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
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
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.
Projection Sample jtd edit.twbx.zip 198.8 KB
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!
Richard - Thanks for the look, and you're welcome for the pings!
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.
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?