2 Replies Latest reply on Jan 10, 2019 2:40 PM by jacob.olsby

    WINDOWS_SUM / WINDOWS_AVG behavior without using Table Calculations?


      Hi all,


      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 DateMonth of Order DateMonth of End of Rolling DateDate Difference between Min and Current Date12 Month Window?Rolling Sum Valid FlagWINDOWS_SUM?Sales
      Jan-14Dec-14Jan-141201/01/2014 to 12/01/2014TRUE484,247$69,546
      Jan-14Jan-15Feb-141302/01/2014 to 01/01/2015TRUE502,422$18,174
      Jan-14Feb-15Mar-141403/01/2014 to 02/01/2015TRUE500,136$11,951
      Jan-14Mar-15Apr-141504/01/2014 to 03/01/2015TRUE534,342$38,726
      Jan-14Apr-15May-141605/01/2014 to 04/01/2015TRUE512,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!


      Many thanks,