Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Creator

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

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 ,

1 Solution

Accepted Solutions
Creator
Author

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.

7 Replies

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator
Author

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

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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator
Author

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 ?

Creator
Author

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

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))