1 Reply Latest reply on Oct 3, 2018 10:19 AM by Yuriy Fal

    Rolling Sum with Condition

    Andrew Williams

      Hello Forum,

       

      I'm trying to create table/chart that shows the total of new ("Standing Start" Sales over a rolling 52-week window.  In the dummy dataset and workbook attached, the worksheet "1. Standing Start New Sales (Static)") illustrates the logic/definitions: the bars show the sum of sales by week from new (standing start) customers within the 52-week window spanning from ISO week 201733 to 201832; the line shows the running total of the sales.  Worksheet "Crosstab 1" shows the data for the chart.

       

      In the dataset, "New Sales" are defined as customers with a start week within 52 weeks of the reporting/ISO week (in the example where 201832 is the report week, customers with a start week between 201733 and 201832 are included).  The running total of Sales between those same weeks is taken, with the value of interest being the running total value in the final week (5,046,546 in the example).

       

      What I need is to display the corresponding 52-week total for each reporting week (so the values would be circa 5 million each week). 

       

      Hope this makes sense!  I'm still very much learning Tableau so any help you can give on this will be very much appreciated.

       

      Thanks in advance.

        • 1. Re: Rolling Sum with Condition
          Yuriy Fal

          Hi Andrew,

           

          Your definition of a 'NEW' Customer implies

          that each those Customer should be identified

          as having Sales inside a 52-Week Moving Window.

           

          Though Tableau has Moving Table Calculations --

          thanks to a pair of arguments inside each WINDOW_...() function --

          they are of little use in this particular case, because the view

          should have a granularity (Visual Level Of Details, or VizLOD)

          of [Cust No] AND [ISO Week] (those Dims should be on Details).

           

          A workaround exists, but it needs some preparation of the datasource.

          Namely, a Range Join to the ISO Week (calendar) table is required.

          With the HyPer engine (as of version 10.5), non-equi joins are possible,

          so I've prepared a distinct datasource for this view using a Join Calculation

          and a supplementary Filter -- to work around the Tableau HyPer engine restriction

          on using the single field twice in Join conditions (even if they are Join Calculations).

           

          Essentially, a Range Join is multiplying rows (of the original datasource) --

          making possible to aggregate them (group by the ISO Week calendar table column)

          to get the same result(s) as the 'Moving Window' calculation(s) would do.

           

          This is an old-school (pre SQL-2003) approach to either Running or Moving calculations.

           

          Please find the attached.

           

          Yours,

          Yuri