We use 10 different holiday variables in Business Objects to account for 2005 holidays. If one of the holidays falls between the date a work item was started and completed, then we subtract a day from cycle time since a holiday shouldn’t count against cycle time. I hope that makes sense.
What we have now though are 10 different variables, for example:
Holiday 1: =If ‘1/17/2005’ between [,] then -1.00, else 0.00
Holiday 2: =If ‘2/21/2005’ between [,] then -1.00, else 0.00
Holiday 3:…
…
…
Holiday 10
So let’s say an item came in the 16th of January and was finished the 18th. Regular cycle time calculation would be 2 days (not counting the day it came in). But subtracting for the holiday on the 17th, cycle time will be 1 day, which is correct and accurate.
My question is, instead of having to create 10 different holiday variables, is there a way to combine the logic into one variable but still have that variable account for EACH holiday that occurs? For example if an item came in 1/1 and was completed 2/22, I want the variable to tell the program to subtract 2.00, not just 1.00 since two holidays occured during that period. See what I’m saying? So if an item took all year to complete, the variable should subtract 10.00.
The only method we’ve come up with so far is to write logic for every possible combination of holidays, which is tons more work than just creating 10 holiday variables.
What do you think?
JPritch (BOB member since 2004-12-29)
Create a variable using the 10 Holiday variables:
Holidays
=(<Holiday 1>+<Holiday 2>+<Holiday 3>+<Holiday 4>+<Holiday 5>+<Holiday 6>+<Holiday 7>+<Holiday 8>+<Holiday 9>+<Holiday 10>)
and use the Holidays in the report logic where ever u need which would give you the required count.
Hope this helps!
sskchak (BOB member since 2004-06-21)
Thanks for the response, but we are already doing something similar.
We have a cycle time variable that looks at days between create date and complete date. We use a “business days cycle time” variable in the actual report that takes cycle time, adds Holidays 1 through 10 like you mentioned, and subtracts weekends to get a true business day cycle time. (<cycle_time>+…+)
We get alot of requests for cycle time reports, so I am trying to figure out how to avoid having to create so many holiday variables in the first place, and instead just have to create one, and then mention it once in the business day cycle time variable. (<cycle_time>++)
JPritch (BOB member since 2004-12-29)
I think I got it!
“Holiday”
= (If ‘1/17/2005’ Between ( ,) Then -1.00 Else 0.00)+(If ‘2/21/2005’ Between ( ,) Then -1.00 Else 0.00)+(If ‘3/25/2005’ Between ( ,) Then -1.00 Else 0.00)+…for the rest of the holidays.
Basically it combines the logic of each of the 10 holiday variables, just putting parenthesiss and plus signs in between all of them. The amount of writing is still the same, but we now have ONE holiday variable that we can point to in other variables and not 10.
Any consequences anybody can think of with the logic above? I still have to perform more thorough testing.
JPritch (BOB member since 2004-12-29)
Can you create a object in the universe to accomplish the same using a case statement, so that you could use the object in the reports?
sskchak (BOB member since 2004-06-21)
How 'bout creating a ‘Calender’ table in the database?
This table will have one record per date, and has a flag for date that are holidays. The Calender table can spawn over several years. ( the simpler version will have only 10 rows for thi year, each a holiday)
Bring this table in the universe, create a join between Calender table and table that has create date and complete date information (Holiday between create date and complete date).
Add the holiday in the query, and count it and use the count in your calculations.
There are several ways to handle this problem, once you have the Calender table ready in the database.
AskMe(Dhirendra) (BOB member since 2005-05-13)