Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

29 Replies
Not applicable
Author

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

I.e. StartDate - EndDate  ?

Not applicable
Author

Look at the entry I left on your original post.

Anonymous
Not applicable
Author

@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 DateCompleted Date
21-Mar-199731-Dec-2030
01-Jun-201031-Dec-2030
10-May-201331-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

How Do I Get the Number of Months Between Two Dates.jpg

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor

Great tip!
shilpasingla
Contributor
Contributor

This worked, thanks.

simonaubert
Partner - Specialist II
Partner - Specialist II

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

Bi Consultant (Dataviz & Dataprep) @ Business & Decision
Uppiskalle
Contributor
Contributor

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

manishshukla86
Contributor II
Contributor II

Thanks  a lot!

It is working fine.

Ribeiro
Specialist
Specialist

thanks
Neves