Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Highlighted
bhargav_bhat
Not applicable

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 (3)
1 Solution

Accepted Solutions
bhargav_bhat
Not applicable

Re: Calculating Working Days including Saturday

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

8 Replies
stevedark
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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
Not applicable

Re: Calculating Working Days including Saturday

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