Exclude national/public holidays

Hi all,
 I have a very unique request from my client, they want to exclude public holidays when they run the report.
So here is the requirement.
Report runs fetching data from a View, View does not calculate or exclude weekends and poblic holidays, so when we run the report today it should show data for Monday if Tuesday was a holiday.
This logic/function is not handled on View and they want it to be applied on the report side.
any help will be appreciated.
thanks

Find more posts tagged with

Comments

micajblock

Where are public holidays stored? Easiest is to create a table with the public holidays and join to the view.

hatra

Hi thanks for the responed, so far it is not stored anywhere and they expecte to write a big chank of script on the report, I will advice them to create a table.
 
Thanks

micajblock

It can be done in script also. Question still remains - where to you get the list of public holidays?

Matthew L.

hatraatrin,
 
You indicated in a previous post that you are wanting to accomplish this with script inside the report.
You can use the code from this example to get an array of dates that correspond to holidays, then using the array you can filter the data set results:
<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/1127-business-day-and-holiday-calculator/'>http://developer.actuate.com/community/forum/index.php?/files/file/1127-business-day-and-holiday-calculator/</a>
 
Also, depending on which holidays are needed for your report, the holiday algorithms might need to be added/changed/removed/etc.

hatra

Mblock, corrently there isnt anywhere we can get the holiday , it probably need to be hard coded. they want the eitire calculation be done in the report.

hatra

Thanks Matthew,
I had a look at the link you sent, I need to get it wirking with British public holiday.
 
If anyone has a copy which they may have used it already in their report will be great.
thanks

micajblock

Well that is not smart. Then every year you will need to change the report. In any case the best way to do this is to hard code a where clause in your query something like this:
<pre class="_prettyXprint">
datecolumn not in (comma delimited list of public holiday dates)</pre>

Matthew L.

hatraatrin,
 
As I am unfamiliar with British public holidays, could you point me to a resource that lists the holidays you are needing to determine for the calculation?

hatra

Thanks,
<a data-ipb='nomediaparse' href='https://www.gov.uk/bank-holidays'>https://www.gov.uk/bank-holidays</a>

Matthew L.

Attached is a modified design from the following resource: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/1127-business-day-and-holiday-calculator/'>http://developer.actuate.com/community/forum/index.php?/files/file/1127-business-day-and-holiday-calculator/</a>
 
I've added United Kingdom (ENG, NIR, WAL) holiday logic as well as the Common Local Holidays to this design.
Please test thoroughly for any issues in the calculation logic.
Also note that I noticed that Spring Bank Holiday has a rare day of June 4th for special occasions and I've added both 2002 and 2012 years to the logic (I did not go further back for this logic).
Since this day cannot be calculated (that I know of) it might cause an error in the calculation when these special occasions occur in the future.
 
Below is the calculation logic functions added to the original design, feel free to update this topic thread with any corrections as needed:
<pre class="_prettyXprint _lang-js">

//Easter Monday
function EasterMonday(year){
var d = EasterSunday(year);
d.setTime( d.getTime() + (1000*60*60*24) );
return d
}

function NewYearsDayObservedUK(year){
var dayOfTheWeek = NewYearsDay(year).getDay(); //Get holiday
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 0, 2); //If holiday is on Sunday: Return Jan 2nd
case 1: case 2: case 3: case 4: case 5: //If holiday is on Monday,Tuesday,Wednesday,Thursday,Friday:
return new Date(year, 0, 1); //Return Jan 1st
default: return new Date(year, 0, 3); //If holiday is on Saturday: Return Jan 3rd
}
}

//First Monday of May
function EarlyMayBankHolidayUK(year){
var dayOfTheWeek = new Date(year, 4, 1).getDay(); //Get May 1st
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 4, 2); //If May 1st is on Sunday: Return May 2nd
case 1: return new Date(year, 4, 1); //If May 1st is on Monday: Return May 1st
case 2: return new Date(year, 4, 7); //If May 1st is on Tuesday: Return May 7th
case 3: return new Date(year, 4, 6); //If May 1st is on Wednesday: Return May 6th
case 4: return new Date(year, 4, 5); //If May 1st is on Thursday: Return May 5th
case 5: return new Date(year, 4, 4); //If May 1st is on Friday: Return May 4th
default: return new Date(year, 4, 3); //If May 1st is on Saturday: Return May 3rd
}
}

//Last Monday of May
function SpringBankHolidayUK(year){
if([2002,2012].indexOf(year)>-1){return new Date(year, 5, 4);} //Years holiday is moved to Jun 4th
var dayOfTheWeek = new Date(year, 4, 31).getDay(); //Get May 31st
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 4, 25); //If May 31st is on Sunday: Return May 25th
case 1: return new Date(year, 4, 31); //If May 31st is on Monday: Return May 31st
case 2: return new Date(year, 4, 30); //If May 31st is on Tuesday: Return May 30th
case 3: return new Date(year, 4, 29); //If May 31st is on Wednesday: Return May 29th
case 4: return new Date(year, 4, 28); //If May 31st is on Thursday: Return May 28th
case 5: return new Date(year, 4, 27); //If May 31st is on Friday: Return May 27th
default: return new Date(year, 4, 26); //If May 31st is on Saturday: Return May 26th
}
}

//Last Monday of August
function SummerBankHolidayUK_ENG_NIR_WAL(year){
var dayOfTheWeek = new Date(year, 7, 31).getDay(); //Get August 31st
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 7, 25); //If August 31st is on Sunday: Return August 25th
case 1: return new Date(year, 7, 31); //If August 31st is on Monday: Return August 31st
case 2: return new Date(year, 7, 30); //If August 31st is on Tuesday: Return August 30th
case 3: return new Date(year, 7, 29); //If August 31st is on Wednesday: Return August 29th
case 4: return new Date(year, 7, 28); //If August 31st is on Thursday: Return August 28th
case 5: return new Date(year, 7, 27); //If August 31st is on Friday: Return August 27th
default: return new Date(year, 7, 26); //If August 31st is on Saturday: Return August 26th
}
}

//First Monday of August
function SummerBankHolidayUK_Scotland(year){
var dayOfTheWeek = new Date(year, 7, 1).getDay(); //Get August 1st
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 7, 2); //If August 1st is on Sunday: Return August 2nd
case 1: return new Date(year, 7, 1); //If August 1st is on Monday: Return August 1st
case 2: return new Date(year, 7, 7); //If August 1st is on Tuesday: Return August 7th
case 3: return new Date(year, 7, 6); //If August 1st is on Wednesday: Return August 6th
case 4: return new Date(year, 7, 5); //If August 1st is on Thursday: Return August 5th
case 5: return new Date(year, 7, 4); //If August 1st is on Friday: Return August 4th
default: return new Date(year, 7, 3); //If August 1st is on Saturday: Return August 3rd
}
}

function BoxingDay(year){
return new Date(year, 11, 26); // December 25th
}
function BoxingDayObserved(year){
var dayOfTheWeek = BoxingDay(year).getDay(); //Get holiday
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 11, 28); //If holiday is on Sunday: Return Dec 28th
case 1: case 2: case 3: case 4: case 5: //If holiday is on Monday,Tuesday,Wednesday,Thursday,Friday
return new Date(year, 11, 26); //Return Dec 26th
default: return new Date(year, 11, 28); //If holiday is on Saturday: Return Dec 28th
}
}

function ChristmasDayObservedUK(year){
var dayOfTheWeek = ChristmasDay(year).getDay(); //Get holiday
switch(dayOfTheWeek){ //Check day
case 0: return new Date(year, 11, 27); //If holiday is on Sunday: Return Dec 27th
case 1: case 2: case 3: case 4: case 5: //If holiday is on Monday,Tuesday,Wednesday,Thursday,Friday
return new Date(year, 11, 25); //Return Dec 25th
default: return new Date(year, 11, 27); //If holiday is on Saturday: Return Dec 27th
}
}
</pre>

hatra

Thanks mblock

hatra

thanks Matthew,
I will give it a try.