Date Range Formula 1

[McChops]

CR10

I need a formula that takes the Open_Date of a record and groups it into 1 of the 4 following date ranges.

The Open_Date has always previously occured and I want the grouping to work like this.

If Open_Date occurred in the last 7 days then "Last 7 days"
     else
If Open_Date is older than 7 days but less than 14 days" then "Last 14 days"
     else
If Open_Date occurred in the last 30 days then "Last 30 days"
     else
"Older than 30 days"

I can get some of it working using the supplied functions Last7days and Current date - 14 etc, but can't get the whole thing to work.

Thanks.

 

[synapsevampire]

VERY little of your syntax would work as is, kindly post what did.

If {table.Open_Date} in last7days then "Last 7 days"
else
If Open_Date is >= minimum(last7days)-7 and
If Open_Date is <= maximum(last7days)-7 then
"Last 14 days"//then you switchto 30days,what about days 15, 16, 17, etc...?

You need to allow bucketing for ALL days.

So adjust as required and use comparable functions.

-k

 

[McChops]

Yeah, I was trying to spell out the problem in structured english rather than crystal syntax.

The following worked;

If {@OpenDate} in last7days then "Last Week"
     else
If {@OpenDate} >= minimum(last7days)-7 and
{@OpenDate} <= maximum(last7days)-7 then 
    "Last 14 days"
    else
If {@OpenDate} >= minimum(last7days)-23 and
{@OpenDate} <= maximum(last7days)-14 then 
    "2 Weeks to 30 Days"
    Else
    "Greater than 1 month"

Thanks -k