add business days

[system]

Hi all,

I have a date field object and my requirement is to add 10 working days to this object. Is there any way of doing this can you please help.

Thanks

---

nivas258 (BOB member since 2008-04-14)

[system]

Try reading through these topics:

120×120 10.4 KB

Hello I have a calculation as a measure to work out the mean time for product orders and product invoices. I am using a derived table where the start and end dates for each month are retained. However, we only work Monday to Friday and I need to exclude weekends and bank holidays. At present the mean time is high and not reflected correctly. I am using BOB version 2003. I am new to sql so any assistance you can provide would be greatly appreciated. Code of measure is as follows: NULLIF ( …

120×120 10.4 KB

Hi, I’m new to Universe Design. I’m using a SQL DW as the datasource. My issue is that I want to calculate the working days between two dates. There is a working day calendar in the datawarehouse that uses 1’s to indicate working days for a particular country. There are different statuses that a book order can be in at any one time. I want to be able to choose two different statuses i.e. packed and shipping and view the number of working days based on the dispatch country. There are 20 pos…

120×120 10.4 KB

How can I calculate business days between two dates ? Developer_Designer (BOB member since 2009-08-09)

120×120 10.4 KB

Hi, Do we have any function that excludes (saturday and sunday) between two dates. In excel we use networkdays() function to derive the result. What function we have to use for above scenario in WEBI? Thanks Sriraman srijackson (BOB member since 2009-07-29)

HTH

NMG

---

mcnelson (BOB member since 2008-10-09)

[system]

Hi,

I’ve read through the posts above but can’t find a solution to my specific issue with regards to adding working days to a specified date. My issue is as follows:

I need a result object which adds a specific number of working days to a date for the purposes of targetting. We have a calendar table in our universe which provided the date (linked to the originating date table) and a flag to indicate if a particular day is a working day or not. All i want to do is take that calendar date and add X number of working days to it. If there was an ID in the calendar field i’d just use that to count up to X where working_day=‘Y’ but no ID is available, the calendar only contains dates and the flag. I thought about converting the date to a number but T-SQL doesn’t seem to allow that. There must be a simple solution to this but my brain refuses to co-operate! Any ideas would be appreciated

Just so you know i don’t have access to change the calendar table, and we’re using Oracle 9 and BOXI 3.1 sp5.

Thanks

Chizo Ejindu

---

Xensor (BOB member since 2013-01-16)

[MarkP]

Xensor,

Have you considered writing and using a function?

Pass in the date, have a cursor loop checking back ten working days then return the 10th working day. Could be fairly slow performing so you might want (assuming ten days back is a common requirement) to push it back to the calendar table as an extra column and just update that column the once.

[system]

Hi Mark,

Thanks for the response - unfortunately i have no access to the Oracle database so i cannot write functions or make updates/changes to the calendar table. Any solution would have to be universe or report based (preferably universe). I have seen a function-based solution on another website which could be adapted to fulfill my needs but as i say, i have no access . The obvious solution is to get the DBA to add this function but as our database is externally administered there would be a cost implication and a lead-in time and chances are my boss would not be willing to pay for it anyway.

Regards,

Chizo Ejindu

---

Xensor (BOB member since 2013-01-16)

[system]

Please find attached document for the requirement that I initially started the topic. I think it works but only thing is we have to add bank holidays to the varibale manually every year.
working days.doc (24.0 KB)

---

nivas258 (BOB member since 2008-04-14)

[system]

Hi nivas258,

Thanks for the document, it does look very promising. I do have however a concern about how it handles the addition of days, it doesn’t look like it will specific add additional working days for each bank holiday it calculates. If i write out an example perhaps it will explain my point better:

We have a start date of say Friday 24th December and a need to add 5 working days to that date. Part one of your calculation would return Friday 31st December which is correct. Then the second part would calculate 2 bank holidays in that period (27th and 28th in this example), which would mean an addition of 2 days to the date calculated in step one. However adding 2 days would push the result of step 3 to Sunday 2nd January which is not correct. In this example i think the actual correct result should be Tuesday 4th January as Monday the 3rd would also be a bank holiday.

Admittedly this is an edge case but i think it does highlight the issue when adding a day due to bank holidays pushes the result into the weekend or another bank holiday.

Regards,

Chizo Ejindu

---

Xensor (BOB member since 2013-01-16)

[system]

Hi Chizo,

Yes you are right, I dint recognize that error. Not sure if there is any other alternative solution for this. Please let me know if you find solution to resolve this issue.

Thanks
Nivas

---

nivas258 (BOB member since 2008-04-14)