Select next 3 months for distinct quantities 1

[Debug44]

I have a report that we have been using Parm Promts to enter the start dates for the next 3 months. I am using Crystal version 8.0 connecting to Baan 4cIV.

I am trying to sum the quantity on Order by date ranges.
Ex

Late = (Sum of all quantities with a due date later than today)
if {tdsls041.ddta} < {?today's date} then {tdsls045.oqua} else (0)

Month 1 (Sum of all quantities with a due date from today to the end on Month 1 {current month})
if {tdsls041.ddta} >= {?today's date} and {tdsls041.ddta} < {?Month 1 start date} then {tdsls045.oqua} else (0)

Month 2 (Sum of all quantities due next month)
Etc, Etc

I am having problems with the formulas to select the start of the next month. It workes fine when left as parm prompts as the dates are being manually entered. This does not allow the report to be auto-ran.

I need to be able to have it define the end of current month and start/end of subsequent months...

I am using the tdsls041.ddta to select date and the .oqua for the quantity.
Any help would be appreciated as the different formats I have been trying, to get this as a hard coded formula that changes based on what today is, have not worked.
Thanks in advance
Bart

 

[kskid]

I use 8.5 with the dateserial function so I am not sure this would work in 8.0

//@Month 1
if {tdsls041.ddta} in
 [
  {?PDate} to_ 
  dateserial(year({?PDate}),month({?PDate])+1,1)
 ]
then
 {tdsls045.oqua}
else
 0

//Month 2
if {tdsls041.ddta} in
 [
  dateserial(year({?PDate}),month({?PDate])+1,1) to_ 
  dateserial(year({?PDate}),month({?PDate])+2,1)
 ]
then
 {tdsls045.oqua}
else
 0

//Month 2
if {tdsls041.ddta} in
 [
  dateserial(year({?PDate}),month({?PDate])+2,1) to_ 
  dateserial(year({?PDate}),month({?PDate])+3,1)
 ]
then
 {tdsls045.oqua}
else
 0

Substitute {?PDate} with your own Start Date Parameter. I just used a shorter name for easier reading. Also, the "to_" means excluding the end date, which in these cases is the first day of the following month.

Cheers,
-LW

 

[Debug44]

Could {?PDate} = Today's date? As i would like this report to run at night with ZERO input as to the Parms?

Thank you very much for the help...

 

[kskid]

Just substitute CurrentDate for ?PDate

-LW

 

[kskid]

By using CurrentDate, you wouldn't need any parameters. Crystal will use the system date at the time the report is started.

 

[Debug44]

I tied the above and it worked like a champ...
Thanks for the help...
One ?last? question...If I use the formulas as above, it will include the 1st day of Month 3 into the #'s for month 2 won't it?
dateserial(year({?PDate}),month({?PDate])+2,1) to_
dateserial(year({?PDate}),month({?PDate])+3,1)
I am assuming the date serial as stated is taking current month (+2, 1) where one equals the defined day date?
Or does the to_dateserial prevent that?

I may be Way off base...am I?

Thanks again

Bart

 

[kskid]

dateserial(year({?PDate}),month({?PDate])+2,1) to_
dateserial(year({?PDate}),month({?PDate])+3,1)

The to_ will not include month({?PDate])+3,1

If it is too confusing, you could also use

dateserial(year({?PDate}),month({?PDate])+2,1) to
dateserial(year({?PDate}),month({?PDate])+3,1-1)

Noticed I subtracted 1 day from the 1st of the month which will give the last day of the previous month

Hope this helps.

 

[Debug44]

I used the -1 configuration and it worked great...
All the #'s match up to the previous report.
Thanks again for the help