Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having some problems with the Before function. Here is a simplified version of what I'm trying to achieve:
Year | 2012 | 2012 | 2012 | 2013 | 2013 | 2013 | |
---|---|---|---|---|---|---|---|
Month | October | November | December | January | Feburary | March | |
A | 2 | 4 | 4 | 3 | 2 | 4 | |
B | 5 | 6 | 4 | 2 | 2 | 3 | |
C | 8 | 2 | 3 | 6 | 2 | 2 | |
Running Total | 15 | 27 (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:
Year | 2012 | 2012 | 2012 | 2013 | 2013 | 2013 | |
---|---|---|---|---|---|---|---|
Month | October | November | December | January | Feburary | March | |
A | 2 | 4 | 4 | 3 | 2 | 4 | |
B | 5 | 6 | 4 | 2 | 2 | 3 | |
C | 8 | 2 | 3 | 6 | 2 | 2 | |
Running Total | 15 | 27 (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
Try adding the TOTAL qualifier to your ColumnNo() function:
rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )
Try adding the TOTAL qualifier to your ColumnNo() function:
rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )
So that's what the /total/ in the schema was all about!
Thanks swuehl.