Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason1
Contributor II
Contributor II

Determining if there is a Sat/Sun between two dates

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!

 

 

1 Solution

Accepted Solutions
Vegar
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Vegar
Partner
Partner

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.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
jyothish8807
Master II
Master II

Hi Jason,

Check this, could be helpful:

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

You have to define the holidays separately if needed.

Best Regards,
KC
Gysbert_Wassenaar

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

talk is cheap, supply exceeds demand
Vegar
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Jason1
Contributor II
Contributor II

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