topic Re: date difference in QlikView

date difference

qvqfqlik — Fri, 02 Nov 2018 04:59:43 GMT

Hi,

need help with calculating difference between two dates timestamps which should exclude weekends.

eg: date1= 12-nov-2018 8:30 am

date2 = 19-nov-2018 9:30 am

need the difference(date2-date1) result = 5 days 1hr

Re: date difference

vitaliichupryna — Fri, 02 Nov 2018 05:55:52 GMT

Hi Prithvi,

Use interval function:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FormattingFunctions/Interval.htm

Thanks,

Vitalii

Re: date difference

rubenmarin — Fri, 02 Nov 2018 08:55:10 GMT

Hi Prithvi, you can use Networkdays to count working days:

https://help.qlik.com/es-ES/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/networkdays.h…

In example:

=If(date1_time>date2_time,

(NetWorkDays(date1_date,date2_date)-1) & 'days ' & time(date2_time-date1_time, 'h') & 'hr',

(NetWorkDays(date1_date,date2_date)-2) & 'days ' & time(date2_time-date1_time, 'h') & 'hr')

Re: date difference

Anil_Babu_Samineni — Fri, 02 Nov 2018 09:50:14 GMT

Ruben, Eager to know. Her requirement is little different. I am trying to help but not yet solved. May be you have other work around. I can think Master Calendar with Timestamp for every hour.

Coming to requirement.

Input:

Load * Inline [

date1, date2

12-nov-2018 8:30 am, 19-nov-2018 9:30 am

];

She need result as below

date1, date2, Expected

12-nov-2018 8:30 am, 19-nov-2018 9:30 am, 5 Days 1 Hour

Explanation about requirement

date1 start @8:30 am that means she need to calculate as

12-nov-2018 8:30 am to 13-nov-2018 8:30 am as 1 Day

13-nov-2018 8:30 am to 14-nov-2018 8:30 am as 2 Day

14-nov-2018 8:30 am to 15-nov-2018 8:30 am as 3 Day

15-nov-2018 8:30 am to 16-nov-2018 8:30 am as 4 Day

16-nov-2018 8:30 am to 17-nov-2018 8:30 am as 5 Day

17-nov-2018 8:30 am to 18-nov-2018 8:30 am as 6 Day

18-nov-2018 8:30 am to 19-nov-2018 8:30 am as 7 Day

Till here We got it as 7 Days - And in this arbitrary she want to exclude 2 Days as in this week we have Saturday and Sunday.

That means we will get 5 Days. Now How 1 Hour is needed?

From 19-nov-2018 8:30 am to 19-nov-2018 9:30 am --> If we calculate, It is showing 1 Hour.

So, Finally Result field is 5 Days 1 Hour

I hope I make clear

Re: date difference

rubenmarin — Fri, 02 Nov 2018 09:59:16 GMT

Hi Anil, sorry but I don't understand the issue, I tried to set fixed values to make a test with '12-nov-2018 8:30 am' as date1 and '19-nov-2018 9:30 am' as date2:

=If(time('9:30')>Time('8:30'),

(NetWorkDays('12/11/2018','19/11/2018')-1) & 'days ' & time('9:30'-'8:30', 'h') & 'hr',

(NetWorkDays('12/11/2018','19/11/2018')-2) & 'days ' & time('8:30'-'9:30', 'h') & 'hr')

This returns '5days 1hr', is this wrong?

Re: date difference

Anil_Babu_Samineni — Fri, 02 Nov 2018 10:07:18 GMT

That means, you are splitting Date and Time different - If i understand correct. But when we are doing with full timestamp it is making problem. I hope this resolve for priqvqlik

Re: date difference

rubenmarin — Fri, 02 Nov 2018 10:26:56 GMT

Yes, I separated them, that causes to have a check to know with time part is higher to get the correct days, but allows to use networkdays() or a flag in dates of master calendar to count working days and also helps keeping the time part as a simple substraction.

Maybe other way can be using (Date1-Date2)-Count(WeekendDatesInInterval)... still you will have to deal to give in 'x days h hours' format, wich will be easier having separated days and hours.

Re: date difference

qvqfqlik — Mon, 05 Nov 2018 08:05:36 GMT

Thanks Ruben!

this expression works but is not giving results accurately for some of the date values.

eg:

date1 & time1= 20-Aug-2016, 12:32 am

date2 & time2 = 20-Aug-2016, 1:42 am

difference of these would be 1hr 10min, this can can be rounded to 2hr

but the expression is returning as -1day 1hr.

can you please help with this , also rounding of accordingly.

Re: date difference

qvqfqlik — Mon, 05 Nov 2018 08:15:32 GMT

Thanks Ruben!

can you please tell how to calculate total no.of seconds (excluding weekends).

And probably we can convert to no.of days and hours from it.

---------------

Your previous expression works but is not giving results accurately for some of the date values.

eg:

date1 & time1= 20-Aug-2016, 12:32 am

date2 & time2 = 20-Aug-2016, 1:42 am

difference of these would be 1hr 10min, this can can be rounded to 2hr

but the expression is returning as -1day 1hr.

can you please help with this , also rounding of accordingly.

Re: date difference

rubenmarin — Mon, 05 Nov 2018 10:02:28 GMT

Hi Prithvi, that one fails because 20/08/2016 is not a working day, so I think the hours shouldn't be counted either, this can be solved with a condition to check if there is at least 1 working day, and rounding hours up can be donde with ceil, in example:

=If(NetWorkDays('23/08/2016','23/08/2016')>0,

If(time('13:42')>Time('12:32'),

(NetWorkDays('23/08/2016','23/08/2016')-1) & 'days ' & time(Ceil('13:42'-'12:32', 1/24), 'h') & 'hr',

(NetWorkDays('23/08/2016','23/08/2016')-2) & 'days ' & time(Ceil('12:32'-'13:42', 1/24), 'h') & 'hr'), '0days 0hr')