Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Steve,
I got a solution in one of the community posts today.Below are the steps to implement it
Part 1:
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;
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:
Regards,
Bhargav
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
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
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?
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
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.
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.
Hi Steve,
I got a solution in one of the community posts today.Below are the steps to implement it
Part 1:
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;
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:
Regards,
Bhargav