Aggr to calculate latest 3 years of mean data

[KennyTan]

Hi all,

I'm new to Qlik, and I have a use case on calculate average latest 3 years of mean data (from a measure), but I 'm stuck and appreciate some help from the community.

Basically I have a measure to calculate mean sales per product data, as below:

sum(sales)/count(distinct product)

Now I will need to get the latest 3 yeas of available data, and calculate the average figure for the laste 3 years of mean data. I understand I may need something like aggr? Correct me if I am wrong.

I am stuck in the aggr function and have no idea on how to proceed further:

Aggr(
sum(sales)/count(distinct product), year,customer)

The average latest 3 years of data should take into account for filter selection, for eg, if custE is selected, the calculation show only from year 2016-2018. 

While if cust A is selected, the calculation show only from year 2019-2021.

 

whereas if they is no filter selection, the average figure should calculate latest 3 years of overall mean dataset.

I hope I explain my problem clearly. Appreciate any help and input I can get. Thanks.

I also attached a sample qvf file if that will make it easy to understand my use case.

Thanks again for any input and help.

Regards,

Kenny.

[rubenmarin · Accepted solution]

Hi, note that aggr returns a value for each combination of the dimensions set as parameters, you need an aggreagtion funcion to return only one value, in axample an Avg(). Also you can apply set anlysis to limit data to the last 3 years, at the end it could be someting like:

Avg({<year={">=$(=Max(year)-2)<=$(=Max(year))"}>} Aggr(
sum(sales)/count(distinct product), year,customer))

This can work with one customer, it's not clear what you wnat to calculate for diffrent customers selected.

If you select custE and custA it will take data from 2019 to 2021, if you want 3 last years for each customer and do an avg you can't use set anlysis and have to use if.

[Kushal_Chawda]

@KennyTan  you could also try below

=sum({<year={">=$(=max(year)-2)"}>}sales)/count(distinct {<year={">=$(=max(year)-2)"}>}product)

[KennyTan]

Thanks both @rubenmarin and @Kushal_Chawda . much appreciated.