Running Total for 4 weeks Help!

[Anonymous]

Hi,

I have to calculate Running total for past 4 weeks in an expression in a Pivot table.

If 201004(YYYYWK) is chosen then it should give running total for previous 4 weeks i.e. 201001 to 201004

If 201005(YYYYWK)  is chose then it should give running total for previous 4 weeks i.e. 201002 to 201005

I get correct values using RangeSum(Above(Sum(Value),0,4)) in a Straight Table

But I am not able to replicate the same in PIVOT Table, users will have the flexibility to pivot the table.

Sample data

YYYYWK	Value	Running Total Prev 4 Weeks
201001	1	1
201002	2	2+1
201003	3	3+2+1
201004	4	4+3+2+1
201005	5	5+4+3+2
201006	6	6+5+4+3
201007	7	7+6+5+4
201008	8	8+7+6+5
201009	9	9+8+7+6
201010	10	10+9+8+7
201011	11	11+10+9+8
201012	12	12+11+10+9
201013	13	13+12+11+10
201014	14	14+13+12+11
201015	15	15+14+13+12
201016	16	16+15+14+13
201017	17	17+16+15+14

I appreciate any help on this.

Thank you

[qlik4asif]

Try this

=Aggr(RangeSum(Above(Sum(Value),0,4)),Date )

[jharke · Accepted solution]

sum( Aggr( RangeSum( Above( Sum( {$<YYYYWK>}  Value),0,4)),YYYYWK ))

See attachment

[ychaitanya]

This should work actually.. Sairta Please check and confirm

[Anonymous]

Thank you Chaitanya..It worked perfectly !!

[Anonymous]

@jharkeCan you help in calculating the same expression at different aggregation level.

Meaning if I want to calculate rolling 4 months at product level. How do I tweak the above expression. I tried AGGR with NODISTINCT, it isn't working. This expression breaks as soon as I add quarters alongwith Weeks.