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
Looks interesting.
Could you please clarify what you mean by “For the filter, use [Store Name] less than Prompt(“Dates”).”
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”
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