8 Replies Latest reply: Mar 21, 2013 9:28 AM by Annemarie Wagelaar RSS

    Multiple expressions in an expression field in a pivot table

      Hello,

       

      I would like to create a pivot table like this

       

       

      KPICurrent yearLast yearGrow%
      Total sales120.000100.00020%
      Total toy sales40.00050.000-20%
      Number of customers13.00010.00030%
      Average sale per customer 9,210-8%

       

      I created a dimension like this:

      =valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

      That works.

       

      But how do I get the expression fields filled? I was thinking of building the expressions Current Year and Last Year each containing 4 expressions. But how do I formulate this?

       

      Or is there a better solution?

       

      Thanks for thinking with me.

       

      Tariaj

        • Re: Multiple expressions in an expression field in a pivot table
          Michael Solomovich

          I'd rather use straight table here.  No much difference in this case.  As for expressions, the "Current Year" may look like this:

           

          if(valuelist(...)='Total sales', <expression for total sales curr year>,
          if(valuelist(...)='Total t sales', <expression for total t sales curr year>,
          if(valuelist(...)='Number of customers', <expression for number of customers curr year>,
          if(valuelist(...)='Average sale per customers', <expression for avg sale per customer curr year>
          ))))

           

          Similar expression for the last year.

           

          the third column can be calculated using the prevous two, e.g.
          ([Current Year] - [Last Year])/[Last Year]

          (where Current Year and Last Year are the labels of the first two expressions)

           

          Regards,
          Michael

          • Re: Multiple expressions in an expression field in a pivot table
            B Aydin

            Hi,

             

            It may be helpful if you can provide a sample set of data.

             

            Regards,

            • Re: Multiple expressions in an expression field in a pivot table
              Jefferson Martins

              Hi tariaj68,

               

              You can create different expressions for each line using the pick function. For each line you should define a specific set analysis expression.

               

              pick( RowNo(),

                            sum(Total Sales),

                            sum({< Toy = {1} >}Sales),

                            Count(Distinct Customer),

                  avg(Sales)

                  )

               

               

              You dimension is correct as valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

               

               

              Hope this helps you.

               

              Regards

              • Re: Multiple expressions in an expression field in a pivot table
                Maxim Senin

                Hi,

                 

                That really depends on what you have in your data. Anyway it's better if you prepare you data for calculation, i.e. have a KPI dimension and values calculated in a load script otherwise you risk to have performance issues (highly significant for huge volumes) and need to think out magic formulas.

                 

                With the calculated dimension you can use the following expression:

                 

                if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

                ='Total sales',

                          1,

                if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

                ='Total toy sales',

                          2,

                if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

                ='Number of customers',

                          3,

                if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

                ='Average sale per customer',

                          4,

                ))))

                 

                You'll have:

                1.png

                 

                Just paste your formulas instead of 1-4, don't forget to add set analisys for LFL comparison. But it's better to think about (re-)designing of data.

                 

                Best regards,

                Maxim

                • Re: Multiple expressions in an expression field in a pivot table
                  Some Nath Roy

                  Hi,

                  You may create a Inline table like:

                   

                  Load * inline

                  [KPIID,KPIMatrix

                  1,'Total Sales'

                  2,'Total toy sales'

                  3,'Number of customers'

                  4,'Average sale per customer'];

                   

                  Add KPIMatrix in dimention list

                   

                  In chart expression:

                  Exps1(CurrentYear):

                  If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<CurrentYear>,

                       If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<CurrentYear>,

                              If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<CurrentYear>,

                                  Expr_for_AvgSalePerCustomer<CurrentYear>

                               )

                       )

                  )

                   

                  Exps2(Last Year):

                  If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<LastYear>,

                       If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<LastYear>,

                              If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<LastYear>,

                                  Expr_for_AvgSalePerCustomer<LastYear>

                               )

                       )

                  )

                   

                   

                  Exps3(Growth%):

                  (Exps1-Exps2) / Exps1

                   

                  Regards,

                  Som

                  • Re: Multiple expressions in an expression field in a pivot table

                    Thank you all for your helpfull replies.

                     

                    I tried them all and they all work.

                     

                    Finally I applied the solution of Jeff Martins using the pick( RowNo()) because in that way the expressions were the shortest and I had the feeling the performance also was the best.

                     

                    I am thinking of moving some expressions to the load script as Maxim Senin recommended.

                     

                    Regards,

                     

                    Tariaj