Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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)