Simple Average Problem

[Anonymous]

Hi

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

[Anonymous · Accepted solution]

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)

  )