I have a data set that is updated monthly. Each month new data is appended to the data set.
I want to have a MOVING SUM of our SUM(Sales) by the previous 12 periods, inclusive.
Also, only if there are in fact 12 previous inclusive periods, else NULL.
I would like to be able to filter to a specific month or exclude certain months and still see the MOVING SUM of SUM(Sales) without a decrease in value. (This makes me think LODs are involved.)
I am getting the behavior very close to what I want with the WINDOW_SUM Table Calculation.
However, the Table Calculation means that I cannot filter out months as it will decrease the overall MOVING SUM. I would like the ability to filter to say a single month, and get its 12 Month Moving SUM.
- ex. Dec-14 had a Sales of 69,546 and a Moving Sum of 484,247.
TLDR; Is it possible to get WINDOW_SUM behavior without using Table Calculations? Is there perhaps some LOD logic I could leverage perhaps?
I have included a basic table below with said logic behavior:
|Minimum Date||Month of Order Date||Month of End of Rolling Date||Date Difference between Min and Current Date||12 Month Window?||Rolling Sum Valid Flag||WINDOWS_SUM?||Sales|
|Jan-14||Dec-14||Jan-14||12||01/01/2014 to 12/01/2014||TRUE||484,247||$69,546|
|Jan-14||Jan-15||Feb-14||13||02/01/2014 to 01/01/2015||TRUE||502,422||$18,174|
|Jan-14||Feb-15||Mar-14||14||03/01/2014 to 02/01/2015||TRUE||500,136||$11,951|
|Jan-14||Mar-15||Apr-14||15||04/01/2014 to 03/01/2015||TRUE||534,342||$38,726|
|Jan-14||Apr-15||May-14||16||05/01/2014 to 04/01/2015||TRUE||512,847||$34,195|
I would like to be able to filter out the [Rolling Sum Valid Flag] where it is FALSE, without decreasing the [WINDOWS_SUM?] column, i.e. not using a WINDOWS_SUM function in Tableau, which is in context to the view.
I have attached my .twbx file using the superstore dataset, and an excel export illustrating the problem as well.
I would appreciate any insight anyone could share!