topic Re: Subtracting one Date from Another in QlikView

Subtracting one Date from Another

Anonymous — Fri, 15 Jan 2016 19:53:16 GMT

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!

Re: Subtracting one Date from Another

sunny_talwar — Fri, 15 Jan 2016 20:04:04 GMT

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.

Re: Subtracting one Date from Another

nagaiank — Fri, 15 Jan 2016 20:21:35 GMT

Did you look at Calculating Months difference between two dates

Re: Subtracting one Date from Another

Anonymous — Fri, 15 Jan 2016 20:26:03 GMT

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!

Re: Subtracting one Date from Another

sunny_talwar — Fri, 15 Jan 2016 21:15:09 GMT

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:

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

];