Formula query

Not sure this in the relevant section but happy to move it if it is.

I produce reports every couple of weeks to show new membership and membership renewals. These are mostly correct but we do have some anomalies and I've been trying to resolve this, with some progress.

We are currently using Crystal Reports 9 and one of the lines of formula is: if {Subscriptions.BEGIN_DATE} >= DateAdd ('m', -2, {?StartDate}) then '*' else '';

What this is saying is, if this is a new member (based upon the subscription Begin Date), place an asterix * in the respective column. If it's a renewal, leave it blank. What is not clear is what does ('m', -2, {?StartDate}) actually mean in this regard?

I have a very clear example where a renewing member incorrectly exports with an * yet, if I create the formula to say ...('m', -1, {?StartDate}) then the report exports correctly without the *.

Any advice greatly appreciated.

Chris.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

dateadd

Hi, Chris,

The dateadd function takes three parameters: datepart, number, date.

The third parameter is the easiest to explain. That's the date you want to adjust.

The datepart represents what part of the date you want to increment. They usually don't have quotes around them, but Crystal may prefer the quotes. Typical values are year, month, day, hour, minute, second, and several others you probably won't ever use (quarter or nanosecond anyone?).

Due to some vagueness in the documentation, many people seem to think the abbreviations are the only options instead of the regular codes. I prefer to use the spelled out datepart, since it isn't obvious to a casual reader whether m stands for Month or Minute.

The number is how many of the datepart things you would like to add. To subtract, use a negative number.

So in your example, datepart(month, -2, {?StartDate}) evaluates to the date that is exactly two months before {?StartDate}. If StartDate is December 1, 2016, the result would be October 1, 2016.

Using dateadd is much safer over other techniques that slice the date into a string and try to put it back together. For example (using US dates), if you are trying to add one month to '10/31/2016', changing the string to '11/31/2016' will give an error when you try to use it as a date, but dateadd(month, 1, '10/31/2016') will correctly return 11/30/2016.

--
Bruce Wilson
Senior Director, Technology Solutions
RSM US LLP

Hello Bruce Thank you for

Hello Bruce

Thank you for your reply which is very helpful and is making this more clear to understand - although I’m still a little unclear about how this formula works in the context of the report. I think the next step for me is to do some analysis on these date parameters and formulas in relation to some membership records.

Thank you again, very much appreciated.

Chris

A little more interpretation

In the context of the report, {?StartDate} would be a parameter, probably provided by the user, though it could be passed through from iMIS or other software. Subscriptions.BEGIN_DATE indicates when the subscription started -- kind of like a join date.

So I would expect the report to show the * indicator for any subscription that had started within two months before the {?StartDate} parameter.

--
Bruce Wilson
Senior Director, Technology Solutions
RSM US LLP