Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Accumulation in Pivot Tables

Hello all

I am trying to accumulate values in a pivot table. I am using the BEFORE function to calculate the previous value + the current value.

The problem I have seems to be the order of the dimensions. I need the pivot to look like example 1 (examples attached) but I can only get the correct numbers if I set the order like in example 2???

Can anyone think of a solution.

Thankyou for you time.

4 Replies
stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

You need to use Before to generate a range of values and then RangeSum to add them all together:

   RangeSum(Before(total Sum(Value), 0, ColumnNo(total)))

The ColumnNo function has (total) because otherwise it resets at the change of year.

Regards,

Stephen

Anonymous
Not applicable
Author

Hi Stephen

Thanks for the reply. I have sort of understood your solution, however it still doesn't seem to have completely solved the problem. It seems to be ignoring the Year column altogther.

The value in each month is 1 so the table should read;

Apr 2011 = 1

Apr 2012 = 1

May 2011 = 2

May 2012 = 2

Jun 2011 = 3

Jun 2012 = 3 etc etc

I have updated the file. Could you amend the file with what you mean.

Many thanks

Robbie

stephencredmond
Luminary Alumni
Luminary Alumni

Aha!  Sorry, I understand your problem now.

Only way that I can think of to do this off the top of my head is to add a load of Befores:

Sum(Value)

+Alt(Before(Total Sum(Value), 2), 0)

+Alt(Before(Total Sum(Value), 4), 0)

+Alt(Before(Total Sum(Value), 6), 0)

+Alt(Before(Total Sum(Value), 8), 0)

+Alt(Before(Total Sum(Value), 10), 0)

+Alt(Before(Total Sum(Value), 12), 0)

+Alt(Before(Total Sum(Value), 14), 0)

+Alt(Before(Total Sum(Value), 16), 0)

+Alt(Before(Total Sum(Value), 18), 0)

+Alt(Before(Total Sum(Value), 20), 0)

+Alt(Before(Total Sum(Value), 22), 0)

+Alt(Before(Total Sum(Value), 24), 0)

The Alt makes sure that the ones that return Null will still be OK.

Stephen

Anonymous
Not applicable
Author

*BUMP*

Cheers Stephen.

Can anybody else think of a better solution?