Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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))

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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.

Community Browser