How to exclude current date in Max and Min formulas 2

[ginkoba]

Is there a way to exclude the current date from a Max or Min formula? I'm trying to do a Max on a group of numbers for the past 5 years and one of them is from the current date. How do I exclude that from showing up?

Any help will be greatly appreciated.

Ginkoba

 

[hilfy]

You could try using a Running Total. Set it to evaluate on a formula and set the formula to exclude the current date. If you are doing the min or max at multiple levels of grouping, you'll have to create a Running Total for each group.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Madawc]

Another method is to create a formula:

if {your.date} = CurrentDate
then " "
else ToText({your.date}, "yyyyMMDD")

You could then apply a summary total to get the maximum date before the current date. Summary totals can be put in a group header.

You could do the same for minimum, setting it as "9999 No Date" when it matches the current date.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[ginkoba]

Thanks Dell and Madawc Williams,

So this is a snapshot of the problem...These are all numbers for FY 2010. I have all these months summed up like the example below. Say when I apply the Max for Jan, I get 1346074 which is the current number for Jan but I don't want that number at all. I want it excluded. The same thing happens for all of them.


Oct Nov Dec Jan Feb
Min 1628601 1431325 1186025 975809 987534
Max 1950648 1897260 1564104 1850609 1295255
Current 2133606 1907590 1436182 1346074 841896

Mar Apr May Jun Jul Aug Sep
1935338 2771995 2538751 2386944 3029991 2099063 1310594
2479818 3810428 2992284 3343505 4046009 3184305 1837691
2617002 3884757 3237928 3226706 4140980 3197887 1904793

 

[hilfy]

Are you using a command or tables? What type of database are you using?

If you don't want to display the current month at all, you could set up in your data filter to only run through the last day of the previous month.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ginkoba]

I'm using tables. Database is Oracle and my Crystal version is 2008. Don't want to exclude the current month in the filter as I want to see it on the report but not in my max function which will later be shown on a chart.

 

[lbass]

Do you have a group on year? If so, then your maximums and minimums should include the year group condition if you are writing them out, as in:

Maximum({table.amt},{table.date},"annually")

Or you should be able to just insert maximums on them at the group level, as long as they are placed in the group section.

-LB

 

[hilfy]

Since you're using tables, you can create a SQL Expression to help with this calculation.

I'll call the SQL Expression {%CurrentMonth}. The code for it is: Trunc(Sysdate, 'mm'). This will give you the first day of the current month.

From here, you create running totals (unlike summaries, you'll only be able to use these in footer sections). In the Evaluate section, select "Use Formula" and enter something like the following in the formula (use the actual date field from your data): {table.datefield} < {%CurrentMonth}. Your mins and max's should now calculate only for months prior to the current one, but your data for the current month will still display on the report.

Another way to do this using summaries (if you need the data in a group header) is to create a formula like this:
if {table.datefield} < {%CurrentMonth} then {table.numberfield}. You can use this formula in summaries.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ginkoba]

Thanks much LBass and Dell. I will give this a shot and let you guys know as soon as I can.