Greatest/Maximum Date Formula 2

[WestView]

I’ve inherited a report that generates a form letter to remind clients that their subscription for a certain product will expire in X months. They get a letter every month for the three months prior to the expiration date. Clients can then either:

1. Not renew
2. Renew for 1 year
3. Renew for 2 years
4. Renew for 3 years

The problem I’m having is that the product codes for each renewal level is different.

One-year subscription Product Code is 9901
Two-year subscription Product Code is 9902
Three-year subscription Product Code is 9903

So, when a client renews their original one-year subscription (9901) after the first letter - to a two-year subscription (9902) - they still get a letter for the next two months reminding them that their original subscription (9901) is about to expire. I’ve been tasked to make sure this no longer happens.

I’ve tried using the Maximum() function on the subscription Expiration Date, but have not been successful.

Any/all help would be greatly appreciated!!!

Thanks,

Tom

 

[kskid]

Post you record selection criteria.

It seems to me that if I renew today for a one year subscription, the expiration date would be Dec, 2005. Likewise, a 2-year subsription with an expiration date of Dec, 2006 and 3-year with an expiration date of Dec, 2007. Am I correct?

The results you are getting now sounds like the expiration date is Dec, 2005 for all of them.

If each subscription have separate expiration dates regardless of product code, then your record selection should be

{subscription.expiration_date} in [dateserial(year(currentdate),month(currentdate)+1,1) to dateserial(year(currentdate),month(currentdate)+3,1-1)]

If the expiration date is the same regardless of product code, then you need the following

(
{subscription.expiration_date} in [dateserial(year(currentdate),month(currentdate)+1,1) to dateserial(year(currentdate),month(currentdate)+3,1-1)] and
{Subscription.prodcode} = "9901"
) or
(
{subscription.expiration_date} in [dateserial(year(currentdate),month(currentdate)-11,1) to dateserial(year(currentdate),month(currentdate)-8,1-1)] and
{Subscription.prodcode} = "9902"
) or
(
{subscription.expiration_date} in [dateserial(year(currentdate),month(currentdate)-23,1) to dateserial(year(currentdate),month(currentdate)-20,1-1)] and
{Subscription.prodcode} = "9903"
)

Cheers,

-LW

 

[synapsevampire]

Aside from Witchita's points, the date should be altered when they last subscribed, so nothing should be pulled unless it's within x months of that date.

If that's the case, perhaps you have multiple rows demonstrating their history, in which case a maximum is in order by creating a group on the customer, and then using something like the following in the Report->Edit Selection Formula->Group:

maximum({subscription.expiration_date},{cust.table}) > currentdate
and
maximum({subscription.expiration_date},{cust.table}) in
[currentdate to dateserial(year(currentdate),month(currentdate)+3,1-1)]

-k

 

[WestView]

Thank you for your replies!

My Record Selection is criteria something like this.

{subscription.expiration_date} = {?expiration_date} and
{Subscriptions.product_code} startswith "99"

All subscriptions expire at the end of their original subscription month, e.g., if a client purchased a one-year subscription today then their expiration date would be 12/31/2005. The report user provides the date via a date parameter. So, users are running reports to send letters to all clients who's subscriptions are going to expire on 3/31/2005.

Therefore, if a client renewed their one-year subscription this month (product code #9901 due to expire on 3/31/2005) to a two-year subscription (product code #9902 which will expire on 3/31/2007) then I want the report to exclude their renewal letter for their subscription #9901 due to expire on 3/31/2005.

I hope this better clarifies the problem I'm having.

Thanks wichitakid & synapsevampire for your replies!!!

- Tom

 

[synapsevampire]

Try supplying more technical information, as with my original post I asked if there are multiple dates (rows) in the table, which you ignored.

Database/connectivity used
Example data
Expected output

-k

 

[kskid]

Part of the technical information should include any groups you may have along with a description of what is in each section (i.e., Report Header, Group Header, Detail, etc). I think it may be a matter of grouping and/or rearranging the data on the report canvas sections.

Cheers,

-LW

 

[kskid]

More technical information would be helpful.

Database/connectivity used
Example data
Expected output

-LW

 

[kskid]

Ooops... in the wrong thread

 

[WestView]

Hello All,

Thank you again for your replies. Sorry for omitting technical info.

It is a basic Form Letter report: Report/Page Headers & Footers and a Details section. There are no groups.

I am using only 3 tables – NAME, ADDRESS, and SUBSCRIPTION. There is only one date field {expiration_date} per subscription record. Of course, each person in the NAME table can have multiple matching SUBSCRIPTION records. For this report, however, I am only interested in the subscriptions that have a Product_Code beginning with “99” i.e.,

One-year subscription Product_Code is “9901”
Two-year subscription Product_Code is “9902”
Three-year subscription Product_Code is “9903”

Clients can order Subscriptions at any time, so the length of their subscription ends on the last day of the month one, two or three years out - depending on the product they ordered.

The problem I’m having is this: We run this current report for clients whose subscription is due to expire three months from now. For example, Jane Doe has a subscription for Product_Code “9901” (a one-year subscription) that is due to expire 3/31/2005. She receives a letter from us informing her of this. She responds right away that she would like to renew her subscription to this product - but would rather order a two-year subscription (Product_Code “9902”) instead of her previous a one-year subscription (“9901”).

Jane now has two matching records in the SUBSCRIPTION table:

Product_Code Expiration_Date Amount
9901 3/31/2005 100.00
9902 3/31/2007 200.00

We send clients these letters each month for the three previous months prior to expiration date. So, when this report is run again next month it will generate a renewal letter for Jane Doe - because her original “9901” is due to expire in two months. This is what I want to prevent because she HAS renewed her subscription only it has a different Product_Code.
I hope this adequately describes the problem and solution I’m looking to achieve.

Thanks again for all your patient help!!!!

- Tom

 

[kskid]

I can only speak for Crystal 8.5 but here is what I do for a similar report.

Assume that you have a date range parameter, ?DateRange


[ol][li]Group by name[/li]
[li]Insert a summary on ExpirationDate with a summary type of maximum. This will give you the maximum date by name[/li]
[li]Go into Select Expert->Show Formula and click on Group Selection and add the following

maximum({subscription.expirationdate},name) in ?dateRange

[li] Suppress all sections except the group header where the form letter will be located[/li][/ol]

If I haven't forgotten anything, that should do it.


Cheers,
-LW

 

[WestView]

wichitakid,

Thank you VERY much for your reply. However, it does not seem to work. I am using CR 8.5 (sp3)

I created a group based on NAME.ID and inserted a Maximum summary on ExpirationDate.

Then I put the following in the Select Expert Group Summary: Maximum({SUBSCRIPTION.ExpirationDate},{NAME.Id}) IN [?dateRange]

I suppressed all other sections except the Group Header.

Then, if I used a record similar to the Jane Doe example, and put in a date parameter of 3/31/2005 for the ExpirationDate, the report still pulled her in - even though she has 'renewed' until 3/31/2007.

What am I doing wrong?!?!?

Thanks SO much again for all your help!

- Tom

 

[kskid]

Hmmm... Strange.

I suspect the expiration date is in your record selection formula looking at the same date range. If so change it to the following

Subscription.expirationDate in dateserial(year(Minimum({?dateRange})),month(minimum({?DateRange}))+1,1) to_ dateserial(year(Maximum({?DateRange})),Month(Maximum({?DateRange}))+37,1)


This says give me all subscriptions that will expire in the next 3 years from the maximum({?DateRange})

The Group Selection formula will limit what is reported based on the range

-LW

 

[WestView]

LW,

Yes, that's exactly right. I have the the ExpirationDate/{?DateRange} parameter in the Record Select formula.

However, when I put in your formula - Subscription.expirationDate in dateserial(year(Minimum({?dateRange})),month(minimum({?DateRange}))+1,1) to_ dateserial(year(Maximum({?DateRange})),Month(Maximum({?DateRange}))+37,1) - in the Record Select formula I get this error message:

"The Summary/running total field could not be created"

Thanks again!!!

- Tom

 

[kskid]

Might have to create a couple of formulas to handle the ?dateRange

//@DtFrom
Minimum({?DateRange})

//@DtTo
Maximum({?DateRange})


In the record selection, replace the Expiration date portion with this

Subscription.expirationDate in dateserial(year({@DtFrom})),month({@DtFrom})+1,1) to_ dateserial(year({@DtTo}),Month({@DtTo})+37,1)

 

[WestView]

LW,

Thanks again SO much for your help! But I just can't seem to get this thing to work.

I still get the "The Summary/running total field could not be created" when I try to create the two formulas you suggested:

//@DtFrom
Minimum({?DateRange})

//@DtTo
Maximum({?DateRange})


Is an Array approach the way to go? Like in my previous example, all I really need to do is get the record with the greater date.

Jane now has two matching records in the SUBSCRIPTION table:

Name_ID Product_Code Expiration_Date Amount
123 9901 3/31/2005 100.00
123 9902 3/31/2007 150.00


Thanks again!!!!

- Tom

 

[kskid]

I did not think that was the problem.

I noticed on one of the earlier posts, you had have the following formula

{subscription.expiration_date} = {?expiration_date} and
...

This normally indicates a single date. Edit ?expiration_date and make sure that Range values is checked. Then the formula should read

{subscription.expiration_date} in {?expiration_date} and
...


-Larry

 

[WestView]

Hi Larry,

That worked! However, I don't think I'm looking for a date range in this report.

Users will put in a single date (end-of-month for whatever year) to send letters to clients whose subscription will end on that date.

As in my previous 'Jane Doe' example, if Jane has an existing subscription (9901) that does not expire until 3/31/2005 - but has also ordered a new two-year subscription (9902) that runs until 3/31/2007 - I need this report to ignore the (9901) subscription because she has a subscription to a product code that starts with '99..' and does not expire until 3/31/2007.

Jane now has two matching records in the SUBSCRIPTION table:

Name_ID Product_Code Expiration_Date Amount
123 9901 3/31/2005 100.00
123 9902 3/31/2007 150.00

Is this possible?

Thanks again for all your patient help!!!!!

- Tom

 

[rosemaryl]

Dear WestView,

What type of database are you using (SQL Server, Oracle for example).

If I were writing the report, I would do a sql expression as follows (SQL Server Example):

Insert Field Object/Sql Expression and name it, for my example and reference MaxEXP:

(Select Max(S."Expiration_Date")
From SUBSCRIPTION S
where S."Product_Code" in (9901,9902,9903)
and
S.ClientField = TableNameinReport."Name_ID"
)

Now, I am guessing at some field names here. You should have some field in Subscription that you are linking to your client and that is what I am indicating by S.ClientField = TableNameinReport."Name_ID"

Now in the selection criteria you can choose records based upon the {%MaxEXP} date field and only return those that meed to the criteria.

I think that will work nicely.

Regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[kskid]

That's was the problem. I assumed a date range but you use a single date.

Then the record selection would be

Subscription.expirationDate in
dateserial(year({?expirationdate}),month({?expirationDate})+1,1)
to_
dateserial(year({?expirationDate}),Month({?DateRange})+37,1)

 

[kskid]

or simpler yet. Since the user is specifying the end of month date, then

{Subscription.expirationDate} in {?expirationdate} to
dateadd("m",48,{?expirationDate}) and
{Subscription.product_code} startswith "99"