YearMonth adjustment

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: YearMonth adjustment

niclaz79 — Tue, 14 Aug 2018 13:13:12 GMT

HI, can't you just tell the calculations to ignore SalesYearMonth selection in the set-expression?

{<SalesYearMonth = >}

Re: YearMonth adjustment

Anonymous — Tue, 14 Aug 2018 13:23:06 GMT

Hello,

yes, i tried it works.

Thank you,

Alâa Eddine.

Re: YearMonth adjustment

niclaz79 — Tue, 14 Aug 2018 13:25:32 GMT

Great! Can you please mark my answer as correct?

topic Re: YearMonth adjustment in QlikView

YearMonth adjustment

Re: YearMonth adjustment

Re: YearMonth adjustment

Re: YearMonth adjustment