How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?

[Anonymous]

Hi everyone,

I have two parts of questions regarding date differences:

Part 1

I want to calculate the date difference in terms of month. For example, i am given an application with a certain start date, i want to calculate the duration (or so called "lifespan") of the application. In terms of days, i can use this expression:

Interval(date#(ConvertToLocalTime(UTC(now()), 'GMT+08:00'),'M/D/YYYY h:mm:ss[.fff] TT') -

date(date#([DATEOFBIRTH],'DDMMYYYY'),'M/D/YY h:mm:ss[.fff] TT'), 'd')

How do i go about it in terms of months. I am using Qlik Sense so i dont think the monthDiff expression is available.

Also, this code does not work:

Interval(date#(ConvertToLocalTime(UTC(now()), 'GMT+08:00'),'M/D/YYYY h:mm:ss[.fff] TT') -

date(date#([DATEOFBIRTH],'DDMMYYYY'),'M/D/YY h:mm:ss[.fff] TT'), 'M')

Take note that i want to include the days in terms of calculating the month.

For example, 17042017 - 17032017 = 1 month

                      16042017 - 17032017 = 0 month

Part 2

Once i can calculate and display the difference in months of the application, how do i count the number of applications which has passed a certain amount of time.

For example, if i want to know how many applications have lasted for more than 3 months, my current code which is not working is:

count(Interval(date#(ConvertToLocalTime(UTC(now()), 'GMT+08:00'),'M/D/YYYY h:mm:ss[.fff] TT') - date(date#([DateOfCreation],'DDMMYYYY'),'M/D/YY h:mm:ss[.fff] TT'), 'M') > 3 )

Any suggestions will be greatly appreciated.

Regards,

Gaston

[jonathandienst]

1. No MonthDiff function. Try this:

=12 * (Year(date1) - Year(date2)) + Month(date1) - Month(date2)

(insert the actual date expressions in place of date1/date2.

2. Try

=Count(If(12 * (Year(date1) - Year(date2)) + Month(date1) - Month(date2) > 3, <expression>))

(insert correct date expression and the expression that you are trying to count)

[Anonymous]

Hi Jonathan,

Thanks for your help. However i am unable to use the equation as it doesn't work.

12 * (Year(UTC(now())) - Year([DateOfCreation]) + Month(UTC(now())) - Month([DateOfCreation])

Doesn't this equation pose a problem. For example 01032017 - 28022017 would produce the result as one month. However in fact, only one day has passed. I want to include the days in counting the month difference. If there is no way, i can just count the days difference and divide it by 30.

I am unable to get the 2nd part right too.

Count(if(12 * (Year(UTC(now())) - Year([DateOfCreation]) + Month(UTC(now())) - Month([DateOfCreation]) > 3, <expression> ))

May i have an example of <expression>.

Huge thanks,

Gaston

[jonathandienst]

>>12 * (Year(UTC(now())) - Year([DateOfCreation]) + Month(UTC(now())) - Month([DateOfCreation])

Missing a parenthesis:

=12 * (Year(UTC(now())) - Year([DateOfCreation])) + Month(UTC(now())) - Month([DateOfCreation])

What values do Year([DateOfCreation]) and Month([DateOfCreation]) give?

Is DateOfCreation a proper numeric date field?Or is it a number in the form ddmmyyyy?

If the latter:

=12 * (Year(UTC(now())) - Year(Date#(DateOfCreation, 'ddMMyyyy')) + Month(UTC(now())) - Month(Date#(DateOfCreation, 'ddMMyyyy'))

If you convert the field to a proper date value on initial load, you wont need all the date mangling in the previous expression.

You could also say:

=12 * (Year(UTC(now())) - Right(DateOfCreation, 4)) + Month(UTC(now())) - Mid(DateOfCreation, 4, 2)

>>May i have an example of <expression>.

<expression> is just a placeholder for whatever you are trying to count. Probably a field name.

[Anonymous]

Hi Jonathan,

Thanks for your suggestion. I understand the convenience of converting the field to a proper date value during the initial load. Given my environment i am unable to do so, this is because i am not using the data manger to add it but rather the data load editor to load the table from a script.

That aside, i have formulated the code you provided which allows me to count the months.

12 * (year(now()) - year(date#(DateOfCreation,'DDMMYYYY'))) + month(now()) -month(date#(DateOfCreation,'DDMMYYYY'))

This poses the same problem as mentioned earlier. Comparing dates between 01032017 and 28022017 gives me the difference as 1 month, when in fact it should be 0 as only one day has passed. What i would like is to be able to compare the entire date field.

For example,

28032017 - 28022017 = 1 month

05042017 - 28022017 = 1 month

28042017 - 29022017 = 2 months

31012017 - 28022017 = 1 month (because its the last day of Feb)

I'm not sure if you catch my drift, but if there is no function or expression to calculate this, i guess i'll just go with the assumption the every month has 30 days.

Regards,

Gaston

[Anonymous]

I have gotten the answer for your second part too.

Thanks.

[Siraj]

Hi 

"12 * (Year(date1) - Year(date2)) + Month(date1) - Month(date2)"  this  calculation works fine to get month difference