Forum Discussion

Jake_Raz's avatar
Jake_Raz
ETL
11-14-2022
Solved

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...
dashboards & reporting
  • wallingfordce's avatar
    wallingfordce
    11-16-2022

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

Jake_Raz's avatar
Jake_Raz
ETL
11-16-2022

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.

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.

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.

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

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.

wallingfordce's avatar
wallingfordce
Data Pipeline
11-16-2022

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.

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

 

 

You get to the formula editor for that here.