Filter Pane Based on Excluding Set Analysis

[bdickinson3]

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.

[Anil_Babu_Samineni]

@bdickinson3 Perhaps you missed field in Exclusion.

=count([Person.Level] = {'01'}, ID = e({<[Person.LEVEL]={'02','03', '04','05'}>} REPID)>} distinct REPORT_ID)

[bdickinson3]

I tried that at the filter came back as "Invalid Dimension" on the filter. I found I forgot set expression syntax at the beginning, but still having issues with the filter working.

[Anil_Babu_Samineni]

@bdickinson3 Can you please attach the sample data set?

[stevedark]

HI @bdickinson3 

Dimensions must have multiple values that can be selected. Your current code is an expression which will give a single integer result.

What you need is to get the string value for each REPORT_ID and have that in a list. The way to do that is with an AGGR statement.

Assuming your set analysis is correct, this should work:

aggr(maxstring({<[Person.Level] = {'01'}, ID = e({<[Person.LEVEL]={'02','03', '04','05'}>})>} REPORT_ID), REPORT_ID)

The statement is basically saying get the max REPORT_ID for every REPORT_ID that matches the criteria.

Hope that works for you.

Steve

[bdickinson3]

This is a bit better. The thing is I am trying to get the filtered groups altogether under a single option, like the current filter:

=if([Person.Level]= '01', 'New Hire', '1+ Year').

The problem with the old filter is that it will pick the up REPORT_ID as long as it contains a Person.Level of the selected value instead of the highest (eg. using 'New Filter' I get a REPORT_ID containing two people with Level of '02' and '01', when it should be excluded because it contains a higher value than the selected filter.

[stevedark]

Hi,

I'm not sure I follow what you are trying to achieve.

Should the ID in your set analysis before the E be REPORT_ID though?

Steve

[bdickinson3]

Yeah, I have it set to REPORT_ID.

Essentially, I'm trying to create a filter pane that matches the results of a set analysis with an exclusion function
=count({<[Person.Level] = {'01'}, REPORT_ID = e({<[Person.LEVEL]={'02','03', '04','05'}>})>} distinct REPORT_ID)

.

I have the data set up so that all people involved in the same incident will be linked by the same report ID. I am trying to filter out reports based on the person(s) with the highest senority (eg. if Report ZZ9999 has the highest Person.Level of '00' it would be a New Hire.)

The problem with the current filter is that it pulls a Report_ID based on with the level is included, not if it is the highest available. I am trying use it to match the results of a set analysis I listed previously that excludes all Reports with a higher record (ie anything higher that the selected filter option)

[stevedark]

What does the data model look like? Is everything in one table, or are their joins/data islands?

It is probably going to be useful to have a level number as well, so in the load do this:

LEVEL,
1*LEVEL as LevelNo,

You can then use relative expressions in set analysis.

Am I correct in thinking that by highest you mean closest to zero?

Your set analysis may then need to be:

aggr(maxstring({<Person.LevelNo-={"<$(=min(Person.LevelNo))"}>} REPORT_ID), REPORT_ID)

What is confusing me though is that if you have a proper relational data model set up (or even everything in one table) as soon as you pick a lower record higher records would be filtered out by the associative engine.

It may be worth posting a QVF of what you are attempting so someone can take a look?

Steve

[bdickinson3]

The data's connected across 8 tables with REPORT_ID as the common factor between them. Person.Level and Person.ID are all on one table and I am trying to group them by REPORT_ID and the max Person.Level

Just tried the script and it still isn't exactly what I need. I don't need a filter with a list of report ID's.  What I am trying to do is create an filter option with logic  similar to this:

=if(count(Person.Level = '01') >= 1 AND count(Person.Level = '02') < 1 AND count(Person.Level = '03') < 1 AND count(Person.Level = '4') < 1 AND count(Person.Level = '5') < 1, 'New Hire' ,

if(count(Person.Level = '02') >= 1  < 1 AND count(Person.Level = '03') < 1 AND count(Person.Level = '4') < 1 AND count(Person.Level = '5') < 1, '1+ Year',  ...

Essentially, I am trying to create a filter that when selected, will only show Reports when the max Person.Level associated with them is selected, (if I selected 'New Hire', only show reports where '01' is the highest level associated with the report, and exclude Reports with Person.Level = '02' or higher.