Window Sum with Condition in Tableau

Question

Using Tableau 10.5 I'm trying to create table/chart that shows the total of new ("Standing Start") Sales over a rolling 52-week window. The chart image below shows 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.

The following image shows the last few weeks of data for the chart.

"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) and the bars would be relatively flat (allowing for sales growth, etc). My specific issue is that I can't figure out how to define the window (i.e. New accounts only) when doing the window/moving calculation.

The following example table shows Sales made between 201824 and 201827 from accounts opened in the same period. In week 201824, only accounts opened in 201824 have any sales (value of 1). In week 201825, there are now 2 weeks' of accounts: those opened in week 201824 and those opened in week 201825, making sales of 2 and 1 respectively. Each subsequent week has an extra week's worth of accounts contributing to the sales total of 20.

+--------+--------+--------+--------+

| 201824 | 201825 | 201826 | 201827 |

+--------+--------+--------+--------+

|        |        |        |      1 |

|        |        |      1 |      2 |

|        |      1 |      2 |      3 |

|      1 |      2 |      3 |      4 |

+--------+--------+--------+--------+

To continue with the example, the next table shows similar data but with the time period moved by 1 week:

+--------+--------+--------+--------+

| 201825 | 201826 | 201827 | 201828 |

+--------+--------+--------+--------+

|        |        |        |      2 |

|        |        |      2 |      3 |

|        |      2 |      3 |      4 |

|      2 |      3 |      4 |      5 |

+--------+--------+--------+--------+ 

The sum of sales in this table is 30: accounts opened in week 201825 contribute 14 (2 + 3 + 4 + 5), those opened in the second week contributed 9 (2 + 3 + 4), and so on.

These two tables describe the methodology I'm looking for, and the results I'm looking for will be something like the following:

+--------+--------+

| 201827 | 201828 |

+--------+--------+

|     20 |     30 |

+--------+--------+ 

If I were doing something similar in SQL, the following gives me the result I need:

;with d AS 

(

    select s.ISOWk, a.OSW as StartWeek, sum(s.Sales) as Sales

    from accs a

    join sales s

        on a.ID = s.ID

    where s.ISOWk >= 201701

        and a.OSW >= 201701

    group by s.ISOWk, a.OSW

)

select c.ISOWk, sum(case when d.ISOWk between FYS and c.ISOWk and StartWeek between FYS and c.ISOWk then Vol else 0 end) as Rolling_ss_vol

from d

cross join (select distinct ISOWk, FYS from tblCalendar c where ISOWk between 201801 and 201835) c

group by c.ISOWk

order by ISOWk 

 Where FYS (front year start) is linked to the ISO week and indicates when the current sales period began (ie. 52 weeks prior).

Answer 1

Use the following formulas

DATEADD('week',INT(MID(STR([Start Week]), 5,2))-1,DATE("01/01/"+(MID(STR([Start Week]), 1,4))))

{fixed [Cust No] : min([start_week_date])} 

if datediff('week',[cust start], [iso_week_date])<52 then [Sales] end 

window_sum(sum([Sales within 52]),-51,0) 

If you want to learn more about it Tableau, then go through this Tableau Training for more insights.