1 Reply Latest reply on Sep 14, 2018 7:50 PM by swaroop.gantela

    WINDOWS_SUM / WINDOWS_AVG behavior without using Table Calculations?

    Jacob Olsby

      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-14Jan-14Feb-131FALSE$14,237
      Jan-14Feb-14Mar-132FALSE$4,520
      Jan-14Mar-14Apr-133FALSE$55,691
      Jan-14Apr-14May-134FALSE$28,295
      Jan-14May-14Jun-135FALSE$23,648
      Jan-14Jun-14Jul-136FALSE$34,595
      Jan-14Jul-14Aug-137FALSE$33,946
      Jan-14Aug-14Sep-138FALSE$27,909
      Jan-14Sep-14Oct-139FALSE$81,777
      Jan-14Oct-14Nov-1310FALSE$31,453
      Jan-14Nov-14Dec-1311FALSE$78,629
      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,
      Jake