-
1. Re: Moving 4 quarter Average not using Window Calculations
Michel Caissie Mar 23, 2017 10:55 AM (in response to Michael Murphy)2 of 2 people found this helpfulMichael,
Check in the attached, if you get the expected result.
The trick here is to put a calculated field on the filter shelf. This changes Tableau's order of operations. The table calculations are now done before the filtering.
(Tableau needs to bring all the data in the engine in order to be able to compute the filter, the side effect being that all other table calculations are now computing on all the data and not only the data in the view after filtering).
You can accomplish this by replacing Order Date by LOOKUP(min([Order Date]), 0 ). (the aggregation could be min,max or attr , it doesn't care it's computed on every row). This table calculation simply returns the Order Date. The drawback is that you cannot use the range filter no more, but you can find alternate ways to accomplish this using a parameter.
In the attached, the final filter is
DATEDIFF('quarter', LOOKUP(min([Order Date]), 0 ) , MIN( [Order Date (dataset last date)] ) ) < [show last x Quarters]
where [Order Date (dataset last date)] is {MAX ([Order Date])}.
and [show last x Quarters] is a parameter.
I also change the columns pill to discrete , to get an axis a bit less confusing.
Michel
-
2. Re: Moving 4 quarter Average not using Window Calculations
Michael Murphy Mar 23, 2017 12:29 PM (in response to Michel Caissie)THANK YOU!