Rolling 4 Week totals with two expressions and percentage

[SimonGoodman1]

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%

[KGalloway · Accepted solution]

Would something like the following work?

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

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

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))