how to show only records stopping at last full month 1

[JSeidel]

this is what I wrote - but I still get June data in my graph and tabular data...

//to differentiate between records for the last full month and current month records which shouldn't be included in the analysis
WhileReadingRecords;
booleanvar VisitInMonthFg;
If {OrderVolume.OrderStopDateTime} In DateAdd ("m", -12, CurrentDate) To CurrentDate
Then VisitInMonthFg := True
Else VisitInMonthFg := False

 

[Naith]

That is everything in the last 12 months.

Use "in LastFullMonth" instead of "In DateAdd ("m", -12, CurrentDate) To CurrentDate"

Naith

 

[Naith]

Sorry, I mean:

WhileReadingRecords;
booleanvar VisitInMonthFg;
If {OrderVolume.OrderStopDateTime} In DateAdd ("y", -1, CurrentDate) To CurrentDate-(Day(CurrentDate))
Then VisitInMonthFg := True
Else VisitInMonthFg := False

Naith

 

[JSeidel]

I get a message saying too many arguments have been given to this function. I need to have data for last full month and for the previous 12 rolling months..

 

[JSeidel]

still isn't working..

 

[Naith]

Can you paste your current formula, since you applied the change above? I'm looking at the syntax, and it looks fine.

Naith

 

[JSeidel]

//to differentiate between records for the last full month and current month records which shouldn't be included in the analysis
WhileReadingRecords;
booleanvar VisitInMonthFg;
If {OrderVolume.OrderStopDateTime} In DateAdd ("y", -1, CurrentDate) To CurrentDate-(Day(CurrentDate))
Then VisitInMonthFg := True
Else VisitInMonthFg := False

it doesn't throw an error but still gives me june data.

 

[Naith]

Sorry, you've inherited my typo there:

If {OrderVolume.OrderStopDateTime} In DateAdd ("yyyy", -1, CurrentDate) To CurrentDate-(Day(CurrentDate))

CurrentDate-(Day(CurrentDate)) should return you the last day of last month. If you use that part on it's own, it will return May 31st.

Nevertheless, there's always more than one way to skin a cat. Try this instead:

If {OrderVolume.OrderStopDateTime} In DateAdd ("yyyy", -1, CurrentDate) To Maximum(LastFullMonth)

Naith

 

[JSeidel]

worked great -thanks!!!

 

[IdoMillet]

Naith,

If you run this today, wouldn't this "truncate" the information from June 2002 to include only 6/30/2002
(instead of including all of June 2002)?

I think you need to ensure the "lower bound" starts at the beginning of the month (by subtracting the day in the month number or by constructing the lower bound as Previous Year, Previous Month, and day 01.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

I actually thought that was what was required, but having read your post, that would seem to make more sense.

The corrected formula excerpt would look like:

CDate(Year(DateAdd('m',-13,CurrentDate)),Month(DateAdd('m',-13,CurrentDate)),01)

Well spotted, Ido.

Naith

 

[IdoMillet]

This may also do the trick :
-----------------------------------------------------------
DateDiff('m', {OrderVolume.OrderStopDateTime}, CurrentDate) > 0 AND
DateDiff('m', {OrderVolume.OrderStopDateTime}, CurrentDate) < 13
-----------------------------------------------------------

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Ido: I don't think that will pass the SQL to the database.

Create 2 formulas, as in startdate and enddate and use your < 13 formula in a cdate to create the start date, and use cdate(year(currentdate),month(currentdate),1)-1 to get the enddate.

Now reference those dates in the record selection formula.

Same result, plus it passes the SQL.

-k