topic Month Difference between Two Dates in QlikView

Month Difference between Two Dates

MCFH93 — Wed, 10 Jul 2024 07:55:22 GMT

Hi, I want to calculate month difference between two dates but from what I found out, the formula (Year(A1)*12 + Month(A1)) - (Year(B1)*12 + Month(B1)) will not be correct for all dates. For example as shown below.

Therefore, kindly advise on how to formula above can be modified to include the date calculation or any expression that can be used for the below.

Thank you.

Example (Correct)

A1 = 22 June 2021

B1 = 19 April 2018

Month Difference = 38 Months

Example (Wrong)

A1 = 31 August 2023

B1 = 01 August 2022

Wrong Month Difference = 12 Months

Suppose Correct Month Difference = 13 Months

Re: Month Difference between Two Dates

Anil_Babu_Samineni — Wed, 10 Jul 2024 07:57:40 GMT

Did you try this? "Interval - script and chart function | Qlik Sense on Windows Help"

Re: Month Difference between Two Dates

MCFH93 — Wed, 10 Jul 2024 08:15:18 GMT

I have tried and it did not work as intended.

Re: Month Difference between Two Dates

marcus_sommer — Wed, 10 Jul 2024 08:28:34 GMT

It depends mainly on your definition how the months should be counted - to apply a suitable method.

Within the above logic you may also add any if-loops to compare the day() of the dates.

Another approach might be the following logic:

round((DateA - DateB) / 30)

Instead of round() you may also consider to use floor() or ceil().

Re: Month Difference between Two Dates

rwunderlich — Wed, 10 Jul 2024 14:54:27 GMT

If you want to count the month when the month# if the same, adjust your expression to add 1 when the month# is the same.

(Year(A1)*12 + Month(A1)) - (Year(B1)*12 + Month(B1)) + -(Month(A1) = Month(B1))

-Rob