Is there a need to refine the combination of aggregation and condition expression syntax ?

[wallacechen]

Hi ,

I do double check of the average using the expression in qlikview to compare with the average calculated in excel. It is slightly different not exactly the same making me feel confused. is there any need to refine the expression ?

Avg(Aggr(If((Date(CapturedDate)>=Date($(vMinDate)) and Date(CapturedDate)<=Date($(vMaxDate)))and Type ='M' ,salary),[city],[salary]))

Thanks ,

[Anil_Babu_Samineni]

Try this?

Avg(Aggr(If((Date(CapturedDate)>=Date($(vMinDate)) and Date(CapturedDate)<=Date($(vMaxDate))) and Type='M', Salary), city))

And Let me know your dimensions of report?

[wallacechen]

Hi ,

it does not show up anything as attached.

the dimensions are : state -> county -> city. it looks need to put all the dimensions into the expression so that it will not show nothing ?

thanks

[Anil_Babu_Samineni]

That is the case for Your assumption could be correct

Avg(Aggr(If((Date(CapturedDate)>=Date($(vMinDate)) and Date(CapturedDate)<=Date($(vMaxDate))) and Type='M', Salary), state, country, city))

[wallacechen]

it is still the same. not showing up any value. only -.

this expression is based on boxplot wizard .as attached

so I guess it is necessary to put ,[salary])) at the end ?

I suspect the way of condition in the expression needs to be refined as set analysis if there is a need ?

[wallacechen]

I fixed it

Avg(Aggr(Avg(If((Date(CapturedDate)>=Date($(vMinDate)) and Date(CapturedDate)<=Date($(vMaxDate))) and Type='M', Salary)), state, country, city,month))

some months are repeated in the data

[Anil_Babu_Samineni]

Great, hahaha. You may forget Salary Calculation before. Anyway, You got it thru. Anyway, I believe this will also work

Avg(Aggr(If((Date(CapturedDate)>=Date($(vMinDate)) and Date(CapturedDate)<=Date($(vMaxDate))) and Type='M', Alt(Avg(Salary),0)),) state, country, city,month))

Can you close this thread by flag "Correct Answer" and More info Qlik Community Tip: Marking Replies as Correct or Helpful

[wallacechen · Accepted solution]

thanks . not sure if you tried the expression you suggested. it shows errors in expression. but never mind , the one I fixed is correct I think with  validation . the logic is it calculates each average for values group by state , country , city , and month. and then , average all values group by those variables to get the final average.