[cduffyaz4]

I am attempting to report on lost business. We do not use Contract Renewals, Recurring Billing, Subscription Management. We manage renewals manually however we did create and populate a custom field, Expiration date on the transaction line.

I created a saved search with several Grouped fields can return the Max Expiration Date but I would like to only return the transaction lines Max Expiration date less than/not after today… My theory is if the Max Expiration date is in the past then the Customer has not renewed a particular item.

Can anyone suggest how I can add criteria/formula to my Saved Search so that I only see the Transaction lines where the Max Expiration Date is in the past…

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

[pcutler]

On the criteria section, there are two tabs. You want to look at the summary tab. There you can set up criteria for the MAX expiration date being before today.

To use the summary criteria, you need to use grouping in the results. I assume you want to group by customer for this purpose.

[cduffyaz4]

Yes, Thanks… I've tried that… my Results have a Max Expiration date

ITEM
MAXIMUM OF EXPIRATION DATE
MAXIMUM OF FORMULA (DATE)
CUSTOMER STAGE
CUSTOMER STATUS
CUSTOMER LAST SALES ACTIVITY

Forms : StateW4.com Access
5/28/2016
5/28/2016
Customer
Won
4/18/2018

and the Summary Criteria is

Summary

PARENS
SUMMARY TYPE
FIELD
DESCRIPTION
FORMULA
PARENS
AND/OR

Maximum
Expiration Date (Custom Column)
is not after today

The system is seems to be interpreting my criteria as even though there may be expiration dates after today, only return results where the maximum expiration date is not after today. So I receive a result with an expiration date with last year's date (i.e., 10/17/2017) included in my saved search. When auditing the results, by drilling into the detail, I see there is a Customer/Item combination with an Expiration date 10/17/2018.

I'd like to instruct the search to evaluate the expiration dates and if there is a maximum expiration date that is after today, do not return any results for that customer/item, since it would have renewed.

Can you imagine another suggestion?

Thanks

[pcutler]

My guess is that your issue is probably in the summarization column on the results tab. If you can't figure it out, try posting screenshots of both the critieria and results tabs.

[cduffyaz4]

Thanks, as I went back to capture print screens, I previewed it again and audited some of my results.

I audited more results and find I still have the same issue.

My Summary Criteria is Summary Type = Maximum, Field = Expiration Date, Description = is not after today.

I need a way to tell the system if there is NO Expiration date after today… return the Max Expiration date.

My Summary Criteria seems to be evaluating all the Expiration Dates and only returning those that are not after today even though there are subsequent Expiration dates for the same Item.

As Example: my search returns a Customer Invoice Line Item with a Max Expiration Date = 05/17/2017.

When I look at all the Customer transactions, I see there is the same Item with a Expiration Dates = 04/17/2018 and 04/17/2019…

This Customer Item should not have been included in my results because there IS an expiration date AFTER today, 04/17/2019.

I do have Summary Types on my "Results" tab…

Name is Group

Amount is Maximum

Item is Group

Expiration Date is Maximum

Formula(Date) Maximum and Formula is max({custcol_fhi_ss_expirationdate}) keep (dense_rank last order by {custcol_fhi_ss_expirationdate}) I just did this to try another Max… the same results are returned as Expiration Date Maximum

Customer (Main Line):Stage is Group

Customer (Main Line): Status is Group

Customer (Main Line) : Last Sales Activity (Custom) is Group

You may not be able to see my Search information above so….Screen Shot attachments are below.

Anyone have any other suggestions for how I can return the Items when they have not renewed.

Thanks

Attached Files

Attrition Summary Criteria and Result Fields.docx

(329.4 KB, 1 view)

[Author]

Posts