topic Help aggr function price effect in QlikView

Help aggr function price effect

Tue, 07 Mar 2017 14:40:59 GMT

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

sunny_talwar — Tue, 07 Mar 2017 16:29:42 GMT

May be this:

Sum(Aggr(

((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)))

, ESCALA))

Wed, 08 Mar 2017 07:05:00 GMT

Thanks, it works !!

Regards,