how to create Business Days(continues number)?

[system]

hi,
i have value(sales amount) for business days only ie., working days
Instead of date i want to display business days(1,2,3,4…)
Ex:
Date Amt
01/10/2005 20
04/10/2005 10
05/10/2005 5
07/10/2005 15

Instead of Above i Want to be

Business days Amt
1 20
2 10
3 5
4 15

Please help me it’s very urgent.

Regards.,
karthik

---

karthishan (BOB member since 2005-12-28)

[system]

Karthishan,

I don’t know if this will help you or not. I needed to do something similar and created this variable:

=If(IsNull([Submitted Date]);(Floor(DaysBetween([Date Approved];[Production Date])+1)-(Truncate(((DayNumberOfWeek([Submitted Date])+(DaysBetween([Submitted Date];[Production Date])))/7);0)*2));(Floor(DaysBetween([Submitted Date];[Production Date])+1)-(Truncate(((DayNumberOfWeek([Submitted Date])+(DaysBetween([Submitted Date];[Production Date])))/7);0)*2)))

It’s a combination of three other variables I found somewhere on these boards (can’t seem to find the original anymore) but it basically counts the number of weekends in the date range and subtracts those days back out of the total. One of my developers has since taken this formula and rewritten it into a dimension of the universe to hide the complexity of the formula.

Obviously [Submitted Date], [Date Approved], and [Production Date] are the Dimensions from the query.

This post may also help you out.

120×120 10.4 KB

Hi - Im trying to get the difference between two dates but I want to know the number of BUSINESS days and not just the absolute number of days. I have used the following formula so far: =DaysBetween( ,) is there any trick I can use to get BO to tell me the number of Work days elapsed? Thanks Mandy Maximo625 (BOB member since 2006-04-26)

Or try searching for NETWORKDAYS (the Excel function that accomplishes this) or Business Days on the forum and you’ll find some other ideas.

---

jtleach (BOB member since 2006-05-19)

[system]

@jtleach

=If(IsNull([Submitted Date]);(Floor(DaysBetween([Date Approved];[Production Date])+1)-(Truncate(((DayNumberOfWeek([Submitted Date])+(DaysBetween([Submitted Date];[Production Date])))/7);0)*2));(Floor(DaysBetween([Submitted Date];[Production Date])+1)-(Truncate(((DayNumberOfWeek([Submitted Date])+(DaysBetween([Submitted Date];[Production Date])))/7);0)*2)))

It’s actually important to note on this formula (as I should have previously) that we had to use an If statement because we didn’t have a submitted date for every record from a data migration. Therefore, we had to use the If(IsNull… portion to determine which date to calculate from.

Just wanted to clear up any confusion that may come from that.

---

jtleach (BOB member since 2006-05-19)

[system]

You could create a lookup table that has a date column, a month/year column and a businessday column, then map that into the universe…

---

jac (BOB member since 2005-05-10)

[system]

You haven’t mentioned what database server you use so I can just point out an option but no code.

Presumption:
It will work if your business weeks are in line with the calendar weeks.

Coding:
Generate the week of the year (check whether you need to do this in ISO8601 or whether database function is ok for users), multiply by 5 (assuming 5 business days per week).
Add to this number the day of the week - depending on which time locale you are you have to either leave it as is (Monday as starting day) or subtract 1 from it (if Sunday is the starting day of the week).

If you just want to display business days from the lowest starting date then deduct the minimum week of the date from your week calculation.

This function doesn’t take bank holidays into account. If you require this functionality then a calendar lookup table is what you have to build.

---

KMB (BOB member since 2004-02-11)