Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Before to create a running total

I'm having some problems with the Before function.  Here is a simplified version of what I'm trying to achieve:

Year201220122012201320132013
MonthOctoberNovemberDecemberJanuaryFeburaryMarch
A244324
B564223
C823622
Running Total1527 (12 + 15)38 (27 + 11)49 (38 + 11)55 (49 + 6)64 (55 + 9)

This is my expression:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo() ) )

But this is what I am getting:

Year201220122012201320132013
MonthOctoberNovemberDecemberJanuaryFeburaryMarch
A244324
B564223
C823622
Running Total1527 (12 + 15)38 (27 + 11)22 (11 + 11)28 (22 + 6)37 (28 + 9)

The problem being that in January instead of Before using the Running Total for December it uses the Total for December.

I know this is because "If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom."

But what would be the expression be to calculate the Running Total as I would like?

Many thanks for any help,

Matt

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try adding the TOTAL qualifier to your ColumnNo() function:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )

View solution in original post

2 Replies
swuehl
MVP
MVP

Try adding the TOTAL qualifier to your ColumnNo() function:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )

Not applicable
Author

So that's what the /total/ in the schema was all about!

Thanks swuehl.