Range sum of entire pivot table

[#1]

My rangesum expression is behaving in an unexpected way: It's only summing up rows of an expanded dimension.

I need a running total of the entire pivot table for cashflow purposes (taking into account of starting bank balance + inflows/outflows throughout the year).

Have a look: http://i50.tinypic.com/14vqwc8.gif

RangeSum Amount expression: RangeSum(Above(1), 0, RowNo())

I used 1 for debugging purposes.

[Miguel_Angel_Baeyens]

Hi,

Use the TOTAL qualifier to read the whole column. For example:

RangeSum(Above(TOTAL Column(1), 0, RowNo()))

That will sum up all the amounts in the first column (corresponding to the first visible expression in the pivot or straight table).

Hope that helps.

Miguel

[#3]

Hi,

Thanks for the quick reply.  I pasted your code (did the rangesum of Inflows only).  It's still resetting at every new ClientName / Description.

http://i49.tinypic.com/15fncjl.gif

BTW, col1 expression: SUM(InAmount)

col2 expression: SUM(OutAmount)

[Miguel_Angel_Baeyens · Accepted solution]

So it seems,

Let's tweak it a bit, then:

RangeSum(Above(TOTAL Column(1), 0, NoOfRows(TOTAL)))

Hope that helps.

Miguel

[#5]

Great!  Getting there!

How do I reverse the direction of the sum?  My pivot table is sorted Effective Checking Date DESC.  (Latest/Current first ---> Earlier in the year)

http://i47.tinypic.com/281bw5l.gif

[Miguel_Angel_Baeyens]

Hi,

Go to the chart properties, Sort tab and sort the dimension the other way round. RangeSum() will always start in the first row and end in the last row.

Hope that helps.

Miguel

EDIT: Or you can Sort the chart by Y-Value, that will do as well.

[#7]

Thanks!

[#8]

I tweaked it a little bit.  I kept my sorting as is... Instead of ABOVE(), I used BELOW().  Works great.  Thank you!

RangeSum(Below(TOTAL Column(1) - Column(2), 0, NoOfRows(TOTAL)))