topic Re: Accumulation in Pivot Tables in QlikView

Accumulation in Pivot Tables

Anonymous — Mon, 23 Jan 2012 15:01:25 GMT

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.

Accumulation in Pivot Tables

stephencredmond — Mon, 23 Jan 2012 15:15:13 GMT

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

Re: Accumulation in Pivot Tables

Anonymous — Mon, 23 Jan 2012 15:43:47 GMT

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

Re: Accumulation in Pivot Tables

stephencredmond — Mon, 23 Jan 2012 16:10:55 GMT

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

Re: Accumulation in Pivot Tables

Anonymous — Tue, 24 Jan 2012 11:34:42 GMT

*BUMP*

Cheers Stephen.

Can anybody else think of a better solution?