Overall Calculation did not match when Week Added to Dimensions

[danyal1990]

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%')

[rubenmarin · Accepted solution]

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.

[danyal1990]

Thanks Ruben,

Solved it out by splitting all expressions into numerator and another for denominator as you suggested.