Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gregoryt40
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
Not applicable

Re: Subtracting one Date from Another

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
Not applicable

Re: Subtracting one Date from Another

gregoryt40
Not applicable

Re: Subtracting one Date from Another

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
Not applicable

Re: Subtracting one Date from Another

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

];