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

# Rolling Sum with Condition

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.

• ###### 1. Re: Rolling Sum with Condition

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.