topic Re: Difference in Months, between 2 dates in QlikView

Difference in Months, between 2 dates

QFanatic — Fri, 20 Nov 2020 18:58:42 GMT

hi all

I want to calculate, the difference in months between today() and a CommencementDate in Qlik (true qlik date, format DD/MM/YY)

My statement is "'mm'".

if CommencementDate = 12/02/2015, I get 3021120. Obviously thats not right.

if I change the 'mm' to 'M' or 'MM' i get zero....

what am I doing wrong here?

Thanks

Re: Difference in Months, between 2 dates

rubenmarin — Tue, 10 Nov 2020 12:27:28 GMT

Hi,I think there is no function to calculate months difference, and interval() function anly allows for days, not months, I would try with something like: =(Age(Today(),CommencementDate)*12)+(Month(Today())-Month(CommencementDate))

Re: Difference in Months, between 2 dates

QFanatic — Tue, 10 Nov 2020 12:41:44 GMT

Hello,

I've just stumbled on round((interval#(Today()-CommencementDate, 'DD')/30.5)) and that seems to work ok.

Thank you very much.

Re: Difference in Months, between 2 dates

Spartan27215 — Fri, 03 Dec 2021 19:35:10 GMT

How do you handle A Zero value for Age. From day 1 to day 365/366 is year 0 and anything multipleid by that is 0

Example: Age('12/31/2021', '1/1/2021') = 0 and 0 * 12 = 0 where you would really want 1 i.e. 12 if you are looking for months

However

Age('12/31/2021', '1/1/2020') = 2,

Also if you are wanting something like 3 months as a time interval this does not work and dividing by 365 will not always work because of leap years.

Sounds like a DateDiff function should be added just for this purpose so you can specify that you want the interval in Days, Months, Years.