Simple Average Problem

Anonymous — Tue, 21 Mar 2017 17:07:53 GMT

I'm trying to calculate sum of the average but getting Qlik is not calculating it correctly.

I have a simple table,

I need to take average of the last three dates only. I'm using this expression:

sum(

aggr(if(Rank(Only({<Date={"=Len(Only(Date))>1"}>}Date))<4,

sum( aggr(sum(Sale),ID,Date))

/ count(aggr(count(DISTINCT Date),ID,Date))

),ID,Date)

)

If I use avg instead of sum it shows correct average but I need to be able to sum the average calculated. Can anyone help?

Thanks

Re: Simple Average Problem

Anonymous — Tue, 21 Mar 2017 17:43:16 GMT

Guys

I've resolved it using the following expression

sum(

aggr(if(Rank(Only({<Date={"=Len(Only(Date))>1"}>}Date))<4,

sum( aggr(sum(Sale),ID,Date))

/ sum(TOTAL <ID>aggr(if(Rank(Only({<Date={"=Len(Only(Date))>1"}>}Date))<4,count(DISTINCT Date)),ID,Date))

),ID,Date)

)