Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)