2 Replies Latest reply: Mar 8, 2017 2:05 AM by Tanguy de Becdelievre RSS

    Help aggr function price effect

    Tanguy de Becdelievre

      Good afternoon,

       

      I want to determine the price effect of a variation of employee cost.

      No problem when I create a Table, and use as Dimension the grade (ESCALA) of the employees:

       

      PRICE EFFECT=

      ((Sum({<FinancialYear={2017}TRANS_AMOUNT)/Avg(Aggr(count(distinct{<FinancialYear={2017}EMPNUM), PAY_PERIOD,ESCALA)))

      -

      (Sum({<FinancialYear={2016}TRANS_AMOUNT)/Avg(Aggr(count(distinct{<FinancialYear={2016}>}EMPNUM), PAY_PERIOD,ESCALA))))

      *

      Avg(Aggr(count(distinct{<FinancialYear={2017}>}EMPNUM), PAY_PERIOD,ESCALA)))

       

      And making the sum of the rows gives me the correct result.

       

      My problem comes when I want to get the same figure but on a KPI object for reporting purpose.

       

      Does anybody know what formula I should use ?

       

      Thanks for your help