Qlik Community

QlikView Documents

Documents for QlikView related information.

Calculating Months difference between two dates

MVP & Luminary
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:

LOAD

    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)
Comments
tchovanec
Contributor

This was extremely helpful. Thanks

0 Likes
jolivares
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
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.

dickelsa
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

0 Likes
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..

0 Likes
dickelsa
Contributor

Hi,

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

Best Regards,

Dick

0 Likes
MVP & Luminary
MVP & Luminary

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

-Rob

bonkornwit
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?

0 Likes
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?

0 Likes
MVP & Luminary
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: