Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
bdickinson3
Contributor II
Contributor II

Filter Pane Based on Excluding Set Analysis

I have been attempting to create a custom filter based on a set analysis expression I used on an app.

My goal is to have REPORT_ID's  pulled based on the highest person involved and not include any REPORT_ID's higher than the level selected. 

The current filter goes something similar to this:
=if([Person.Level]= '01', 'New Hire', '1+ Year').

The main issue is the filter will pick up reports based on the fact it contains [Person.Level] equaling '01' and not because it's the highest value related to that Report.

For example, if I selected a value of '1', I should only collect REPID where '1' is the highest number, and not reports that have '1' that also include '2')

This is the set analysis expression I am attempting to base it off of using a set analysis with an excluding query:
=count({<[Person.Level] = {'01'}, REPORT_ID = e({<[Person.LEVEL]={'02','03', '04','05'}>})>} distinct REPORT_ID)

I appreciate any help on this issue. Thank you.

Edit: Fixed set expression set at beginning, still having issues.

Edit: Updated summary to include more info of what I am trying to accomplish.

Labels (4)
10 Replies
bdickinson3
Contributor II
Contributor II
Author

I managed to solve it. I created an aggr() that identified the ID in a previous column on the table, and used the set analysis with the exclusion of high levels to create the filter using the following syntax:

=aggr(if(Count(distinct{$<[ID_Column] = {'REPORT_ID'}, [Person.LEVEL] = {'01'}, REPORT_ID = e({<[Person.LEVEL]={'02','03', '04','05'}>})>} distinct REPORT_ID) >=1, 'New Hire',
if(Count(distinct{$<[ID_COLUMN] = {'REPORT_ID'}, [Person.LEVEL] = {'02'}, REPORT_ID = e({<[Person.LEVEL]={'03', '04','05'}>})>} distinct REPORT_ID) >=1, '1+ Year(s)',
'n/a'))

//aggr ending
, REPORT_ID)