# Filtering Question

Member

I have a mega table report that is displaying customer invoice data. One of my filters is for the Aging Range. I have been asked to modify the filter to include all invoices for a customer if any of their invoices are in the filtered range. Meaning that in the below dataset if they selected to filter 151-180 all invoices for Customer A would still be in the report. This allows the user to see a full picture for that customer.

Maybe my brain is just tired, but I'm just drawing a blank on how to make this happen.

Customer

Invoice

Aging Range

Customer A

Invoice 1

151-180

Customer A

Invoice 2

91-120

Customer A

Invoice 3

31-60

Customer A

Invoice 4

1-30

Customer A

Invoice 5

1-30

Customer A

Invoice 6

1-30

Customer A

Invoice 7

1-30

Customer B

Invoice 8

91-120

Customer B

Invoice 9

31-60

Customer B

Invoice 10

31-61

Customer B

Invoice 11

31-60

Customer B

Invoice 12

1-30

Customer B

Invoice 13

1-30

Tagged:

• Coach

Add a calculated field that determines whether a customer should be included based on their invoices. This field will check if any invoice for a customer falls within the selected aging range. Simplified….

Customer In Range:

```CASE
WHEN `Aging Range` = '151-180' THEN 1
ELSE 0
END
```

Aggregate the customer inclusions. To ensure all invoices for a customer are displayed, you'll need a formula to determine if any invoice for a customer falls with the range.

Include Customer:

```SUM(CASE
WHEN `Aging Range` = '151-180' THEN 1
ELSE 0
END) > 0
```

Then you need a filter.

Show All Invoices For Selected Range:

```CASE
WHEN SUM(CASE
WHEN `Aging Range` = '151-180' THEN 1
ELSE 0
END) OVER (PARTITION BY `Customer`) > 0
THEN 'Include'
ELSE 'Exclude'
END
```

Use the Show All Invoices For Selected Range to filter your report. Set the filter to only show rows where this filed equals "Include".

** Did this solve your problem? Accept it as a solution! **

• Member

Thanks for your response. I'm following the logic, but I'm guessing I wasn't clear enough. The user has the ability to select from multiple ranges. Maybe my brain has just decided to take a vacation, but I can't put 2 and 2 together.

• Member

I was able to figure it out in the end. Thanks for the jumping off point.

CASE
WHEN MAX(CASE
WHEN `Aging Range` IN ('1-30','31-60','61-90','91-120','121-150','151-180','181-365','>365')
THEN 1
ELSE 0
END)
OVER (PARTITION BY `Customer`) > 0
THEN 'Include'
ELSE 'Exclude'
END

• Coach

Sorry, that's what I meant by "simplified". I didn't have the time to fully think through the whole solution.

** Did this solve your problem? Accept it as a solution! **

• Member

No worries. I was wrong anyways. It's not working. I swear it was, but I'm back to just filtering by the ranges selected. Ugh

• Member

You can do this by combining a Variable with a Fixed Function. First create a variable named "Age Range" and populate it with your `Aging Range` bins, then create a beast mode:

CASE
WHEN `Customer` = MAX(MAX(CASE WHEN `Aging Range` = `Age Range` THEN `Customer` END) FIXED (BY `Customer`))
THEN 'Include'
ELSE 'Exclude'
END

place the beast mode in your filter and filter on the value "Include"

• Member

Thanks. I am able to get it to work using a variable, but I was really hoping I wouldn't have to. One of the most highly requested feature when I first convert this report from Excel was the ability to filter on multiple ranges. As variable only allow single select that kills that. Ugh.

• Member

In order to apply a range filter, you need a number to filter on rather than text. If you were to include the actual age of the invoice in your dataset then you can filter on the range of days. In your example that would be 0 to 180 days.

However, a range filter would also include the invoices for Customer B. If you still want a result that filters to only Customer A you might need a MAX AGE field

• Member

In the case that the range selected was 0-180 (1-30,31-60,61-90,91-180) then yes, Customer B would also be included. I do have the raw Aging days. The frustrating thing is that by using the Aging Range or Aging Days the reports seems to filter on those before filtering on the Include/Exclude flag. Either that, or the Partition by just isn't working. I'm going to mess around with fixed by a bit more and see if I can stand on 1 foot under the full moon spinning in circles long enough to get it to work.

• Member

I have come up with a potential solution. It feels very bulky at the moment and there is one annoying element. I created 2 textbox variables, 'From' and 'To'. I set the defaults to 1 and 365 respectively. The intent is to dynamically select the desired range. I then modified my beast mode.

CASE
WHEN MAX(CASE
WHEN `Days Aging`>= `From`
AND `Days Aging`<= `To`
THEN 1
ELSE 0
END)
OVER (PARTITION BY `Customer`) > 0
THEN 'Include'
ELSE 'Exclude'
END

The annoying part is that without defaulting the 'To' variable to something annoyingly high there is no good way to include everything over 365. I have the variable set as a number in order to use Days Aging meaning I can't use something like 'max'. Is there a way to dynamically set the default for a variable?

• Member
edited September 6

There unfortunately is no way to dynamically set the default for a variable without using code engine. However maybe you could make the default value "MAX" and then update your beast mode?

CASE
WHEN MAX(CASE
WHEN Days Aging>= From
AND Days Aging<= CASE WHEN To = "MAX" THEN <Your Dynamic Value> ELSE To END
THEN 1
ELSE 0
END)
OVER (PARTITION BY Customer) > 0
THEN 'Include'
ELSE 'Exclude'
END

• Member

I didn't think that would work because text and number fields usually don't work well together. But it did! Thanks everyone for the ideas!