accumulation within a month

[let12]

Hello community,

I would like to create an expression, which accumulates the weekly quantity by month.

Every 4 weeks the quantity should be accumulated. 

I have a sample of data:

Year	Week	sum(Quantity)	Weekly accumulated quantity by month
2024	1	0.00	0.00
2024	2	0.74	0.74
2024	3	2.32	3.06
2024	4	2.60	5.66
2024	5	3.08	3.08
2024	6	3.28	6.35
2024	7	3.38	9.74
2024	8	4.36	14.10
2024	9	3.98	3.98
2024	10	6.56	10.55
2024	11	6.85	17.40
2024	12	6.92	24.31
2024	13	5.50	5.50
2024	14	5.12	10.62
2024	15	4.91	15.54
2024	16	5.93	21.47
2024	17	3.49	3.49
2024	18	5.84	9.33
2024	19	1.99	11.32
2024	20	5.29	16.61
2024	21	4.45	4.45
2024	22	4.37	8.82
2024	23	5.02	13.84
2024	24	3.94	17.79
2024	25	5.70	5.70
2024	26	4.90	10.60
2024	27	5.23	15.83
2024	28	5.90	21.73

so far I tried this in QS:

Create a line chart

Dimension:

- Week

Expression:

- sum({<Year = {$(=Max(Year))} >} Quantity)

In the Line charts properties -> Measure -> Modificator -> Select Accumulation -> Step 4.

This method does not bring me the expected values.

Could anyone help me out here?

Thank you.

[Kushal_Chawda]

@let12  I actually never understood how modifier accumulation works but without modifier you can use below expression

=sum(aggr(rangesum(above(total sum({<Week>}Quantity),0,mod(RowNo(total),5)+1)),(Week,(NUMERIC,ASCENDING))))

[let12]

Hello @Kushal_Chawda , thank you for your reply. Your expression works fine for the Weeks 1-8, 15-18, 25-28, but for the other weeks sometimes the accommulation goes in 5 steps, which should be always in 4 steps. For example the accumulation starting from Week 5, has a step of 5 instead of 4. The accumulation goes from Week 4 until Week 9 while it should only go to Week 8 and from Week 9 the accumulation should repeat again.

The accumulation should start with

Week 1 Quantity = 3.98,

Week 5 Quantity = 3.08

Week 9 Quantity = 3.98

Week 13 Quantity =  5.5

Week 17 Quantity = 3.49

Week 21 Quantity = 4.45

Week 25 Quantity =  5.7

[Kushal_Chawda · Accepted solution]

@let12  I have updated formula as below

=sum(aggr(rangesum(above(total sum({<Week>}Quantity),0,if(mod(RowNo(total),4)=0,4,mod(RowNo(total),4)))),(Week,(NUMERIC,ASCENDING))))

[Kushal_Chawda]

@let12  or better

=sum(aggr(rangesum(above(total sum({<Week>}Quantity),0,mod(RowNo(total)-1,4)+1)),(Week,(NUMERIC,ASCENDING))))

[let12]

Hello @Kushal_Chawda , thank you for your solution. It works !

[seanbruton]

Superb, very helpful solution!!!