topic Re: Interval in DD HH:MM just excluding 'Sunday' in QlikView

Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 13:11:17 GMT

Greetings all,

I have a small issue, I want to calculate the interval between 2 dates but excluding the Sunday, not all the weekend. So far I had been using the 'NetWorkDay()' function but this one is excluding Sunday and Saturday too. Saturday I want it in the calculation.

So the result that I want should be in Days Hours:Minutes (interval)

Anyone has any idea how I can do this ?

Thank you!

Re: Interval in DD HH:MM just excluding 'Sunday'

swuehl — Tue, 27 Oct 2015 13:17:14 GMT

You can look into one of the approaches shown here:

Calculate hours between two Date/Time strings

They may be a bit more complex than requested, but it should be quite easy to simplify and adapt.

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 13:22:57 GMT

Yes, I know that post, but I did not find anything that can help on my situation.

Re: Interval in DD HH:MM just excluding 'Sunday'

swuehl — Tue, 27 Oct 2015 13:29:33 GMT

Do you actually have start and end dates or timestamps?

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 13:30:33 GMT

Time stamps. Sorry that I did not mention it.

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 13:41:11 GMT

But I have start time stamp and end time stamp.

Re: Interval in DD HH:MM just excluding 'Sunday'

swuehl — Tue, 27 Oct 2015 13:43:04 GMT

Here is adapted sample code from the referenced thread:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, frac(DT1), 0) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), frac(DT2),1) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP

where WeekDay(Date)<6 and not match(Date,$(vHol)) // Exclude Sundays and Holidays defined above

group by ID;

drop table TMP;

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 13:47:16 GMT

Thanks! But there is any way to do this into a chart ? Not in the script.But somehow to exclude the sundays ? Like this example ?

=Interval(

(RangeMin(frac(Closedate), MakeTime(18))

- RangeMax(frac(Logdate), MakeTime(8)))

+ (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

Re: Interval in DD HH:MM just excluding 'Sunday'

swuehl — Tue, 27 Oct 2015 14:21:30 GMT

Maybe like

Interval(

rangesum(

DT2-DT1,

-If(Weekday(DT1)=6,1-frac(DT1)),

-If(WeekDay(DT2)=6,frac(DT2)),

-Div(floor(DT2)-floor(DT1),7), -If(WeekDay(DT2)<Weekday(DT1),1), if(WeekDay(DT1)=6,1)

)

edit:

added a component to correct Sunday counting

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 14:45:48 GMT

Is not working properly.

This is my script where I calculate only the work days.

Interval(

(RangeMin(frac(Real_Resolution_Provided_to_Client__c))

- RangeMax(frac(Open_Date__c)))

+ (NetWorkDays(Open_Date__c, Real_Resolution_Provided_to_Client__c))

,'D hh:mm')

Do you have any idea in order to change this one to take the Saturdays ?

Re: Interval in DD HH:MM just excluding 'Sunday'

swuehl — Tue, 27 Oct 2015 14:48:13 GMT

What do you mean with not working properly?

Please note that I just made a change before your post to the expression.

If this is not working for you, please provide the timestamps that result in incorrect interval. And the expected interval.

Re: Interval in DD HH:MM just excluding 'Sunday'

Tue, 27 Oct 2015 15:02:07 GMT

I did some tests, and everything is ok. Thank you very much ! Very very helpful.