Avg, Group by + set analysis

[danimarc12]

Hello everyone!

I have an issue with Set Analysis in Qlik Sense.

I want to calculate the Avg of a multiplication Group by a field: avg(aggr(importo*molt, scontrino)).

Well, now I want to calculate the same measure but in the previous year:

avg({<Data={">=$(=Date(Addyears(Min(Data),-1))) <=$(=Date(Addyears(Max({1}Data),-1)))"}>}aggr(importo*molt, scontrino)).

In this way, my measure is not working, even if Qlik tells me that it's correct, as in the img below:

In this way I don't see any value but if I move the {<**>} content after aggr(, I see the same value of the current year.

Could anyone tell me what's wrong with this expression?

Thanks to all!

[rubenmarin · Accepted solution]

Hi, if there is more than value for importo or molt per scontrino you need an agrgeation funciton to tell what to do with this differnt values, something like:

avg({<Data={">=$(=Date(Addyears(Min(Data),-1))) <=$(=Date(Addyears(Max({1}Data),-1)))"}>}aggr(Sum(importo*molt), scontrino))

Maybe you need to add the set analysis also to this sum:

avg({<Data={">=$(=Date(Addyears(Min(Data),-1))) <=$(=Date(Addyears(Max({1}Data),-1)))"}>}aggr(Sum({<Data={">=$(=Date(Addyears(Min(Data),-1))) <=$(=Date(Addyears(Max({1}Data),-1)))"}>} importo*molt), scontrino))