- For the moving calculation to work, you have a table calc filter on the date.
- For the difference from previous calc to work, you have it set to compare to First. Because the table calc filters are applied after table calculations are done, the First row is then January 2009, the first row in the data set.
There are solutions for this, the basic process is to identify the reference date(s), and return offsets to that date to every row in the filtered data so the lookup for the difference from previous can be accurate. I have a couple of questions - do you want every comparison to be to January 2011? Is that because it's the beginning of the filtered date range, or do you want to pick a date? Will that reference date change a lot (like every month), or just once a year? Do you want the reference date changed automatically or do you want the user to be able to choose?
Hi Jonathan -
My apologies for not replying sooner. Thanks for your reply - and also to Shawn for mentioning this thread in his brilliant digest.
Here are my answers to your questions:
January 2011 is selected since it is the beginning of the range; the beginning of the range could be any month, in practice, but it will not change regularly (most likely every 12 months). I don't think I want the user to modify that date at the moment.
I hope that answers your questions...
I set up both the moving average calc and the difference calcs to have an Advanced... Compute Using of Month of Order Date. This causes them to partition on the State, which I'm guessing is what you'd wanted since you filtered to only one state. In other words, the calculations will be performed independently for each state.
Then for the difference calc pill, I right-clicked on it and set the Relative to January 2011.
Also, the Moving Average for January 2011 was -1.23%, not 1.23%, so the difference reported for February is 1.98%.
Hi Jonathan -
Your solution works like a treat.
I actually came up with a solution which works for my real-world problem - the solution is slightly different to yours.
My real world calculation is already a table calculation - let's call it Profit Ratio Rolling Average.
Since it's already a table calc, I cannot do what you have suggested above.
So my 5 calculations that make this work are:
Rolling average (this is a parameter)
Date 1 (this is a parameter)
Profit Ratio rolling average - WINDOW_AVG([Profit Ratio], -[Rolling Average],0)
# of months between today and baseline date - DATEDIFF('month',TODAY(),[Date 1])+[Rolling Average]+1
Profit Ratio Rolling Average v Baseline - ZN([Profit Ratio Rolling Average]) - LOOKUP(ZN([Profit Ratio Rolling Average]), LAST()+[ # of months between today and baseline date])
See attached for my solution.
Thanks for your help!!!
That's pretty much in line with what I was thinking, nice work!
One thought about this...
If one of my dimensions is missing a datapoint, then the calculation stretches back a further month (so instead of it being a moving 12 month average, it's actually a moving 12 datapoint average)...most of my dimensions have data for every month, but some don't.
With my constraints (i.e. the fact I have to have the moving average pre-calculated), is there any way I can limit the calculation back to the 12th previous month, even if data is missing?
Hope that isn't too confusing...
I guess my last question is really asking if you can show me (Jonathan?!) how to specify a date, rather than just go back 12 months (or 12 datapoints)?
1 of 1 people found this helpful
There's a hard way of doing this by making the table calculations aware of the month with extra IF statements.
The (mostly) easier way is to use Tableau's Show Missing Values feature, which is accessible from the context menu for Date and Bin pills. The thing is, you can't use a regular LOOKUP() filter for the date anymore, because that table calc filter is applied before Tableau does the domain padding for the missing values. Also, Tableau doesn't let us direclty access the values that are domain padded, we have to infer them from the data. So, I built two calcs that generate a date that can then be used as a working table calc filter.
I also think the LOOKUP() you were using for the comparison to baseline is not correct, since it was based on TODAY() and that would make the baseline value pulled change depending on the day the user looked at the data, so the setting of the Comparison Date parameter would not be accurate. I changed the lookup to do a DATEDIFF based on the generated date and that is returning accurate results, you can see that in the crosstab.
Awesome - this seems to be the solution!