topic Re: Weighted Accuracy rollup in QlikView

Weighted Accuracy rollup

sangeess21 — Wed, 28 Sep 2016 02:19:49 GMT

Hi All,

I have been stumped on this weighted forecast accuracy for few days. Any help would be appreciated.

Let's say we have the below hierarchy.

Company->Division->Group->Model->Category->Item Group->Fcst Product Classification->Item.

We have two types of accuracy - Normal and weighted accuracy. Normal accuracy is where all items have the same weight (1) and weighted accuracy is where items under Fcst Product Classification have different weights.

Fcst Product Classification	Weight
High Revenue	0.35
Commodity	0.25
EOSPending	0.10
Legacy	0.07
Build to Order	0.08
High Growth Potential	0.15

If the items do not fall under these category, they would have weight value 1.

I'm trying to calculate the weighted accuracy using the formula from Fcst Product Classification level.

1-∑ (error*weight)/demand, where error=sum(Forecast)-sum(Demand)

1-sum(aggr((sum(aggr(fabs(sum(FORECAST-QTY)-sum({<Flag_Type=>}Demand)))/sum({<Flag_Type>}Demand)) * Forecast_weight,Item),[Fcst Product Classification])).

The weighted accuracy works fine on Fcst Product Classification level, but when I try to roll up the accuracy to higher hierarchy, it changes. I want the accuracy to be consistent on all levels. How do I calculate the weighted accuracy and roll up the percentage to higher levels?

Thanks,

Sangeetha

Re: Weighted Accuracy rollup

sunny_talwar — Wed, 28 Sep 2016 19:02:08 GMT

It would probably be easier if you are able to share a sample with us where we can play around with it

Re: Weighted Accuracy rollup

sunny_talwar — Wed, 28 Sep 2016 20:59:28 GMT

Is [Fcst Product Classification] your most granular dimension? If it is, then may be use the same expression for all levels

1-sum(aggr(((sum(aggr(fabs(sum(FORECAST_QTY)-sum({<Flag_Type=>}Demand)),Item)))/sum({<Flag_Type=>}Demand))*if([Fcst Product Classification]='High Revenue',$(vHighRevenue),

if([Fcst Product Classification]='Commodity',$(vCommodity),

if([Fcst Product Classification]='High Growth Potential',$(vHighGrowthPotential),

if([Fcst Product Classification]='EOS Pending',$(vEOSPending),

if([Fcst Product Classification]='Build to Order',$(vBuildToOrder),

if([Fcst Product Classification]='Legacy',$(vLegacy),

1)))))),[Fcst Product Classification]))

Re: Weighted Accuracy rollup

sangeess21 — Wed, 28 Sep 2016 21:26:13 GMT

I cannot use [Fcst Product Classification] for all levels. When I break down the hierarchy (Select Company,Division,Group,Model and Fcst Product Classification) the weighted accuracy should be calculated accordingly but it does not do so. It just shows 100% for most of them. Attached is the excel that calculates the weighted accuracy(in red) but the percentage does not rollup to the Fcst Product Classification.

Re: Weighted Accuracy rollup

sunny_talwar — Wed, 28 Sep 2016 21:53:03 GMT

How does this look?

Sum(Aggr(1 -

(Sum(Aggr(RangeMax(Sum(FORECAST_QTY)-Sum({<Flag_Type=>}Demand),0),Item))/Sum({<Flag_Type=>}Demand)

Pick(Match([Fcst Product Classification], 'High Revenue', 'Commodity', 'High Growth Potential', 'EOS Pending', 'Build to Order', 'Legacy') + 1, 1, $(vHighRevenue), $(vCommodity), $(vHighGrowthPotential), $(vEOSPending), $(vBuildToOrder), $(vLegacy))),

Company,DIVISION_NAME,GROUP_NAME,Model,[Fcst Product Classification]))

Re: Weighted Accuracy rollup

sangeess21 — Wed, 28 Sep 2016 22:09:36 GMT

No. The percentage does not match to those when calculated manually

Re: Weighted Accuracy rollup

sunny_talwar — Fri, 30 Sep 2016 16:02:15 GMT

May be this:

If(Dimensionality() = 0,

1 -

Sum(Aggr((Sum(Aggr(fabs(Sum(FORECAST_QTY)-Sum({<Flag_Type=>}Demand)),Item))/Sum({<Flag_Type=>}Demand)*

Pick(Match([Fcst Product Classification], 'High Revenue', 'Commodity', 'High Growth Potential', 'EOS Pending', 'Build to Order', 'Legacy') + 1, 0,

[Fcst Product Classification])),

1 -

Sum(Aggr((Sum(Aggr(fabs(Sum(FORECAST_QTY)-Sum({<Flag_Type=>}Demand)),Item))/Sum({<Flag_Type=>}Demand)*

Pick(Match([Fcst Product Classification], 'High Revenue', 'Commodity', 'High Growth Potential', 'EOS Pending', 'Build to Order', 'Legacy') + 1, 0,

$(=Concat(DISTINCT '[' & _DIM & ']', ', ')))))

Re: Weighted Accuracy rollup

sangeess21 — Fri, 30 Sep 2016 16:05:51 GMT

Thanks Sunny. This expression works