2 Replies Latest reply: Dec 6, 2013 9:04 AM by Matt Morris RSS

    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