Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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,

 

3 Replies
Highlighted
Partner
Partner

Re: Convert if to set analysis for aggr funtion with variable A > greater than aggr with Variab

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.

Highlighted

Re: Convert if to set analysis for aggr funtion with variable A > greater than aggr with Variab

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

 

Highlighted
Digital Support
Digital Support

Re: Convert if to set analysis for aggr funtion with variable A > greater than aggr with Variab

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.