Counting Holidays between Two Dates

Dear BusinessObjectsTips.com visitors, the following article was written by Rogério Goulart who has kindly offered to share his knowledge with all of us. Thank you very much Rogério!

Hi Everyone,
I came up with a solution to count the number of holidays between two dates. In conjunction with http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/ it’s possible to calculate the amount of business days between two dates.

I’ll use the “E-fashion” universe to explain how it works.

Create a query filter using [Store Name], the filter will have a prompt called “Dates”.

For the filter, use [Store Name] less than Prompt(“Dates”).

The prompt value will be something like xxx;01/01/2011;01/05/2011;03/06/2011;15/09/2011;25/12/2011

Create a report variable called “Holidays” = Substr(UserResponse(“Dates”);5;Length(UserResponse(“Dates”)))

So, in [Holidays] you’ll got 01/01/2011;01/05/2011;03/06/2011;15/09/2011;25/12/2011

Next, we will need an index to parse the dates. I’ll chose [Year], [Month], and RunningCount([Month]) (the index)

Here is the table:

Year Month Count
2006 1 1
2006 2 2
2006 3 3
2006 4 4
2006 5 5
2006 6 6
2006 7 7
2006 8 8
2006 9 9
2006 10 10
2006 11 11
2006 12 12

Now, I will parse the dates based on count. The formula will be :

Get the right piece of the date:
SubStr([Dates];([Count]-1)*11+1;10)

Transform the string to date:
ToDate(SubStr([Dates];([Count]-1)*11+1;10);”dd/MM/yyyy")

Verifies if the date is within business days:
DayNumberOfWeek(ToDate(SubStr([Dates];([Count]-1)*11+1;10);”dd/MM/yyyy"))<6)/

Finally putting all together:
If(DayNumberOfWeek(ToDate(SubStr([Dates];([Count]-1)*11+1;10);”dd/MM/yyyy"))<6);T oDate(SubStr([Dates];([Count]-1)*11+1;10))

Next, create a fourth column to get all the dates putting the formula in that.

Finally, in the fifth row create another variable to test your holidays against a pair of dates:

If( ([Date1]<=[Holidays] And [date2]>=[Holidays]); 1; 0)

Sum up the fifth columns and you will have the number of Holidays between the dates.

Date1 = 01/01/2010 and date2= 13/08/2010

Year Month Count Holiday Index

2006 1 1 01/01/2011 1
2006 2 2 01/05/2011 1
2006 3 3 03/06/2011 1
2006 4 4 15/09/2011 0
2006 5 5 25/12/2011 0
2006 6 6
2006 7 7
2006 8 8
2006 9 9
2006 10 10
2006 11 11
2006 12 12
Sum 3

Enjoyed this post? Share it!

 

3 thoughts on “Counting Holidays between Two Dates

  1. Looks interesting.
    Could you please clarify what you mean by “For the filter, use [Store Name] less than Prompt(“Dates”).”

  2. Sorry, it really isn’t clear.
    What I mean is :
    1) Create a query filter for [Store Name];
    2) The operator will be “Less than”;and
    3) The operand a query prompt whose name is “Dates”

  3. Hi, Rogerio
    I am new bo leaner, maybe I understand wrong, I tried as following: In webi, create new with efashion universe, in query panel, I put year, city as result objects and put store name as query filter and configured it as you mentioned above. when i run query, it show no data to retrieve. would you please explain it little detail.

    Thanks

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.