Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Partner
Partner

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

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

5 Replies
MVP
MVP

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

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

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

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

MVP
MVP

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

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

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

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

Partner
Partner

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

I have gotten the answer for your second part too.

Thanks.