Filtering Question

ARosser

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

ggenovese

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

ArborRose

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

ARosser

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.

ARosser

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

ArborRose

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

ARosser

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

ggenovese

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"

ARosser

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.

ggenovese

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

ARosser

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.

ARosser

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?

ggenovese

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

ARosser

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!