12 Replies Latest reply: May 15, 2014 9:55 AM by Dirk Schulze

# Generating a new date using a calculated field

Is there a simple[/b] way in Monarch 6.01 to calculate a new date based off a date that exists in the text file I am using?  For instance, if I have a date of 5/15/03 is there an easy way to roll the date back a month to 4/15/03?  There doesn't seem to be a function that would do this for me.  I can do it using a long, complicated calculation but I was hoping for something simpler.

• ###### Generating a new date using a calculated field

Just to confirm my understanding, you need to change the MONTH of a date back one month but keep the DAY NUMBER of the month the same and only alter the YEAR if you are rolling back from January. Have I got that right?

You can of course simply deduct a number of days from the date to generate a new date but that will only work if you make the number of days deducted the same as the number of days in the month you are rolling back to (and allow for leap years). However I think you are hoping for something simpler than that. Am I right?

Grant

Originally posted by skinman:

Is there a simple[/b] way in Monarch 6.01 to calculate a new date based off a date that exists in the text file I am using?  For instance, if I have a date of 5/15/03 is there an easy way to roll the date back a month to 4/15/03?  There doesn't seem to be a function that would do this for me.  I can do it using a long, complicated calculation but I was hoping for something simpler. /b[/quote]

[size="1"][ May 20, 2003, 06:05 AM: Message edited by: Grant Perkins ][/size]

• ###### Generating a new date using a calculated field

Complicated things, dates. Formula likely in any event since there are problems due to the different numbers of days in various months and you really have to define how you want to handle those for your purposes (as would any standard function also need to know).

Some suggestions.

The function MONTH(&lt;date field&gt;)-1 will give you the previous month number except where January needs to become 12 rather than 0. (This could be an intermediate calculated field)

You will still have problems with the last days of  31 day months preceded by 30 day months and always with March going back to February. (Plus Leap Year issues).

This could be addressed by creating a lookup table calculated field that defines what you want to happen. For example you could simply have a record that lists months 0 to 11 (see subtraction result comment above) and the number of days to be deducted (number of days in the month of the calculated MONTH field) and then subtract that number of days from you start date. There will be anomalies with this approach if I have interprted you requirement correctly.

At the other extreme you could create an entire table that lists all ddmm combinations and specifies how many days to deduct for each. Use that number of days in the "(Date)-days" formula.

Obviously you coould also take a shortened version that idea and include only the diffucult dates in the lookup table and have an IF formula that applies some standard rules for the majority of cases but uses the lookup table for the exceptions. A bit of work to set it up but once done easily transportable from model to model if required. It would also allow correction for leap years if that is significant.

I offer the concept of intermediate calculated fields for development purposes. Whilst lookup tables do need to be created in the model many other intemediate calculation can be combined into a formula of course.

I hope this offers some ideas. Many of the other regular forum members are at the conference for another day or so. If you can hang on a while they may well have some better ideas than I have come up with.

Good luck. Let me know what you think - it may spark some alternative thinking from me.

Grant

Originally posted by Grant Perkins:

Just to confirm my understanding, you need to change the MONTH of a date back one month but keep the DAY NUMBER of the month the same and only alter the YEAR if you are rolling back from January. Have I got that right?

You can of course simply deduct a number of days from the date to generate a new date but that will only work if you make the number of days deducted the same as the number of days in the month you are rolling back to (and allow for leap years). However I think you are hoping for something simpler than that. Am I right?

Grant

/size[quote]quote:[/size]Originally posted by skinman:

Is there a simple[/b] way in Monarch 6.01 to calculate a new date based off a date that exists in the text file I am using?  For instance, if I have a date of 5/15/03 is there an easy way to roll the date back a month to 4/15/03?  There doesn't seem to be a function that would do this for me.  I can do it using a long, complicated calculation but I was hoping for something simpler. /b[/quote][/b][/size][/QUOTE]

• ###### Generating a new date using a calculated field

You understood my problem perfectly.  I hadn't even considered a lookup table.  I've got some time to work around this problem, so I'm going to see if any of your ideas will work for me.  Thanks for your help.

• ###### Generating a new date using a calculated field

welcome, skinman.

This is coming to you live, from the 2003 User Conference in Las Vegas!

Not sure if this is what you want, but this will subtract one month from the date, keeping the day itself the same, it accounts for when the date is in January too. For instances, if TestDate = 5/20/2003, it returns, 4/20/2003.  If TestDate = 1/20/2003, it returns, 12/20/2002.

[font="courier"]Ctod(Right(&quot;00&quot;LTrim(Str(If(Month(TestDate)=1,12,Month(TestDate)-1),2)),2)&quot;/&quot;

Right(&quot;00&quot;LTrim(Str(Day(TestDate),2)),2)+&quot;/&quot;

+Str(If(Month(TestDate)=1,Year(TestDate)-1,Year(TestDate)),4))[/font][/quote]

• ###### Generating a new date using a calculated field

Hi Nick,

Hope you have all had a great time in Vegas.

I like your formula and was heading that way (or similar, concentrating only on the month and then specifiying a number of days to deduct  from the original date) myself since I thought that might be OK with skinman's desire to avoid a complicated formula. Focusing only on the month would also mean the results would be completely date format independent.

Then I remembered the problems of dealing with months with different number of days. Handling any 'preferred rules' for that makes things a little more complicated. Then there are Leap Years ....

OK, so once the formula is established you can copy and re-use, but the formula might get a little complex.

A lookup table could be used to define either generic rules or specifics. I would expect that it could also be used to define only the exceptions, thus keeping the table smaller, though the benefit with dates is less clear. For example if your date is 29, 30 or 31 March I guess you need to go back to 28 February. ( Maybe 29 February on a Leap Year but that may not be worth worrying about depending upon the purpose of the report).

I initially looked at a simple lookup table that defined how many days were in the month BEFORE  the month of the original date. You then deduct that number of days and all is well - except for the 31st of months with 31 days and 29, 30 and 31 of March. For these dates you need to define exceptions to standard handling or maybe special rules if that better suits your needs.

If you have a  need for users to be able to interpret what is going on, a formula to deal with this may look complicated! Look up tables would be more understandable and have the same benefits of being replicatable by copy and paste. However that is a matter of personal choice. That the choice exists point to the potential Monarch

offers!

My current (untested!) thinking is to set up 2 lookup tables (for simplicity). One would deal with  the exceptional dates (31sts and the February problem) and the other would define the  'previous month days' list. (December = month 0!). Use an IF statement to check for exceptions

(IF (Exception_Table_Lookup)&lt;&gt;"", (Exception_table_lookup), (Month_Days_Lookup))

for example. If no exceptions (there cannot be too many on the list!) use the other 'number of days in previous month'  table lookup. Deduct  the resulting number of days from the current  date and you should have your answer.

If Leap Years remain a problem that has to be addressed the simple way would be to list Leap Years in a lookup table, check that and if the year of the original date is a leap year and the day in March is 29, 30 or 31, set the new date to 29 Feb - if that is what you want to do.

This could be handled in the formula or by the 'day' lookup table using an input in the MMDD (or DDMM) format, or simply as another calculated field step. For example calculate 3 'work fields' - 'Number of days to educt', 'exceptions' and 'leap year special', all of which define a  number of days to deduct from your original date.

A 2 level nested IF formula (relatively easy to interpret later) should then be able to provide the definitive adjustment for that particular record.

Of course plan D would be to ask Datawatch for a special function that allows a predefined month rollback with perhaps a number of months required parameter?

One for the wish list maybe?

Grant

EDITED: Updated.

Good news which I had overlooked.

Having tested the 2 lookup table concept the even better news is that the (Date)-number of days calculation already understands leap years so no extra processing required. (It's part of the date validation checking - I should have thought of that!)

So 2 tables do it. One to establish the 'default' number of days to deduct (12 rows) and one to establish the exceptions (7 rows). My example of the latter works on the basis of creating a DDMM (European - sorry) entry -  a calculated work field to strip out these values is required - I used a Lsplit formula.

Only needs a single IF formula to get the date.

I can provide the sample model for people to test if you are interested. Send me a mail if you have the address or a private message with your email.

Originally posted by Nick Osdale-Popa:

welcome, skinman.

This is coming to you live, from the 2003 User Conference in Las Vegas!

Not sure if this is what you want, but this will subtract one month from the date, keeping the day itself the same, it accounts for when the date is in January too. For instances, if TestDate = 5/20/2003, it returns, 4/20/2003.  If TestDate = 1/20/2003, it returns, 12/20/2002.

/b[/quote]

[size="1"][ May 21, 2003, 09:34 AM: Message edited by: Grant Perkins ][/size]

• ###### Generating a new date using a calculated field

oh, you brought up a good point I hadn't considered when I posted the formula (it was late, check the time on my earlier post     ).

I hadn't considered months with 30/31 days and February, thus March 30th would give an error with my formula.  I'm sending a PM to you now, as I'd like to see how the lookup table is working.

• ###### Generating a new date using a calculated field

Hmm, yes I noticed the time. And from the style it looked like a very good time was in progress!

But it is only fair to mention that I forgot to consider the odd days in my first concept. Sad as they were always a challenge in one of the software systems I worked with so I should have thought of it sooner.

The adjustments could be put into a formula I reckon, but the look up tables are quite simple to set up. They could also be used for other purposes, like financial period definitions, or a 5 or 6 week back calculation say, using the same principle and simply adjusting the number of days to deduct (or even add maybe).

I look forward to your critique.

Grant

Originally posted by Nick Osdale-Popa:

oh, you brought up a good point I hadn't considered when I posted the formula (it was late, check the time on my earlier post       ).

I hadn't considered months with 30/31 days and February, thus March 30th would give an error with my formula.  I'm sending a PM to you now, as I'd like to see how the lookup table is working. /b[/quote]

• ###### Generating a new date using a calculated field

Well, I have returned from the desert to the land of green grass and internet connectivity.

skinman,

This is an interesting problem tha you have posted. I am sorry that I was not able to look at it sooner.

Grant,

That is an interesting solution that you have come up with. Usually, I am a big fan of lookup tables, but not in this case. It seems a little much to use lookup tables to deal with 6 or 7 exceptions each year.

So, here is what I came up with. I will show the two calculated field version to explain how it works then boil it down to one calculated field.

Note: I am at home and not work. So, this method was tested on a spreadsheet not Monarch. If you get different results, or if these formulas need to be modified, let me know.

Here is the setup:

2. set up a calculated field:

[font="courier"]Name: DateEOPM (that is, Date-End of Previous month)

Type: Date

Formula: DATE-Day(DATE) /font[/quote]This formula will return the last day of the previous month. Note that this will also change the year for January dates.

3. Set up a second calculated field:

[font="courier"]Name: DateLM (that is Date-Last Month

Type: Date

Formula: IF(Day(DATE)&lt;Day(DateEOPM),DateEOPM-(Day(DateEOPM)

-Day(DATE)),DateEOPM)  /font[/quote]That is it. The second formula says, if the day in the DATE is less than the number of days in the previous month, then subtract the difference between the two from the DateEOPM field. Otherwise, use the date of the end of the previous month.

Now that you see how it works. The formula can be boiled down to a single calculated field.

[font="courier"]Formula:DATE-Day(DATE)-(Day(DATE)&lt;(Day(DATE-Day(DATE))))

*((Day(DATE-Day(DATE))-Day(Date))  /font[/quote]As I stated this has not actua1ly been tested in Monarch. If someone could do that and post a reply I would appreciate it.

[size="1"][ May 23, 2003, 01:55 PM: Message edited by: Winn ][/size]

• ###### Generating a new date using a calculated field

By George, I think he's done it! Great work Winn.

Just a little fix to the number of "()" and this is the finished result:

[font="courier"] DATE-Day( DATE ) - ( Day( DATE ) &lt; ( Day( DATE - Day( DATE ) ) ) ) *

( ( Day( DATE - Day( DATE ) ) - Day( Date ) ) ) /font[/quote]

[size="1"][ May 23, 2003, 11:55 AM: Message edited by: Nick Osdale-Popa ][/size]

• ###### Generating a new date using a calculated field

Thanks Nick. Yeah, I figured that I had messed up some paranthesis somewhere, since I had to enter it by hand instead of copying and pasting it from the calculated field window.

• ###### Generating a new date using a calculated field

Winn,

I just had a feeling you would come up with an even better solution! And to think I even (coincidentally) chose your normal favourite route!

I don't think I would have got to your solution anyway - interpeting a formula like that (logical though it is) always makes my brain work overtime. Perhaps I should do a programming course. Still, I picked up on a couple of things that were certainly not at the forefront of my understanding so thanks, a nice way to learn.

Also I had fixated on the 'simple formula' requirement which in turn first led me to the lookup tables. So there is another simple lesson worth remembering and being reminded about regularly, always look for alternative paths, there may be a better (or simply more elegant) way.

Great solution anyway.

Your comments re the efficacy of lookup tables for so few occurrences during the year. Very true but my thought process did not spot the low number of entries at the beginning, although anything more than a few entries would make a formla even more desirable in my view. Anyway, it prompted me to wonder whether there are any performance difference implications between the options. Obviously not a problem with a handful of records and I suspect it would be unimportant with a recent spec. PC and even less important with V7.  However, if there is a difference in performance it might become more noticable with very large data sets. Maybe not for this particular problem but for a common reference point between formulae and calculated fields.

Does anyone have any input on this question?

Grant

Originally posted by Winn:

Grant,

That is an interesting solution that you have come up with. Usually, I am a big fan of lookup tables, but not in this case. It seems a little much to use lookup tables to deal with 6 or 7 exceptions each year.

So, here is what I came up with. I will show the two calculated field version to explain how it works then boil it down to one calculated field.

/b[/quote]

• ###### Generating a new date using a calculated field

Thanks everybody.  I didn't expect such a response, I really appreciate it.  Winn's formula is perfect for my needs (sorry Grant, I never got around to trying your lookup table suggestion).  I wish I had found this forum a year ago.