Hello,
please find attched the table below.
The average is calculated with this formula:
=aggr(sum(SalesActualSales)/count (DISTINCT SalesYearMonth),SalesAggregatedBrand_CD, SalesSubsidiaryCode_CD).
The Standard deviation:
=if(sum(SalesActualSales)=0,0, Stdev([SalesActualSales])).
The coefficient of variation:
=if(sum(SalesActualSales)=0,0, Stdev([SalesActualSales]))/aggr(sum(SalesActualSales)/count (DISTINCT SalesYearMonth),SalesAggregatedBrand_CD, SalesSubsidiaryCode_CD).
The Sub/aggr brand accuracy:
= If ((1- (sum (fabs(aggr(sum ([SalesActualSales])
, SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth) -
aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth)))/sum(aggr(sum([SalesActualSales]),
SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth ))) < 0
or sum(aggr(sum([SalesActualSales]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD
,SalesYearMonth))= 0),0
,1- (sum (fabs(aggr(sum ([SalesActualSales]), SalesSubsidiaryCode_CD,
SalesAggregatedBrand_CD,SalesYearMonth)
- aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,
SalesYearMonth)))
/sum(aggr(sum([SalesActualSales]), SalesSubsidiaryCode_CD,
SalesAggregatedBrand_CD,SalesYearMonth )))).
--------------------------------------------------------------------------------------------------------------------------------
What i want to do is to show the accuracy value of a specific period let says july 2018 but when i choose this period the actual sales [column 2] will change its value to the actual sales of july and the average will be equal to the actual sales because in the formula of the average the number of periods will be counted and because i selected one period this number will equal to 1 that's why the average equal to the actual sales. This is my issue.
In other terms, i want it to return the actual sales, the average, the standard deviation, the coefficient of variation with the current selection but with the selection in period which is here SalesYearMonth removed. (values do not depend on the selection period). In contrast, for accuracy values, i want to be changed according to the current selection.
I hope it is clear.
Thank you very much.
Alâa Eddine.
