topic Determining if there is a Sat/Sun between two dates in QlikView

Determining if there is a Sat/Sun between two dates

Jason1 — Fri, 15 Feb 2019 06:51:47 GMT

Hi all,

I'm trying to determine whether a Saturday or Sunday can be found between any two datetimes. I have a table with two datetime fields and I have determined the interval between the dates but I would like to ignore Saturdays and Sundays when calculating the interval. Networkdays() isn't accurate enough, I've tried that. If my first datetime is Friday 15th Feb at 1pm and my second datetime is Monday 18th Feb at 1pm, networkdays() returns 2 whole days, when I am looking for an exact interval in days, hours, minutes.

Thanks all!

Re: Determining if there is a Sat/Sun between two dates

Vegar — Fri, 15 Feb 2019 07:15:08 GMT

A solution to your issue is to split up your transactions into days. Your example transaction, Friday 15th Feb at 1pm to Monday 18th Feb at 1pm, would split into rows like this:
Friday 15th; 11h
Saturday 16th; 24h
Sunday 17th; 24h
Monday 18th; 13h

The benefit is that you will have better control, but you will have to multiply your transaction amount any count transactions expressions will need to be adjusted.

Re: Determining if there is a Sat/Sun between two dates

jyothish8807 — Fri, 15 Feb 2019 07:20:20 GMT

Hi Jason,

Check this, could be helpful:

https://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings/td-p/380745

You have to define the holidays separately if needed.

Re: Determining if there is a Sat/Sun between two dates

Gysbert_Wassenaar — Fri, 15 Feb 2019 07:54:37 GMT

You could calculate the number of saturdays and sundays with this:
Floor(EndData)-Floor(StartDate)+1-NetWorkdays(StartDate, EndDate)

Re: Determining if there is a Sat/Sun between two dates

Vegar — Fri, 15 Feb 2019 09:49:47 GMT

I believe that @Gysbert_Wassenaar solution will work as long as your start time nor end time is on a Saturday or Sunday.

Re: Determining if there is a Sat/Sun between two dates

Jason1 — Fri, 15 Feb 2019 11:42:13 GMT

Thanks for the input everyone, I've marked what worked. Nice and simple, I should have thought of that logic in the first place.