3 Replies Latest reply on Dec 4, 2018 10:01 AM by christina.hopes

Creating Dynamic Sets that Exclude Overlapping String Data

I am running into an issue setting up dynamic sets that can filter out the overlap between two sets.  I am not able to share the actual data, but I’ve created a dummy set to help illustrate the issue.

We have three teams.  I need to run recurring reports on the following individuals:

1. All members of Team A who are not in Team C
2. All member of Team B who are not in Team C

I need the sets to be dynamic because the team membership changes frequently even though the logic behind the reports do not.

On the Try 1: Team tab, I did a true/false calculation on the criteria and created a set off that.   I wanted to apply a combined set that did not include the overlap, but a combined set was not an option.  I can add the C team as a filter, but that only provides correct results for In Set A and In Set C.

On Try 2: Team B, I tried creating sets with the character names instead of True/False criteria.

I’ve also tried a few FIXED formulas but get keep getting an aggregation error.

Any help would be greatly appreciated. Apologies if I missed this in a previously answered thread.

• 1. Re: Creating Dynamic Sets that Exclude Overlapping String Data

Christina,

I'm not sure if this will work for your true setup, but maybe it can give ideas.

To get a running list of all team per character, you can try the method described here:

Re: Floor Map: how to show more than 1 person per room?

I adapted this to create a running list per character

IF FIRST()=0 THEN ATTR([Team])

ELSE PREVIOUS_VALUE("")+"|"+ATTR([Team])

END

And then took the max and called it [TeamList]:

WINDOW_MAX([RunningTeams])

So the Flag for whether to include or not becomes:

IF [Report Type]="A not C"

THEN

IF CONTAINS([TeamList],"Team A")

AND NOT(CONTAINS([TeamList],"Team C"))

THEN "Include"

END

ELSEIF [Report Type]="B not C"

THEN

IF CONTAINS([TeamList],"Team B")

AND NOT(CONTAINS([TeamList],"Team C"))

THEN "Include"

END

END

I tried counting this but I couldn't get it to show in the "Grand Total" row.

• 2. Re: Creating Dynamic Sets that Exclude Overlapping String Data

Here are two different approach using Sets.

Approach 1:

Create a Single Conditional set on Disney character.

Approach 2: Create each set on Disney character for Team A,B and C using condition. Then create combine set using A and C and B and C.

Hope this helps.

1 of 1 people found this helpful
• 3. Re: Creating Dynamic Sets that Exclude Overlapping String Data

Perfect! Thank you!