Creating Formulas based on dates. Past, Current, Future 2

[CrystalKim]

Hi all,

I am using Crystal Reports 11.

I do allright with formulas until I start using date formulas. They confuse me so much that I sit there paralyzed looking at my screen and not knowing what to do.
(

I have to create a report that uses three forumula fields based upon date range values on one of my date fields.

Example: This field I am using is called ForecastedClsDt

I have to break out the result of the report in three seperate ways using the 3 different formulas

Formula breakout?

1. A formula called staledt. This would show stale deals which would equate to customers whose ForecastedClsDt is 30 days or earlier from todays date

2. A formula called currentdt. ForcastedClsDt is 29 days or less from todays date but can be 60 days out of todays date as well

3. A formula called futuredt. This would be customers with a ForecastedClsDt of 61 days or greater of todays date.

In summary (I think) it would be

1. Stale is dates 30 days or more past todays date
2. Current is 29 days earlier from todays date but can be 60 days greater than todays date
3. Future is 61 days or greater from todays date.

Does this make any sense? I am sorry if this is confusing. I have a feeling I am making this more complicated than it really is, but I am terrible with date formulas.

Any help would be most appreciated as always.

 

[kskid]

It depends on what you want to do with the data. If you want to place them in separate gruops then

//@Category
if isnull({table.ForecastedClsDt}) then
"Category 0"
else if {table.ForecastedClsDt} < currentdate - 30 then
"Category 1"
else if if {table.ForecastedClsDt} in [currentdate - 30 to currentdate + 30 then
"Category 2"
else "Category 3"

If you group by {@category} then the records will be separated.

 

[lbass]

If you want to be able to summarize other fields based on these categories, then write one formula and insert a group on it:

if {table.ForcastedClsDt} < currentdate - 29 then
"Stale Deal" else
if {table.ForcastedClsDt} in currentdate - 29 to currentdate + 60 then
"Current Deal" else
if {table.ForcastedClsDt} > currentdate + 60 then
"Future Deal"

-LB

 

[CrystalKim]

Thanks to both of you.

the problem with making this one field and then grouping on it is that I already have a group placed in the report. I need this to appear as columns and not groups.

Ironically enough, I did both of your formulas and they worked. I then tried to create a single field using only a snipet of the code and it did work as well.

Example: If {QRY_Pipeline.Forecast Cls Dt} < currentdate -30 then
"Stale"

Now it gets even more complex for my mind because I now have to group dollar amounts into each of the date formulas fields. I tried doing this and got the error message

"the remaining text does not appear to be part of the formula".

This is my formula

If {@PastDue_DealsCat} then Sum {QRY_Pipeline.Commitment ($)} else 0

Can anyone tell me what I am going wrong?

Thanks again.

 

[lbass]

Insert a crosstab in the report footer or report header and add the formula as the column field your current group field as the row field, and the field you want to summarize as your summary field.

-LB

 

[CrystalKim]

lbass,

thanks for the suggestion on the cross tab. I will do that and makes things easier for me.

I am saving both versions of the code above for future reference.

Thanks again to both of you