# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor 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 (3)

• ### Scripting

1 Solution

Accepted Solutions
Contributor II

## 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 Dateautogenerate(\$(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 DateNoresident Dates;drop table Dates;`

 Date DateNo 2010-01-14 268 2010-01-15 269 2010-01-16 269 2010-01-17 269 2010-01-18 269 2010-01-19 270

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
MVP

## 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:

Hope that helps point you in the right direction.

Steve

Contributor II

## Re: Calculating Working Days including Saturday

Hi Steve,

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

MVP

## 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?

Contributor II

## 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

MVP

## 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.

Valued Contributor II

## 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.

Contributor II

## 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 Dateautogenerate(\$(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 DateNoresident Dates;drop table Dates;`

 Date DateNo 2010-01-14 268 2010-01-15 269 2010-01-16 269 2010-01-17 269 2010-01-18 269 2010-01-19 270

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

Contributor II

Hi Robert,