Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Subtracting one Date from Another

Hello,


I am trying to determine the months elapsed from a field [Commitment Cal Day Dt] and 12/31/14.  The format of the field 'Commitment Cal Day Dt' is D/M/YYYY or DD/MM/YYYY depending on if the day and date have 2 digits.  I tried using the following line of code in my script. 

year(MakeDate(2014,12,31))*12 +month(MakeDate(2014,12,31)) - (Makedate([Commitment Cal Day Dt],01)) as [Months Elapsed],

It gives me a large negative number.  Any thoughts?

Thank you!

4 Replies
sunny_talwar

You are using MakeDate() function. Is [Commitment Cal Day Dt] a year field or date? If it is already date you don't need to use MakeDate.

nagaiank
Specialist III
Specialist III

Anonymous
Not applicable
Author

It is already a date.

Would it simply be as follows?

year(MakeDate(2014,12,31))*12 +month(MakeDate(2014,12,31)) - [Commitment Cal Day Dt],01 as [Months Elapsed],

I still get a large negative number.  I appreciate any tips!

sunny_talwar

Month calculation tend to be a difficult one. Try this may be, I have used in one of my applications. For me the exact number of months was not very important so I think in particular cases it calculates a extra month. But you can fine tune this for your requirements.

If(MakeDate(2014,12,31) > [Commitment Cal Day Dt], 0,

     If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

             Month([Commitment Cal Day Dt]) - Month(MakeDate(2014,12,31)),

             12 - Month(MakeDate(2014,12,31) ) +  Month([Commitment Cal Day Dt])

     )

+

     If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

          ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ))*12),

          ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ) - 1)*12)

     )

)

Attaching a sample:

Capture.PNG

Script used:

Table:

LOAD *,

  If(MakeDate(2014,12,31) > [Commitment Cal Day Dt], 0,

     If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

             Month([Commitment Cal Day Dt]) - Month(MakeDate(2014,12,31)),

             12 - Month(MakeDate(2014,12,31) ) +  Month([Commitment Cal Day Dt])

     )

+

     If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

          ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ))*12),

          ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ) - 1)*12)

     )

) as Month_Elapsed;

LOAD * Inline [

Commitment Cal Day Dt

01/25/2015

04/20/2013

05/25/2015

12/20/2015

];