10 Replies Latest reply on Jan 15, 2013 7:50 AM by Jonathan Drummey

# % difference using a calculated field and a date filter

The key metric I'm looking at here is the moving average of the profit ratio.

Thanks to a nice date filter, I am displaying only 2011 through 2012, but my moving average calculation takes into account data points before 2011 - so my January 2011 moving average score is different to my actual January 2011 profit ratio.

What I would like to do now is display a % difference in the moving average profit ratio with January 2011 as the baseline and the % difference being the difference from January 2011.

Unfortunately the only calculation I seem to be able to perform that does a % difference looks at the entire table...i.e. at the data I have hidden with my date filter...

• ###### 1. Re: % difference using a calculated field and a date filter

Hi Michael,

I saw this on Shawn Wallwork's Tableau Forums Digest. I can see what's happening, this has to do with the order of filter application & calculations.

- 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?

Jonathan

• ###### 2. Re: % difference using a calculated field and a date filter

Hi Jonathan -

My apologies for not replying sooner. Thanks for your reply - and also to Shawn for mentioning this thread in his brilliant digest.

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.

Thanks!

• ###### 3. Re: % difference using a calculated field and a date filter

Hi Michael,

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%.

Jonathan

• ###### 4. Re: % difference using a calculated field and a date filter

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.

Mike

• ###### 5. Re: % difference using a calculated field and a date filter

Hi Michael,

That's pretty much in line with what I was thinking, nice work!

Jonathan

• ###### 6. Re: % difference using a calculated field and a date filter

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...

• ###### 7. Re: % difference using a calculated field and a date filter

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)?

• ###### 8. Re: % difference using a calculated field and a date filter

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.

Jonathan

1 of 1 people found this helpful
• ###### 9. Re: % difference using a calculated field and a date filter

Awesome - this seems to be the solution!

Thanks Jonathan!

• ###### 10. Re: % difference using a calculated field and a date filter

You're welcome!