- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ,
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.