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

[wallacechen]

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?

Thanks,

[Arthur_Fong]

Try this:

count({<Date={">=$(=MakeDate(2018,10,1))"},Date={"=aggr(sum(Salary),Date,City)>aggr(avg(AllIncome),Date,City)"}>}Date)

Change the makedate parameter to your required Starting Date.

Refer qvw attached below.

[sunny_talwar]

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

[Brett_Bleess]

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.

Regards,
Brett