How Do I Get the Number of Months Between Two Dates?

[#1]

Other posts here in the forii have mentioned Interval() to get the number of days or months between two days.  However, I have not found a format code to get months.  Either of these expressions...

=Interval#(Today() - [Set up date], 'YY-MM-DD')

=Interval#(Today() - [Set up date], 'YY-MM')

...give me the number of days since [Set up date], but I need months.  Using 'MM' gives the value "2614" for all dates from 2005 to yesterday - I have no idea where that number comes from.  The doc page for Interval() is of course useless.

If there's a simpler way to get the number of months between two dates, such as is present in any RDMS (e.g., DATEDIFF() in MSSQL and MySQL, MONTHS_BETWEEN() in Oracle), I am all ears.  If someone suggests adding the difference in years times 12 to the difference in months, I fear I may lose any remaining faith in QlikView.

Thank you!

[#2]

Is there a way to accommodate two date fields with this solution ?

I.e. StartDate - EndDate  ?

[#3]

Look at the entry I left on your original post.

[Anonymous]

@rwunderlich,

Hi Rob,

I am still lost in your expression. being New to QV & Scripting but is been like thrown into the deep end.

  Data set i have:

Start Date	Completed Date
21-Mar-1997	31-Dec-2030
01-Jun-2010	31-Dec-2030
10-May-2013	31-Dec-2030

1. find the number of months between the dates
2. find the number of Years between the dates
3. Do not load/(drop) data below certain year stamp
   

SUP500:

LOAD

  District,

[Contract No],

if(Left([Contract No],3)='FPA','FPA','CONTRACT') as TYPE,

     [Tender No],

     [Supplier Name],

     [Contract Description],

     [Start Date],

     [Completed Date],

     SET MonthDiff=((year[Completed Date])*12)+month[Completed Date])) - (((year([Start Date])*12)+month([Start Date]))),

     Month([Start Date]) as [Start Month],

     Year( [Start Date]) as [Start Year],

[rwunderlich]

The SET statement was more of advanced approach. Forget that for now if you are a beginner.  Code your field in the LOAD statement like this:

((year([Completed Date])*12) + month([Completed Date])) - ((year([Start Date])*12) + month([Start Date])) as MonthDiff

-Rob

[controlling_ms]

Great tip!

[shilpasingla]

This worked, thanks.

[simonaubert]

Hello,

You can also vote on this idea to implement a Datediff function on Sense
https://community.qlik.com/t5/Ideas/New-function-Datediff-to-have-the-difference-between-two-dates/i...

Best regards,

Simon

[Uppiskalle]

I belive the latin plural of forum is fora, not forii. The Anglicized forums is also common.

[manishshukla86]

Thanks  a lot!

It is working fine.

[Ribeiro]

thanks