cancel
Showing results for 
Search instead for 
Did you mean: 

Filter down to records that contain two categories

Jake_Raz
10 - ETL
10 - ETL

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.

Jake_Raz_0-1668444489845.png

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. 
    • Jake_Raz_1-1668444636496.png
    • 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!

1 ACCEPTED SOLUTION

wallingfordce
10 - ETL
10 - ETL

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).

View solution in original post

4 REPLIES 4

isocrates_knows
7 - Data Storage
7 - Data Storage

I know you don't have access to modify the cube but as soon as I read the first part I would put this on data engineering. You essentially have a poorly dimensioned table that you are trying to report on. I would clean the model up so it's more front end friendly and recreate the model. I know this doesn't help you but I would try and push back on your DE team for this one. 

wallingfordce
10 - ETL
10 - ETL

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).

Thanks! I think this worked, although I did it slightly different than what you suggested. I wasn't sure how to go from the Ranking Filter on "Dispute Number" into an area where I can enter a formula. So instead I just added a count of Dispute Number to the Values section and clicked Edit to go into the formula mode there. Then I used your formula. This caused the Disputes that had both categories to display a "1", while Disputes that did not contain both were simply blank.

Jake_Raz_0-1668606542686.png

From there I clicked the filter button on the formula, and filtered for any Dispute where the formula was greater than zero. This filtered it to just the records that I needed.

Jake_Raz_1-1668606582931.png

A follow-up question, if you don't mind: 

For some reason, the formula wasn't tabulating the total disputes correctly, it was always just saying "1" as a total. To get a count of the actual disputes being displayed, I changed the "Subtotal By" option for the formula to Sum.

Jake_Raz_3-1668606753666.png

So far so good. Unfortunately, this only seems to works when configuring the widget as a Pivot table, and ultimately I was hoping to configure it as an indicator. When I change it to that, the total just shows as "1".

Jake_Raz_4-1668606870391.png

How can I get it to reflect the correct amount (44)? I tried the various "Quick Functions" for value but none of them worked (either still displayed as "1" or just resulted in an error). I also tried modifying the formula by wrapping the entire thing in a SUM() function but that didn't work either.

To convert that formula for an indicator widget, you'd need to do this...

 

SUM ( [Dispute Number] ,
IF (
  ([# unique Dispute Category],[Dispute Category is Employment,Litigation]) > 1
  ,1
  ,NULL
)
)

 

As for the ranking type filter, I forgot they consolidated that type in their filter updates. Here's an example with their Sample ECommerce that limits the results to Countries that contain both Refurbished and Unspecified Conditions.

wallingfordce_0-1668610734049.png

Here's how to get to the formula editor for that...

wallingfordce_1-1668610749826.png

 

 

You get to the formula editor for that here.