Skip to main content
Within Report Builder, has anyone solved for a filtering scenario that involves excluding rows based on a combination of fields?





For example, if I have a dataset in which all of my users are assigned a letter (A, B, C, D, etc.) and also a number (1, 2, 3, etc.)





I would like to see all of the users who have an A, and all of the users who have a 1, but I want to exclude from view any of the users who have the combination of A and 1.
What if in the filter you set it to Letter != A Number !=1 and then on the filter setting you set it to . (A and 😎?
In your example, it sounds like there's a "Letter" field that might = A, and a "Number" field that might = 1.  If so, then you'd theoretically just use something like





Filter A:  Letter=A


Filter B: Number=1


and the expression:


 (A OR 😎 NOT (A AND B) 





Too bad we can't use NOT as an operator.  I just voted up this related Idea.  Finding another way still sounds like an interesting challenge, but being able to use "NOT" would really tie the room together.  ;)


https://community.gainsight.com/gainsight/topics/filtering-in-reports-for-does-not-start-with
I was guarding against floating a solution to my own question, because I wanted to get some good opinions or creative solutions. You're right, Andy, in that I would use a NOT operator tomorrow if it was available.
Keep hope alive! 🙂
Thanks Marcelo. I've found when I attempt that, I end up excluding all of the A's and then also excluding all of the 1's.
Hi Matthew,


Can you try Marcelo's suggestion with (A or 😎? 


Theoretically, (A ∩ B)' = A' U B'.





For your exact question, filters needed would be


Filter A: Letter A


Filter B: Number 1


Filter C: Not Letter A (!= A)


Filter 😨 Not Number 1 (!= 1)


In advanced logic: (A or 😎 and (C or D)
Rakesh, I attempted your suggestion on the dataset and did not get the results I was hoping for. Records I wanted to exclude because they met BOTH of my criteria still appear in the results.





Given that in my use case, I have 4 pairs of values to exclude (I need to exclude A1, A2, A3 and B1), even if this did work, I would end up with at least 16 filters and an advanced logic string that makes me shudder.





The ability to generate a Calculated Field within a Report (in this case, to CONCAT) would give me a more straightforward solution and put Reports at more parity with Bionic Rules.
Hi Matthew,


Concat should be one of the functions in the [filter_by]=all&topic-reply-list[settings][reply_id]=19548858#reply_19548858]formula fields capability we have on our roadmap! 


I would like to see all of the users who have an A, and all of the users who have a 1, but I want to exclude from view any of the users who have the combination of A and 1.

Logically this cannot be achived. You cannot see all of the As or all of the 1s if you exclude those who have both.

That said, Rakesh almost had it right….

For your exact question, filters needed would be
Filter A: Letter A
Filter B: Number 1
Filter C: Not Letter A (!= A)
Filter 😨 Not Number 1 (!= 1)
In advanced logic: (A or 😎 and (C or D)

… actually it should be ( (A AND D) OR (B AND C) )

Verbally: Letter A but not Number 1 or Number 1 but not Letter A

Reply


Terms & ConditionsCookie settings