7 Replies Latest reply: Sep 30, 2016 12:05 PM by Sangeetha Shanmugham

# Weighted Accuracy rollup

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

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

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

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

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

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

• ###### Re: Weighted Accuracy rollup

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

Thanks Sunny. This expression works