Solved: Re: Interval. Formatting result in hh:mm - Qlik Community

j_nlucas · ‎2012-10-01

Hi,

I created an expression to calculate the time between two dates. It only counts working days, and only 8 hours per working day.

Initially, the result was required to be displayed in format 'D/hh:mm', but now this has changed and has to be displayed in format 'hh:mm'.

The expression below calculates firstly the number of days between the two dates, and then the time for the first day from the opening of the call till EoD and then time from the SoD till the close of the call.

So for a call opened on the '04/09/2012:10:39:00' and closed on the '27/09/2012: 22:58:31' the result generated by the expression below is '16/14:20' (16 days, 14(working) hours, 20mins.

I need to change this expression so that it will return the format 'hh:mm'. So the result should be: 16days*8h=128h + 6.21h(first day, from 10:39 till 5pm) + 8h(last day, from 9am to 5pm). = 142:21 (hh:mm).

It does not work just by changing the formatting line at the bottom. I think the problem is in the first part of the expression, that it's returning days, and the problem as far as I get it is to change these days to the same format returned by the function time used in the following calculations. Any idea about what need to be changed?

The script:

Interval

(

    rangesum(

NetWorkDays([Latest ICT Update]+1,date(floor($(vNow)))-1,$(vBankHols)),    //NUMBER OF DAYS BETWEEN THE DATES

//NUMBER OF HOURS BETWEEN THE SoD AND THE CLOSING OF THE CALL.

                 if(NetWorkDays($(vNow),$(vNow),$(vBankHols)),

                Rangemin(rangemax(frac($(vNow)),time($(vWeekDayStartTime))),time($(vWeekDayEndTime)))-

                     Rangemax(rangemin(frac($(vNow)),time($(vWeekDayStartTime))),time($(vWeekDayStartTime))),0), // working hours last day



//NUMBER OF HOURS BETWEEN THE EoD AND THE OPENING OF THE CALL.

if(NetWorkDays([Latest ICT Update],[Latest ICT Update],$(vBankHols)),

     Rangemax(RangeMin(frac([Latest ICT Update]),time($(vWeekDayEndTime))),time($(vWeekDayEndTime)))-

     RangeMin(RangeMax(frac([Latest ICT Update]),time($(vWeekDayStartTime))),time($(vWeekDayEndTime))),0), // working hours first day



if(NetWorkDays([Latest ICT Update],[Latest ICT Update],$(vBankHols)) and floor([Latest ICT Update])=floor($(vNow)),-(time($(vWeekDayEndTime)-$(vWeekDayStartTime)))) // correct for first equals last day

)

,'D/hh:mm')

Thank you in advance

Lucas

swuehl · ‎2012-10-01

Lucas,

it seems that you are applying Rob's / mine solution from this post:

http://community.qlik.com/message/252929#252929

Any reason why you removed the multiplication with the working hours fraction on a working day (in the original post:

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)

)?

This is quite essential to get the correct result in working hours.

As a result of the rangesum, you get a number that equals the total amount of time for the hours worked. As you probably know, this internal interval format gives you whole days (24 hours) as integer part, fraction of a day as decimal places.

You can display this number in hours using interval, and hours does still mean working hours here.

But if you want to display this number in working days, you can't use interval function here (i.e. the format code for days), since there is a big difference between a day in QV (equalling 24 h) and a working day (equal to e.g. 8 hours). So you need to take the number itself and e.g. divide by the fraction of a working day compared to a full day to retrieve the number of working days:

=Interval(rangesum(...)) / maketime(8)

Does this makes sense?

Regards,

Stefan

View solution in original post

swuehl · ‎2012-10-01