recurring 3 month date.

[DozAdmin]

This is an extension of another thread but as it is more specific I think will be better by itself.

I have a report that needs a Previous and Next date, recurring every 3 months. The starting date can be any, including the last day of the month.

If the starting date is 1/31/07, and today's date is 2/23/09, then I would need to show 1/31/09 and the previous and 4/30/09 as then Next. The data I have to work with is starting date and day of the month - if the day of the month is 31, then it is always the last day. Data would look like this

Based on currentdate=2/23/09
Startdate Day Previous Next
1/31/07 31 1/31/09 4/30/09
6/15/08 15 12/15/08 3/15/09
8/21/06 21 2/21/09 5/21/09

I don't know datevar or how to create date groups ????
I'm thinking I would need to set some variable to say
if the month is 1,4,7,10 then Group1
if the month is 2,5,8,11 then Group2

????

Then determine where the current date falls in the group, and set the next and previous accordingly.

Any ideas on how to do this?

 

[IanWaterman]

No doubt the logic could be developed to to build formula to express Previous and Next. However, it is sometimes helpful to explain why you would want to do this, as a simpler solution may be possible.

Is the Start date a parameter or a field?

Ian

 

[DozAdmin]

The start date is a date field, field name is PLANEESTAT.REBSTARTDATE. I typed a different name in previous post to save typing - now I'm pasting

I am thinking I could do....

if currentdate > date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE})) and
currentdate < date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+3,day({PLANEESTAT.REBSTARTDATE}))
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+3,day({PLANEESTAT.REBSTARTDATE}))

But I need the year to roll over when the next year comes and also need to account for last day of the month. I do have a numeric field for the 'day of the month' if this is the number 31 then it is always the last day of the month.

 

[DozAdmin]

I think below will work ??? but I have to adjust the last day of the month.

if currentdate > date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE})) and
currentdate < date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+3,day({PLANEESTAT.REBSTARTDATE}))
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+3,day({PLANEESTAT.REBSTARTDATE}))

else

if currentdate > date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+3,day({PLANEESTAT.REBSTARTDATE})) and
currentdate < date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+6,day({PLANEESTAT.REBSTARTDATE}))
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+6,day({PLANEESTAT.REBSTARTDATE}))

else

if currentdate > date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+6,day({PLANEESTAT.REBSTARTDATE})) and
currentdate < date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+9,day({PLANEESTAT.REBSTARTDATE}))
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+9,day({PLANEESTAT.REBSTARTDATE}))

else

if currentdate > date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE})+9,day({PLANEESTAT.REBSTARTDATE})) and
then date(year(CurrentDate)+1,month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))

 

[IanWaterman]

YOu could use function dateadd()

Look it up in Crystal help, you can add and subtract whole months, caters for changes in month size without having to worry.

Best way to determine last day of month is to determine 1st day of following month and subtract 1 day.

eg using current date

Date(Year(currentdate), month(currentDate), 01)-1

This rolls back years too, so if cuurent date is in Jan 2009, it will return 31 dec 2008.

Hope this gives you some pointers.

Ian

 

[DozAdmin]

Can I set some kind of variable or statement that says
Rebalance date equals year(currendate)month(rebdate)day(rebate)

To prevent having to type it out?

We are always comparing the current year but month and day of the rebalance date.

 

[IanWaterman]

Yes, create a formula to evaluate

@Vars
Whileprintingrecords;

global numbervar startyear:= year(stratDate);
global numbervar startmonth:=month(startdate);
global numbervar startday:=day(startdate);

You can then use these in date formula eg

date(startdate, startmonth, 01)

Ian

 

[DozAdmin]

Thanks Ian!

I think I am 90% there, but my logic lacking. My formula above is missing something.

The quarterly date has only for possibilities,
startdate, startdate+3mos, startdate+6mos, startdate+9mos.

I need my formula to evaluate which of these 4 is the NEXT one, then which is the PREVIOUS one.

my logic of >currentdate and <currentdate +4mos is not complete.

 

[IanWaterman]

Create another formual and just add 3 months/qtr to your Quarterly date formula.

@NextQtr

Dateadd("q", 1, {@quarterformula})

or
@NextQtr

Dateadd("3", 3, {@quarterformula})

Ian

 

[DozAdmin]

I was just thinking only need to compare months, when years come then the comparison is inaccurate.

You lost me on this - what is my quarterly formula?

Here is what I have - it works if the date falls in this year, but next year doesn't fall in the >current <current+4

if date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))> currentdate and
date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE})) < dateadd("m", 4,currentdate)
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))

else

if Date(DateAdd( 'q', 1 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))> currentdate and
date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))< dateadd("m", 4,currentdate)
then Date(DateAdd( 'q', 1 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))

else

if Date(DateAdd( 'q', 2 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))> currentdate and
date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))< dateadd("m", 4,currentdate)
then Date(DateAdd( 'q', 2 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))

else
if Date(DateAdd( 'q', 3 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))> currentdate and
date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))< dateadd("m", 4,currentdate)
then Date(DateAdd( 'q', 3 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))

 

[DozAdmin]

I'm trying this...
what if I put
Local numbervar m:=month({Planeestat.rebstartdate});
Local numbervar mc:=month(currentdate);

Then run the comparison part of my fomula only on the months?

 

[IanWaterman]

I did not know what you called your big formula above to determine quarters, so I referred to it as {@quarterformula}.

Not sure what you mean

it works if the date falls in this year, but next year doesn't fall in the >current <current+4

Why are you worrying about next year?

Ian

 

[DozAdmin]

using my formula a firstrebdate of 7/04/2007 would return false or blank because the formula option that would hit the correct date 4/04/2009 would have to add 9 mos or 3 qtrs. But this will not work with the formula because the year would be 2010.

My idea to extract the months and compare them only doesn't work because 7+9 doesn't get 4 it gets 16.

Sorry I'm not communicating this very well! I appreciate the help. I'm sure I'm missing something that makes me over complicate this.

 

[DozAdmin]

Well there may be a better way but I got it.. I am comparing the months but adding that the number must also be less that 12, I also had to add -3, -6, -9 months as options.


Local numbervar m:=month({PLANEESTAT.REBSTARTDATE});
Local numbervar mc:=month(CurrentDate);

if m > mc and m<(mc+4)
then date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))
else
if (m+3)<=12 and (m+3) > mc and (m+3)< (mc+4)
then Date(DateAdd( 'm', 3 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))
else
if (m+6)<=12 and (m+6) > mc and (m+6)< (mc+4)
then Date(DateAdd( 'm', 6 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))
else
if (m+9)<=12 and (m+9) > mc and (m+9)< (mc+4)
then Date(DateAdd( 'm', 9 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))
else
if (m-3)<=12 and (m-3) > mc and (m-3)< (mc+4)
then Date(DateAdd( 'm', -3 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))
else
if (m-6)<=12 and (m-6) > mc and (m-6)< (mc+4)
then Date(DateAdd( 'm', -6 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))
else
if (m-9)<=12 and (m-9) > mc and (m-9)< (mc+4)
then Date(DateAdd( 'm', -9 , date(year(CurrentDate),month({PLANEESTAT.REBSTARTDATE}),day({PLANEESTAT.REBSTARTDATE}))))

 

[DozAdmin]

Grrr! Well I have it accept for the last day of the month thing. with this formula, if the firstrebday is on the 30th, then the formula will retun the 30th, unless it's feb. I've seen a couple last day formulas but not sure I know how to insert them in to this.