4 Replies Latest reply: Feb 4, 2013 9:29 AM by Cristian Ivanoff RSS

    Pivot table with set analysis?

    Cristian Ivanoff

      Hello all!

       

      I have a problem that I cant solve and hope you can give me a suggestion how to solve this

       

      I have a pivot table with dimensions Category and YearMonth. Categories are displayed as rows and YearMonth as columns so for each category there are one expression per YearMonth.

       

      Now, the expression is just a sum of TransactionAmount. This works fine. But now I want add one expression that calculates the delta between month's.

       

      My first expression is Sum([Month Result]) and to create a delta between ie 201202 and 201201 I do this but it doesn't work:

      sum([Month Result])
      -
      sum({$<[YearMonthCounter]={"=$(=[YearMonthCounter]-1)"}>}[Month Result])
      

       

      I use "Yearmonthcounter" to avoid problems when its the first month of the year.

       

      This is what I want to achieve:

       

      Category    201201                201202                 201203       

                       Amount    Delta    Amount    Delta    Amount    Delta   

      1               1000         0         500          -500      200         -300

      2               200           0        1000         800       100         -900

       

       

      Any suggestions?

       

      Br

      cristian