5 Replies Latest reply: May 11, 2017 3:53 AM by Gaston Tan RSS

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

    Gaston Tan

      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