Announcements
cancel
Showing results for
Did you mean:
Contributor

## Rolling 4 Week totals with two expressions and percentage

I am looking to create a Rolling 4 week total for Leads, Sales and Percentage as in this example. In expression area of a chart, the Accumulate Step Back can create a version of Rolling Leads but includes in week 1= 100, week 2= 200, week 3= 305, is there a way to hide/zero/null those figures in the chart? Also the Rolling % is an issue as it is meant to be based on the rolling 4 week totals and my attempts have not given me the expected percentages. This is a simplified example of the data set which runs from a script that in turn is based on a rolling year.

I am grateful for any help.

 Week 06/11/22022 13/11/2022 20/11/2022 27/11/2022 04/12/2022 11/12/2022 18/12/2022 25/12/2022 Leads 100 100 105 110 115 120 115 105 Sales 40 35 55 60 50 70 60 40 % 40% 35% 52% 55% 43% 58% 52% 38% Rolling Leads 415 430 450 460 455 Rolling Sales 190 200 235 240 220 Rolling % 46% 47% 52% 52% 48%

Labels (2)

• ### General Question

1 Solution

Accepted Solutions
Creator II

Would something like the following work?

In the table below, the last three columns have the following calculations:

Rolling Sales = if(aggr(rangecount(above(Sales, 0, 4)), Week) = 4, aggr(rangesum(above(Sales, 0, 4)), Week))

Rolling % = Rolling Sales / Rolling Leads = if(aggr(rangecount(above(Sales, 0, 4)), Week) = 4, aggr(rangesum(above(Sales, 0, 4)), Week)) / if(aggr(rangecount(above(Leads, 0, 4)), Week) = 4, aggr(rangesum(above(Leads, 0, 4)), Week))

Creator II

Would something like the following work?

In the table below, the last three columns have the following calculations: