Weighted Accuracy rollup

[sangeess21]

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

[sunny_talwar]

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

[sunny_talwar]

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

[sangeess21]

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.

[sunny_talwar]

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

[sangeess21]

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

[sunny_talwar · Accepted solution]

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 & ']', ', ')))))

[sangeess21]

Thanks Sunny. This expression works