topic Re: Running Total for 4 weeks Help! in QlikView

Running Total for 4 weeks Help!

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Running Total for 4 weeks Help!

qlik4asif — Fri, 16 Feb 2018 05:45:35 GMT

Try this

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

Re: Running Total for 4 weeks Help!

jharke — Fri, 16 Feb 2018 13:16:19 GMT

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

See attachment

Re: Running Total for 4 weeks Help!

ychaitanya — Fri, 16 Feb 2018 14:34:21 GMT

This should work actually.. Sairta Please check and confirm

Re: Running Total for 4 weeks Help!

Anonymous — Fri, 16 Feb 2018 16:40:02 GMT

Thank you Chaitanya..It worked perfectly !!

Re: Running Total for 4 weeks Help!

Anonymous — Wed, 28 Feb 2018 18:53:57 GMT

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