topic Re: accumulation within a month in QlikView

accumulation within a month

let12 — Mon, 30 Sep 2024 11:40:13 GMT

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.

Re: accumulation within a month

Kushal_Chawda — Mon, 30 Sep 2024 14:34:32 GMT

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

Re: accumulation within a month

let12 — Tue, 01 Oct 2024 09:06:41 GMT

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

Re: accumulation within a month

Kushal_Chawda — Tue, 01 Oct 2024 09:22:16 GMT

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

Re: accumulation within a month

Kushal_Chawda — Tue, 01 Oct 2024 09:30:26 GMT

@let12 or better

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

Re: accumulation within a month

let12 — Tue, 01 Oct 2024 11:22:10 GMT

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

Re: accumulation within a month

seanbruton — Thu, 17 Oct 2024 15:02:36 GMT

Superb, very helpful solution!!!