topic Re: diff in QlikView

diff

master_student — Mon, 30 Oct 2017 11:06:52 GMT

Hello Guys,

I am trying to calculate the difference between two dates that have this format :10/25/2017 15:41:06 without counting holidays and sundays / saturdays

but the result seems wrong

bellow is the output :

here is my script :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

//****************************** Connection to CRMQVIEW *************************************//

LET vStartHour = 9;

LET vEndHour = 18;

LET vLunchStart =13;

LET vLunchEnd =14;

LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));

Holidays:

LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [

Holidays

20/03/2017

01/05/2017

25/07/2017

];

LET vHolidays = Peek('Holidays',0,'Holidays');

test :

LOAD

rangesum(Business_Hrs_Without_Overtime,Overtime) as Business_Hrs_With_Overtime;

LOAD *,

rangesum(round(rangesum(

NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // In between hours

if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)),

Rangemin(rangemax(frac(END_TIME),maketime($(vStartHour))),maketime($(vEndHour)))-

Rangemax(rangemin(frac(END_TIME),maketime($(vStartHour))),maketime($(vStartHour))),0), // working hours last day

if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),

Rangemin(rangemax(frac(START_TIME),maketime($(vEndHour))),maketime($(vEndHour)))-

Rangemax(rangemin(frac(START_TIME),maketime($(vEndHour))),maketime($(vStartHour))),0), // working hours first day

if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If same day then correct the hours

)*24,0.01)) AS Business_Hrs_Without_Overtime,

rangesum(if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) ,

round(rangesum(if(frac(START_TIME)<maketime($(vStartHour)),maketime($(vStartHour))-frac(START_TIME),0),

if(frac(END_TIME)>maketime($(vEndHour)),frac(END_TIME)-maketime($(vEndHour)),0))*24,0.01))) as Overtime ; // Overtime

LOAD *,

ROW_ADDED_DTTM as START_TIME,

ROW_LASTMANT_DTTM as END_TIME

;

select ...

any help pleasee

Re: diff

stevedark — Mon, 30 Oct 2017 20:45:12 GMT

Hi Wiem,

In what way does the result seem wrong?

I would look at unit testing each part of the process. Does the variable for vWorkingHoursPerDay seem right, for instance. Does MakeTime give the right result (it should be 1/24*vWorkingHoursPerDay).

NetWorkDays will always give you a result in whole days, so it is probably that you need to get the whole days as a value, multiple that by the hours per day, then add on number of hours until close of play on the start and then add on number of hours since start of play on the end date. This then gets complicated if start or end time falls outside of working days.

My take on how to do this is included at the end of this blog post:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

This avoids the complexities of times though - so only gets you part of the way there.

Hope it's a pointer in the right direction though.

Steve

Re: diff

master_student — Wed, 01 Nov 2017 14:57:06 GMT

Hi Steve, Thanks for your reply. The result is 2.96, I didn't get what it means 2 hours 96 minutes ?

kush141087

any help please ?

Thanks both of you

Re: diff

stevedark — Wed, 01 Nov 2017 15:06:58 GMT

That will most likely be a decimal part of a day, i.e. 57 minutes short of three days.

To format ages, I have a variable called vFormatAge with the following content:

dual(num(floor($1), '#,##0') & 'd ' & time($1 - floor($1), 'hh:mm:ss'), $1)

You can then call that like this:

=$(vFormatAge(FileAge))

Re: diff

master_student — Wed, 01 Nov 2017 15:49:33 GMT

hello Steve,

I didn't get where to put your formula, here is my output :

Re: diff

stevedark — Wed, 01 Nov 2017 18:08:32 GMT

Sorry, I misread and thought your decimal number was number of days, not hours. The number will be hours and fractions of an hour.

The expression in the variable then only needs to convert to hours before displaying as a time:

time($1 / 24, 'hh:mm:ss')

The result should then be given in hh:mm:ss format.

Hope that works out for you.

Re: diff

master_student — Wed, 01 Nov 2017 19:45:18 GMT

Thanks a lot stevedark‌