Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
*BUMP*
Cheers Stephen.
Can anybody else think of a better solution?