Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
sum( Aggr( RangeSum( Above( Sum( {$<YYYYWK>} Value),0,4)),YYYYWK ))
See attachment
Try this
=Aggr(RangeSum(Above(Sum(Value),0,4)),Date )
sum( Aggr( RangeSum( Above( Sum( {$<YYYYWK>} Value),0,4)),YYYYWK ))
See attachment
This should work actually.. Sairta Please check and confirm
Thank you Chaitanya..It worked perfectly !!
@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.