# QlikView Documents

Documents for QlikView related information.

# Calculating Months difference between two dates MVP & Luminary

## Calculating Months difference between two dates

The script below calculates the months difference between two given dates.

//Function to calculate Months Difference in script

SET MonthDiff = Num(((year(\$2) * 12) + month(\$2)) - (((year(\$1) * 12) + month(\$1))) + 1);

Now, we can use the MonthDiff as a function

Data:

UniqueNo,

StartDate,

EndDate,

(\$(MonthDiff(StartDate, EndDate)) AS MonthsDifference

FROM DataSource;

In the above load statement we are calculating the months difference between two dates and arriving it as a new column.

We can also use the MonthDiff in expressions also like below

=\$(MonthDiff(Today(), Today() + 65))

Hope this helps others.

Regards,

Jagan.

Tags (1)
Labels (1)
• ### Creating Analytics

Contributor

Valued Contributor

Thats great but we have to take care about the day.  For instance, if (2014-03-06) - (2014-01-02) the answer is 3, but if the StarDate have a day greater than the EndDate, we may have some problems.

This is one of the functions that QV need to integrate. MVP & Luminary

Hi,

Then use this

Num(((year(\$2) * 12) + month(\$2)) - (((year(\$1) * 12) + month(\$1))) + If(Day(\$1) < Day(\$2), 0, 1) )

Regards,

Jagan.

Contributor

currently, it adds "1" to the age of the date, where it should be 0. for instance, when you select a date that is exactly the same as *StarDate*  it gives back '1' instead of 0.

This is caused by the part '+1 ));'

when you erase this part, it will show you the correct age in months.

Best regards,

Dick Not applicable

we are getting the difference of months, then to get the Month Names of relevant years...

how i can link to a table...Help me on this..

Contributor

Hi,

I am not sure what you mean by this, could you please eleborate?

Best Regards,

Dick MVP & Luminary

Note that a DateDiff function is now available in the QlikView Components library http://qlikviewcomponents.org.

-Rob

Contributor

Hey Rob.

What if i would like to work out the date within the month only?

i.e.

Store From = 5/05/2015

Store To = 25/07/2015

to work out the total datediff would be "Store To - Store From" which will give me total of 61 Days

but what if i would like to know the number of storage days in May which should be 26

June, which should be 30

and the number of storage days in July which should be 25?

also how my i deal with situations where "Store To" date does not exist? Not applicable

Hey Jagan,

The function is actually great and works correct but I could not understand how the '\$1' and '\$2' represent the arguments to the function? MVP & Luminary

Yes, those are arguments to the function.

Regards,

jagan.

Version history
Revision #:
1 of 1
Last update:
‎2013-02-26 08:17 AM
Updated by: 