Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Creator

## 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?

Thanks,

Labels (4)

• ### Set Analysis

3 Replies
Partner - Specialist III

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.

MVP

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

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

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.
Community Browser