Qlik Community

QlikView Documents

Documents for QlikView related information.

Calculating Months difference between two dates

jagan
Esteemed Contributor III

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

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.

jagan
Esteemed Contributor III

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

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

dickelsa
Contributor

Hi,

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

Best Regards,

Dick

rwunderlich
Esteemed Contributor III

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?

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?

jagan
Esteemed Contributor III

Yes, those are arguments to the function.

Regards,

jagan.

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