Date round up or down?

[LaRetta]

We have contracts with StartDate and ExpireDate (both dates).  We have single-record Admin table where NumberOfMonths is stored (number).  I wish to pin down 'what is a month'.  Well, not me actually, but the city using this file needs to pin down their interpretation so there will be a checkbox in the Admin table which applies this theory:

If the day of ( StartDate + NumMonths ) is not the same as the day of the StartDate, consider adjusting the date according to flag field in Admin (adjustDateDownFlag)

FileMaker handles automatically adjusting FORWARD similar to:

Date ( Month ( StartDate ) + NumMonths ) ; Day ( StartDate ) ; Year ( StartDate ) )

What I want is checkbox which says no, jump backwards instead when the DAY can not be the same - this city wants to handle the contract's expiration based upon the short date results.  I realize (I think) that there are easy patterns (please see screen shot), depending upon which of two calculations in attached month.fp7 file.  So I can get the results I want but it is UGGLLEEEE. 

Of course I do not wish to use calculations referencing other calculations - this file was pattern hunting only.  Just because I can identify pattern in the months, once several months are spanned, the results may not remain consistent.  Example - it can't go only by the StartDate as you see 63 and 64.  Just adding a week when same start date, changed the results.  So I need help here ... well, I always do but I mean specifically about FileMaker. 

The adjustedExpirationDate calculation shows the result I'm after.  BTW, I have NumMonths as regular number and not global so I could view multiple dates in single view.

 

 

 

month.fp7

Oh!  Because, just because I see the pattern does not mean the theory will hold.  I think it will be BUT 63 and 64 now throws me.

Edited November 10, 2015 by LaRetta

[comment]

I am trying to figure out what your question is ...

44 minutes ago, LaRetta said:

We have contracts with StartDate and ExpireDate (both dates).  We have single-record Admin table where NumberOfMonths is stored (number).

If you have both StartDate and ExpireDate, then what is the role of NumberOfMonths?

Going by your calculation, you want to calculate the ExpireDate from StartDate and NumberOfMonths?

44 minutes ago, LaRetta said:

If the day of ( StartDate + NumMonths ) is not the same as the day of the StartDate

This can happen only when the target month has less days than the starting month. In such case, the result will "spill over" into the following month - as shown in your first example where the 31st day of February comes out as March 2.

To prevent this, could can do:

Let ( [
expDate = Date ( Month ( StartDate ) + NumMonths ; Day ( StartDate ) ; Year ( StartDate ) ) ;
limit = Date ( Month ( StartDate ) + NumMonths + 1 ; 0 ; Year ( StartDate ) )
] ;
Min ( expDate ; limit )
) 

Or - same thing - what you did in your cRoundDown calculation =

Let ( 
expDate = Date ( Month ( StartDate ) + NumMonths ; Day ( StartDate ) ; Year ( StartDate ) ) 
;
If ( Day ( expDate ) ≠ Day ( StartDate ) ; expDate - Day ( expDate ) ; expDate )
) 

You can of course make the limiting conditional - but I believe your client should figure out their policy once and for all.

Edited November 10, 2015 by comment

[LaRetta]

Good morning!

My apology - it was late.  Yes, it is the expiration date we wish to calculate.  And this file is used currently by many different clients (they each have their own file) and they each must set their file configuration on how they wish to administer their contracts, specifying how many months and which way to 'round'.  :-)

I also realize that, by using an Admin field to flag which calculation to use ( and the number of months to calculate ) that the expiration date will become unstored making it slow in searching so I will instead use an Auto-enter (replace).  It will then re-evaluate if StartDate is changed ( based upon the city's current setting and NumMonths ) but will not adjust if NumMonths or AdjustDateDown flag is changed in Admin table only and that is fine.

I like your calculation better ( no surprise ).  I was originally thinking it could be a single calculation where we used a boolean test * 1 as the if/else portion to round up or down but I never could get there with it.  So to make this conditional based upon AdjustDateDown flag - just wrap with an If() test?

If ( AdjustDateDown ;
	
Let ( [
expDate = Date ( Month ( StartDate ) + NumMonths ; Day ( StartDate ) ; Year ( StartDate ) ) ;
limit = Date ( Month ( StartDate ) + NumMonths + 1 ; 0 ; Year ( StartDate ) )
] ;
Min ( expDate ; limit )
) 
;  // else
Date ( Month ( StartDate ) + NumMonths ) ; Day ( StartDate ) ; Year ( StartDate ) )
)

Do you see any other problems with this, Michael?  Thank you very much for assisting.  It is greatly appreciated.  

[comment]

11 minutes ago, LaRetta said:

this file is used currently by many different clients (they each have their own file) and they each must set their file configuration on how they wish to administer their contracts

I realize this is outside the realm of calculations and deep into the business rules territory, but I would still ask: do you really anticipate a client opting for the default Filemaker method of adding months - the one you called "rounding up"? I have never seen a business that operates on a monthly basis embrace this rule. In most cases, they will count full months only, and pro-rate the first and/or last partial months.

Speaking of business rules, I believe it's also customary to calculate the expiry date as the last day that still counts - i.e. =

expDate = Date ( Month ( StartDate ) + NumMonths ; Day ( StartDate ) - 1 ; Year ( StartDate ) )

 

[LaRetta]

5 minutes ago, comment said:

... do you really anticipate a client opting for the default Filemaker method of adding months - the one you called "rounding up"?

Currently, system adds 365 days for a year contract so it seems they round up.   I too found it unpredictable which is why I wanted to include this flag.  

We are making the change to using 'months' for simplicity since cities use 3, 6 or 12 months or more.  I do not know the rules of each city and this may be YAGNI but, by including this test, we should be covered no matter an individual city's requirements.  

And yes, it should be non-inclusive - thank you for the catch!

[comment]

1 hour ago, LaRetta said:

I do not know the rules of each city and this may be YAGNI but, by including this test, we should be covered no matter an individual city's requirements.

Well, it's your call. But, if you do want to give them the choice, you must also give them the opportunity to change their mind at any time. So no, the expiration date should not be an unstored calculation, and the "flag" needs to be looked up into a local field (or set by a script). Then the calculation can be just:

Let ( [
expDate = Date ( Month ( StartDate ) + NumMonths ; Day ( StartDate ) - 1 ; Year ( StartDate ) ) ;
limit = Date ( Month ( StartDate ) + NumMonths + 1 ; 0 ; Year ( StartDate ) )
] ;
If ( AdjustDateDown ; Min ( expDate ; limit ) ; expDate )
) 

 

[LaRetta]

On November 10, 2015 at 1:14:02 PM, comment said:

 So no, the expiration date should not be an unstored calculation, and the "flag" needs to be looked up into a local field (or set by a script).

Agreed, the city needs to have the option of changing their mind.  So Admin handles the default NumMonths and also the AdjustDateDown flag (are there better names for them?).   Wouldn't they both need to be local in case either were changed later?  Still, it would need to be auto-enter since the User is allowed to override the values (Fee which is looked up from Admin and expiration date ) -  up until the Contract's status is changed from "new" to something else.

If the business changes their mind, script or trigger in Admin could 'find new' Contracts and correct them.  Is this what you have in mind?  I hesitate to use Relookup because I don't want all lookups to recalculate - only the single expiration date.

Edited November 12, 2015 by LaRetta

[comment]

2 minutes ago, LaRetta said:

Wouldn't they both need to be local in case either were changed later?

Certainly.

 

3 minutes ago, LaRetta said:

If the business changes their mind, script or trigger in Admin could 'find new' Contracts and correct them.

I am not sure you need to provide for them changing their minds retroactively.

 

3 minutes ago, LaRetta said:

So Admin handles the default NumMonths and also the AdjustDateDown flag (are there better names for them?).

I don't have anything real good, I am afraid. I thought of maybe using something with "duration" or "term" in it for the former, and ClipExpDateToEndOfMonth for the latter.

[LaRetta]

This is perfect.  Thank you so much for it!   

BTW, existing data ... Users entered their own expiration dates and if StartDate was 6/5/2015, they put expiration dates as 6/5/2016 (for example).  As you indicated, this isn't correct (it is 366 days) but it affirms that they prefer 'same day' which is now in place, allowing business rules to decide what to do if the day is different.