Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
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
Creator

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. 

View solution in original post

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
Creator

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 ?

sc.PNG

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
Creator

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 ?


boxplot.PNG

Creator
Creator

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

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
Creator

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. 

View solution in original post