Months between two dates

[manoj928]

Hello All,

Is there any way to calculate months between two dates using Rules?

Please help.

Thanks,
Manoj

[tomok]

It depends. Since there is no such concept as "Date" in TM1, how are you storing it? Text, or as a serial number. If it's a serial number just subtract them. If it's text then you'll have to get more creative and parse it out. There are plenty of examples on this site. Use the search feature.

[manoj928]

Thats not an issue, we can use either of them (as string or as serial number).

Here is what we are currently on:

to get difference, we tried
(DAYNO(Date1) - DAYNO(date2))/30, but it does not fit into to get exact result as EXCEL DATEDIF() function gives. Actually we want to implement DATEDIF function in TM1 to get correct result.

[tomok]

to get difference, we tried
(DAYNO(Date1) - DAYNO(date2))/30, but it does not fit into to get exact result as EXCEL DATEDIF() function gives.

Why would you expect it to? You're just taking an integer and dividing it by 30. Unless the difference is a multiple of 30 the resulting number will be a fraction. You have to decide whether to truncate to the right of the decimal point (which would be the number of whole months between the two dates), or to round up or down, and when to round up or down. You are creating a function in a rule where no such functionality exists. Therefore, it is incumbent upon you to consider all the scenarios and program accordingly. We don't know what your boundaries are so we can't give you an answer.

[paulsimon]

Hi

Here is an example for calculating the whole number of months between two dates which are input as date serials. (If is best to put a format of date on the two input date measures).

For the example, I used a simple 2D cube with one dimension zTD_Any having a single element called 'Any' and then a second dimension with the measures.

It would be possible to do all this in one statement, but I think it is easier to understand if it is split out into separate measures.

In the measures there are two inputs Date 1 and Date 2 which take Date Serials.

Date 1 YYYY-MM-DD and Date 2 YYYY-MM-DD are strings (text), used to hold the result of converting the Date Serials to Strings as we can more easily then split the dates into their Day, Month, Year parts.

Day 1, Day 2, Month 1, Month 2, Year 1, and Year 2 are all derived from splitting the date strings above.

Day Diff is a consolidation of Day 1 and Day 2 with a weight of -1 on Day 2.
The same approach is used for Month Diff and Year Diff
Year Diff in Months is a consolidation of Year Diff with a weight of 12 to convert from years to months.

Code: Select all

skipcheck ;

# Adjust using Max and Min so we can rely on what goes into Date 1 YYYY-MM-DD being the higher of Date 1 and 2
# and for Date 2 YYYY-MM-DD being the lower of Date 1 and 2

# The fiddle factor of 21916 just seems to be necessary due differences in dates based on a start of 1900 or 1960.

['Date 1 YYYY-MM-DD']=S: DATE( Max( ['Date 1'] , ['Date 2'] )  - 21916 , 1 ) ;
['Date 2 YYYY-MM-DD']=S: DATE(  Min( ['Date 1'] , ['Date 2'] )  - 21916, 1 ) ;

['Day 1']=N: DAY( DB( 'zTD_Test' , !zTD_Any, 'Date 1 YYYY-MM-DD' ) ) ; 

['Day 2']=N: DAY( DB( 'zTD_Test' , !zTD_Any, 'Date 2 YYYY-MM-DD' ) ) ; 

['Month 1']=N: MONTH( DB( 'zTD_Test' , !zTD_Any, 'Date 1 YYYY-MM-DD' ) ) ; 

['Month 2']=N: MONTH( DB( 'zTD_Test' , !zTD_Any, 'Date 2 YYYY-MM-DD' ) ) ; 

['Year 1']=N: YEAR( DB( 'zTD_Test' , !zTD_Any, 'Date 1 YYYY-MM-DD' ) ) ; 

['Year 2']=N: YEAR( DB( 'zTD_Test' , !zTD_Any, 'Date 2 YYYY-MM-DD' ) ) ; 

['Whole Months between']  = N:
  ['Year Diff in Months']
  +
  ['Month Diff'] - 1
  +
  IF( ['Day Diff'] >= 0 , 1 , 0 ) ;

feeders ; 

# Feed consols as a short cut to feeding the elements beneath them
# and feed the final result Whole Months Between.

['Date 1']=> ['Day Diff'] , ['Month Diff'], ['Year Diff'] , ['Whole Months Between'] ;
['Date 2']=> ['Day Diff'] , ['Month Diff'], ['Year Diff'] , ['Whole Months Between'] ;

Regards

Paul Simon

[paulsimon]

Hi

Just to mention, you asked for a rule based method so that is what I did. However if I was doing this for real, I would probably have a dim of dates followed by months with the months being of the form YYYY-MM Consolidations of the relevant dates.

So long as the time dim has all dates in order followed by all months in order you can get to the month via elpar from the date. Then the diff in the dimix of the two month elements gives the months between.

To minimise the need to repeat you could put two time dims into a 2D cube and use a rule to calc the months between any two dates. As the calc would be N level and would not need feeders the cube can then act like a function which you can reference in rules etc.

Regards

Paul Simon

[tsw]

Manoj,

Here's what I used, after being corrected in the other thread:

( ((YEAR(DATE(['Date B']-21916)) - YEAR(DATE(DAYNO('<Date A in string>'))) )*12)
+ (MONTH(DATE(['Date B']-21916)) - MONTH(DATE(DAYNO('<Date A in string>'))) ) )

Notes:
Date B is based on time attribute populated by Performance Modeler
Date A is based on string.