Filter down to records that contain two categories
Hello! I'm trying to filter some data in a widget. I want a list of "Disputes" that have two specific categories. The categories are not values in a single field, but rather are separate entries/rows on a related table. For example, if a Dispute has three categories on it, then this will show as three rows in the widget.
If I add a widget filter for "Dispute Category" and select the two categories I want, it appears to use OR logic, meaning if I've selected Category A and Category B, I get a list of matters that contain either of those categories but not necessarily both at once. I want to narrow this list down to just the matters that contain BOTH categories.
See the below screenshot for an example of what I mean. I've circled the records I'd like it to filter down to.
Is this possible within the UI, maybe using an "Advanced Filter", or formulas, or some clever combination of other elements? Please note: I unfortunately do not have access to modify the eCube or any of those configurations/settings. Hopefully this isn't something that can only be done that way...I'm hoping I can accomplish this sort of filtering just using the main widget/dashboard UI.
Things I've already tried:
- Changing the filter to a TEXT filter and then inputting both values using "AND" logic. This just resulted in zero records found, likely because filtering at the category level means it's essentially filtering on each category in isolation (i.e. on each individual row).
- Created a special checkbox in our application that auto-populates/auto-checks-itself when two particular categories are selected, and then just filter for records where that checkbox is checked. However, this only works for a particular combination of categories, and now I need to filter for a different set (and possibly more in the future). I need a solution that can be generalized to any arbitrary combination of categories.
- Filter at the Dispute level somehow (e.g. filter on Dispute Number) and then attempt to use the Advanced Filter to apply a filter for associated Dispute Categories. Unfortunately I haven't been able to make this work.
- I'm not sure it's even possible, as it seems the scope of a filter is tied to the particular field its for. However, I found some examples & screenshots in the Sisense Documentation that seem to indicate this is possible? See this link, and scroll down a bit to Example 3: it appears to be some JAQL that filters "Customers" by another field, "Products".
Any help would be much appreciated. Thank you!
Try a filter on Dispute Number. Use a Ranking type filter set to the TOP 1000 to start. Use a formula like...
IF ( ([# unique Dispute Category],[Dispute Category is Employment,Litigation]) > 1 ,1 ,NULL )Disable the Dispute Category filter shown in your screenshot.
This will return only Dispute Number that have both Dispute Category you referenced, and will display all dispute categories within those Dispute Number (e.g. if there happens to be a third Dispute Category).
