Skip to main content
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YearMonth adjustment


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,


- aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,


/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.  

3 Replies
Partner - Creator III
Partner - Creator III

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

{<SalesYearMonth = >}

Not applicable


yes, i tried it works.

Thank you,

Alâa Eddine.

Partner - Creator III
Partner - Creator III

Great! Can you please mark my answer as correct?