Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhargav_bhat
Creator II
Creator II

Calculating Working Days including Saturday

Hi All,

We have a requirement  where we need to calculate working days between  creation date and closed date. Working Days  does not include Sundays and Holidays

I found many posts in the community but did'nt  find  an exact solution

Needed some guidance on how to calculate it

 

Regards,

Bhargav

 

 

Labels (2)
1 Solution

Accepted Solutions
bhargav_bhat
Creator II
Creator II
Author

Hi Steve,

I got a solution in one of the community posts today.Below are the steps to implement it

       Part 1:

  1. Create a table containing a list of dates from min(Start Date) to max(End Date) from the data which is available.
  2. Create an incrementing no starting from 1  from min(Start Date) to max(End Date). if there is a holiday or sunday then increment by 0
  3. The below contains the script to implement it
SET vFirstDate = date#('2009-01-01','YYYY-MM-DD');
SET vLastDate = date#('2010-06-01','YYYY-MM-DD');

mHolidays:
mapping LOAD date#(Date,'YYYY-MM-DD') as Date,1 INLINE [
Date
2009-01-15
2009-03-18
2009-05-12
2010-01-18
];

Dates:
Load
date($(vFirstDate)+rowno()-1) as Date
autogenerate($(vLastDate)-$(vFirstDate)+1);

Dates2:
Load
Date,
rangesum(peek(DateNo),
if(match(num(weekday(Date)),0) // Sunday non-working
or applymap('mHolidays',Date,0)=1, // Lookup holiday
0, // Add zero as it is holiday
1) // Add one as it is working day
) as DateNo
resident Dates;
drop table Dates;

 

DateDateNo
2010-01-14268
2010-01-15269
2010-01-16269
2010-01-17269
2010-01-18269
2010-01-19270

 

Part 2:

  1. Now using mapping load from the above table we will get a number for Start date and End Date
  2. Finding the difference of these two numbers will give us the no of working days

 

Regards,

Bhargav

View solution in original post

8 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

I use exactly that requirement as the example in my blog post on passing variables into a parameter. You can find the blog post here:

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

Hope that helps point you in the right direction.

Steve

bhargav_bhat
Creator II
Creator II
Author

Hi Steve,

Thanks for Replying ,

I went through the blog which you have shared and it calculates no of working days  excluding public holidays.But it does not contain any logic or expression to include Saturday .Is there any way we can achieve this in QliKSense

 

Regards,

Bhargav

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

No easy way, as far as I am aware. Are you needing to do this in the front end or in the load script?

During the load script you could create a routine that enumerates around each day.

In an inline function you could work out the WeekDay of the start point and then work out how many Saturdays appear in the difference between the start and end date, then add that to the result of the NetworkDays function.

Something like:

Floor((EndDate - StartDate - (WeekDay(StartDate)-?)/7)

Not sure what the ? would need to be in that expression, but hopefully you can see where it is coming from?

bhargav_bhat
Creator II
Creator II
Author

Hi Steve,

But with that approach there can be an issue. If there is a holiday between start date and end date which comes on Saturday,it will get add up to the working days.

Regards,

Bhargav

 

 

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

In the UK there are no national holidays that fall on a Saturday. Is this something that happens where you need to calculate this for? Not sure how you could deal with that off the top of my head. If it is just one or two a year you could put an if statement in for it. Not pretty, but you should be able to get to the result you want.

robert99
Specialist III
Specialist III

Hi

Why not just load the Dates as required from an Excel sheet.

Ive never had to do this so maybe I'm posting rubbish but either join to the calendar date or use mapping load and if etc to include in the calendar table

Then use set analysis to exclude holidays.

 

 

 

bhargav_bhat
Creator II
Creator II
Author

Hi Steve,

I got a solution in one of the community posts today.Below are the steps to implement it

       Part 1:

  1. Create a table containing a list of dates from min(Start Date) to max(End Date) from the data which is available.
  2. Create an incrementing no starting from 1  from min(Start Date) to max(End Date). if there is a holiday or sunday then increment by 0
  3. The below contains the script to implement it
SET vFirstDate = date#('2009-01-01','YYYY-MM-DD');
SET vLastDate = date#('2010-06-01','YYYY-MM-DD');

mHolidays:
mapping LOAD date#(Date,'YYYY-MM-DD') as Date,1 INLINE [
Date
2009-01-15
2009-03-18
2009-05-12
2010-01-18
];

Dates:
Load
date($(vFirstDate)+rowno()-1) as Date
autogenerate($(vLastDate)-$(vFirstDate)+1);

Dates2:
Load
Date,
rangesum(peek(DateNo),
if(match(num(weekday(Date)),0) // Sunday non-working
or applymap('mHolidays',Date,0)=1, // Lookup holiday
0, // Add zero as it is holiday
1) // Add one as it is working day
) as DateNo
resident Dates;
drop table Dates;

 

DateDateNo
2010-01-14268
2010-01-15269
2010-01-16269
2010-01-17269
2010-01-18269
2010-01-19270

 

Part 2:

  1. Now using mapping load from the above table we will get a number for Start date and End Date
  2. Finding the difference of these two numbers will give us the no of working days

 

Regards,

Bhargav

bhargav_bhat
Creator II
Creator II
Author

Hi Robert,
Thanks for replying,
That may not work as we need to calculate the working days and not just exclude the holidays in the expression.

Regards,
Bhargav