date formula 1

[nb4884]

Hi All,

I want to write a formula for date and costs, any syggestions will be very helpful.
Here's the scenario:

Date Amount
02/22/11 100
03/12/11 100
04/13/11 50

Now, need a formula if dates are between 04/01/10-03/31/11 then sum(Amount) - in this scenario the result will be 200.

Thanks!

 

[HowardHammerman]

Two solutions:
I am assuming that your date field is really a date-time field:

1) Create a fomula like this:
whileprintingrecords;
datefield mydate := date({table.datefield};
if mydate in ctod("04/01/10") to ctod(03/31/2011") then {table.amount} else 0

Then sum that field in the usual way

2) create a running total that sums amount with a evauate formula set to your dates.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[nb4884]

Thanks Howard:

I tried the 1st approach , gives me an error highlighting the 2 lines, " reminaing past is not the part of the formula...."

datefield mydate := date({table.datefield};
if mydate in ctod("04/01/10") to ctod(03/31/2011") then {table.amount} else 0

On the second approach what is the evaluate formula for dates??

Yes, datefiled is date-time value and I am using CR XI

Thanks

 

[HowardHammerman]

Many applogies! I work with another report writer and sometimes I get confused. Here is the correct version of solution #1:

whileprintingrecords;
dateVar mydate := date({EMPLOYEE.BIRTH_DATE});
if mydate in date(2010,04,01) to date(2011,03,31) then 1 else 0

On the second approach use the following formula for the evaluation:

whileprintingrecords;
dateVar mydate := date({EMPLOYEE.BIRTH_DATE});
mydate in date(2010,04,01) to date(2011,03,31)

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[nb4884]

Thanks Howard , I tried the first option and it works great

Do you think we can build a general formula for the year, keeping date and month the same. For ex this report has a meaning if run in those dates , but if I wanted to run after 2 yrs where the dates will be 04/01/2012 - 03/31/2013 and so on. Can I build a general formula for this , so the report shows values based on the year it is run?

Thanks

 

[HowardHammerman]

Sure thing! Here you go.

whileprintingrecords;
numbervar yr := year(CurrentDate);
dateVar mydate := date({EMPLOYEE.BIRTH_DATE});
if mydate in date(yr-1,04,01) to date(yr,03,31) then 1 else 0

Notice the use of "yr-1" If you want to get even more advanced you can add a parameter field to get the number of years from the user when he/she runs the report.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[nb4884]

Thanks Howard , that works