[mushrush]

I need a search to show all customers who’s account is on hold who have not paid in the last 90 days.

I can get a search to work, but it shoes all customers on hold who meets the criteria. ie. even if their last payment was 60 days ago.

Sorted by Formula (descending)

My current Criteria is this:

Transaction : Type is Payment

Credit Hold is On

Consolidated Balance is greater than 0.00

Results:

Name (Group)

Credit Hold (Group)

Transaction : Date (Maximum)

Formula(Numeric) (Minimum) “to_date(SYSDATE)-{transaction.trandate}”

Consolidated Balance (Average)

I get the results I want, but I also get the ones that have paid within the 90 days…

Any ideas?

Thanks!
This is a cached copy. Click here to see the original post.

[k_dunc]

Just quickly, I’m wondering if the solution would be to add a criterion into the Criteria > Summary subtab. I’m sort of thinking that you’ll need something like a Summary Type of Count with a CASE formula looking for any payments made for the Customer within the last 90-days, then to weed these Customers out; therefore only returning Results where this formula yields zero matches.

[mushrush]

Thank you for the response. I am not 100% sure I follow you.

One good thing is the person asking said it was fine to leave the other customers listed that had paid in the past 90 days.

So for right now I am okay, but am still curious to your possible solution.

[k_dunc]

That’s great to hear that you have a viable solution.

Without having tested it – so hypothetically speaking – my suggested solution was to see if using the Saved Search’s Summary Criteria would do the trick. On the Criteria subtab, you have both a Standard and Summary subtab. I was suggesting that in the Summary subtab, you may be able to put in a formula that checks for payment transactions made within the last 90-days per Customer, and if any exist (e.g., a ‘Summary Type = Count’ greater than 0), then to not display this Customer. I.e., we’re looking for Customers whom have 0 payment transactions in the past 90-days.

So in my opinion, it may be something like a DENSE_RANK and PARTITION BY {entityid} type formula that you’d need. So what this could do, is, partitioned by each Customer, see if any payment transactions exist, and if the answer is zero, then display this Customer in your Results.

[Author]

Posts