Skip to main content
You can succeed best and quickest by helping others to succeed. Join the conversation.
Showing results for 
Search instead for 
Did you mean: 

Convert if to set analysis for aggr funtion with variable A > greater than aggr with Variable B

Hi  , 

I encounter some confusions of converting this expression to Set analysis for better loading efficiency in front end dashboard: 

Count( If(aggr(sum( salary),Date,city)>aggr(avg(allincome),Date,city) and Date>=Date('01/01/2019'), [Date]))

May I ask your experience how to covert this if condition into Set analysis 100% right to work?



Labels (4)
3 Replies
Partner - Specialist III
Partner - Specialist III

Try this:


Change the makedate parameter to your required Starting Date.

Refer qvw attached below.


Create a new field in the script which combines Date and City into a single column... something like this

City&'|'&Date as City_Date_Key

and then try an expression like this 

Count({<City_Date_Key = {"=Sum(salary) > Avg(allincome)"}, Date = {">=$(=Date(MakeDate(2019, 1, 1), 'MM/DD/YYYY'))"}>} [Date])


Former Employee
Former Employee

Wallace, did Arthur or Sunny's posts help you resolve things?  If so, please be sure to circle back and use the Accept as Solution button on the post(s) that got you what you needed.  If you did something different that is working, consider posting that and then mark it so others will know what worked, and if you still need assistance, provide an update on the thread.


To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.