Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

Running Total for 4 weeks Help!

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

YYYYWKValueRunning Total Prev 4 Weeks
20100111
20100222+1
20100333+2+1
20100444+3+2+1
20100555+4+3+2
20100666+5+4+3
20100777+6+5+4
20100888+7+6+5
20100999+8+7+6
2010101010+9+8+7
2010111111+10+9+8
2010121212+11+10+9
2010131313+12+11+10
2010141414+13+12+11
2010151515+14+13+12
2010161616+15+14+13
2010171717+16+15+14

I appreciate any help on this.

Thank you

1 Solution

Accepted Solutions
Highlighted
Contributor

Re: Running Total for 4 weeks Help!

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

See attachment

View solution in original post

5 Replies
Highlighted
Contributor II

Re: Running Total for 4 weeks Help!

Try this

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

Highlighted
Contributor

Re: Running Total for 4 weeks Help!

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

See attachment

View solution in original post

Highlighted
Contributor III

Re: Running Total for 4 weeks Help!

This should work actually.. Sairta Please check and confirm

Highlighted
New Contributor II

Re: Running Total for 4 weeks Help!

Thank you Chaitanya..It worked perfectly !!

Highlighted
New Contributor II

Re: Running Total for 4 weeks Help!

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