Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Day All,
I am calculating 4 KPIs differently then taking their AERAGE by using RangeAvg and it is showing perfectly fine percentage when I view it on Month basis (without any filter).
The problem is when I add Week into dimensions the calculation disturbed and not showing correct result week wise.
=num(RangeAvg(
count(if(KPI1_Compliance='C' and KPI1_Parameter='KPI1',KPI1_Compliance))/sum(Planned_Visits)
,
sum(aggr(If(count(distinct if(KPI2_Parameter='KPI2' and KPI2_Compliance='C',Category))>=5,1,0),KPI2_Visit_Type,KPI2_Compliance))/
(Sum(Aggr((if(Store_category='Supermarket', Planned_Visits)),Storecode,Date)))
,
Sum(Aggr(Count(DISTINCT if(KPI3_Parameter='KPI3' and KPI3_Compliance='C', KPI3_Visit_Type)), Storecode))/
Sum(Aggr((if(Store_category<>'Pharmacy', Planned_Visits)),Storecode,Date))
,
Sum(Aggr(Count(DISTINCT if(KPI4_Parameter='KPI4' and KPI4_Compliance='C', KPI4_Visit_Type)), Storecode))/Sum(Planned_Visits)
)
,
'#,##0%')
Hi, hard to say, probably an aggr needs another field to correctly split values between weeks.
I would try to set eack KPI in a different expression to identify wich KPIs are returning an unexpected value.
Then split one of these KPIs in a expression for a numerator and another for denominator, seing the result probably gives you a hint of what is failing.
Or at least you can post again your results and we can try to fix a simpler expression than this one.
Hi, hard to say, probably an aggr needs another field to correctly split values between weeks.
I would try to set eack KPI in a different expression to identify wich KPIs are returning an unexpected value.
Then split one of these KPIs in a expression for a numerator and another for denominator, seing the result probably gives you a hint of what is failing.
Or at least you can post again your results and we can try to fix a simpler expression than this one.
Thanks Ruben,
Solved it out by splitting all expressions into numerator and another for denominator as you suggested.