3 Replies Latest reply: May 12, 2011 10:27 AM by Amien Amien RSS

    how can i create this pivot?

    Amien Amien

      table looks like this:

       

      id, date, profit, costs, discount

       

       

      pivot should look like this:

       

                         year = 2010 |  year = 2011   |  grow %

      -------------------------------------------------------------------

      profit         | expression   |  expressions   |  expression

      costs        |                       |                           |

      discount  |                        |                            |  

       

       

      i can put year in dimensions, add serval expressions (profit, costs, discount) and than drag the expressions to the dimension location and the dimension location to the top. works great, but than i cant add the % column .. how can this be designed?

        • Re: how can i create this pivot?
          Stephen Redmond

          There are several ways of doing this.  Depending on your requirements, you might want to look at a set.  However, the easiest way is that you keep the chart that you have with Year pivoted across the top and a 2nd expression for the growth using the Before function.  E.g.:

           

           

          (Sum(LineSalesAmount)-Before(Sum(LineSalesAmount)))/Sum(LineSalesAmount)

           

           

          In the first column, this value will be null (because there is no "Before") but it will calculate for other years.  This allows you to have several years in your pivot and display the growth for all of them.

           

           

          Regards,

           

           

          Stephen