Workdays Between Two Dates in Webi

Workdays Between Two Dates in Webi

You receive an order, you ship your wares. Now you want to track how long that process takes, excluding weekends. Here’s how:
=DaysBetween(RelativeDate([Order Date];Floor(DayNumberOfWeek([Order Date])/6)*(8-DayNumberOfWeek([Order Date])));RelativeDate([Ship Date];Floor(DayNumberOfWeek([Ship Date])/6)*(8-DayNumberOfWeek([Ship Date]))))-(Floor(DaysBetween(RelativeDate(RelativeDate([Order Date];Floor(DayNumberOfWeek([Order Date])/6)*(8-DayNumberOfWeek([Order Date])));(DayNumberOfWeek(RelativeDate([Order Date];Floor(DayNumberOfWeek([Order Date])/6)*(8-DayNumberOfWeek([Order Date]))))+1)*-1);RelativeDate([Ship Date];Floor(DayNumberOfWeek([Ship Date])/6)*(8-DayNumberOfWeek([Ship Date]))))/7)*2)


I’m Using:

• BI Platform 4.0 SP4 Patch 5
• Web Intelligence via Launch Pad

I’m Assuming:

• That you know how to log into Launch Pad and know what dimensions, measures and variables are in Webi.
• You may want the Webi formulas and functions guide handy (help.sap.com)
• You have a Webi report set up that has two date dimensions in it.
• I’m assuming that your two dates are always in the same order. E.g. the order date is always before the shipment date.
• That you truly should be ignoring weekends. So this is not a post for 24x7 businesses.

The logic here is nothing earth-shattering, but I’ve seen the question asked a few times and thought I might put my answer up on the interwebs. After a quick Google I see Dave Rathburn beat me to the punch by a little over three years, but this is a bit different (there’s no array).


Just a Trick
As always, this post is about a trick, so I should mention first how to do it properly: do it in the data!
No amount of math will know which public holidays you need to take into account, or if they should differ based on the location that an order was shipped to. For a thorough fix, you will need to create a table in your data source that lists public holidays relevant to your part of the world.


The Steps (Theory)

1. If your start date is a weekend, push it forwards to the next Monday (if you receive an order on a Sunday, really that’s the same a receiving it on a Monday).
2. If your end date is on a weekend, push it forwards to the next Monday (likewise, we’ll consider shipping on a weekend the same as shipping on the following Monday).
3. Count the days between (this will include weekends).
4. Count the total number of weeks traversed. You’re actually counting weekends.
5. Subtract the number of weekends (times two) from the total number of days.

Each of these steps roughly equates to one Webi variable. So now…


The Steps (Practice)

1. Add a new variable. Call it Start Date. This will be the same as your first date (for me this is a dimension called  ‘Date Ordered’) except that weekends will be translated to the following Monday. You could use an IF statement here, but I’ve gone for the straight math version (I’ll tell you why later). Enter the following formula:
   
   =RelativeDate([Date Ordered];Floor(DayNumberOfWeek([Date Ordered])/6)*(8-DayNumberOfWeek([Date Ordered])))
   
   If you're not familiar with the functions, you can either look them up when creating your variable (the description will show at the bottom of the variable editor window) or refer to the functions and formulas guide.
   The above formula takes the order date, and if the day is a Saturday, it adds 2 days, if the day is a Sunday, it adds 1 day. Or more technically, if the day number is 1 to 5 (Monday to Friday) then that value, divided by 6, rounded down to the nearest integer, is going to be zero, right? If the day of the week is 6 or 7, then dividing by 6 and rounding down gives you 1. So 1 for weekends and 0 for weekdays. The last part takes the difference between 8 and the weekday. For Sundays, this is 1. For Saturdays this is 2. Now multiply the two together. Since multiplying by zero gives zero, weekdays have their date shifted by zero days. Weekend days will have their value shifted by 1 or 2 days accordingly.
   
   Your results should look something like this, with the original date on the left, the adjusted date on the right.
   
   
2. Do the same for the end date. Add the four columns to a block and make sure everything is shifting correctly.
   
   
3. Add a new variable called Days Between, the formula is simple:
   
   =DaysBetween([Start Date];[End Date])  
   
   Check that your results add up. Remember this is including weekends.
   
   
4. Add a variable called Start Date (Monday) that returns the date for the Monday at the start of the week of the start date. Step 5 will tell you why, but for now…
   
   =RelativeDate([Start Date];(DayNumberOfWeek([Start Date])*-1)+1)
   
   Check that the date returned is always the correct Monday
   
   
5. Create a variable called Weekends Between that counts the weekends between the two dates. Essentially you’re counting the weekends traversed. Or the way the calculation will work, how many full weeks from the Monday of the start week. This is important. Any day in the same week will be less than 7 days, right? The next Monday will be exactly 7 days, so one week. By counting from the start of the week, it doesn’t matter if the start date was a Friday and the end date is the following Monday (only 3 days elapsed) because counting from the Monday gives you 7 in this case. You’ll divide the result by 7 and round it down to get a whole number.
   
   =Floor(DaysBetween([Start Date (Monday)];[End Date])/7)
   
   Check that the results you get show the correct number of weekends.
   
   
6. We’re on the home stretch. You know how many days are between the two dates, and how many weekends, so now one last little sum.
   
   =[Days Between] - [Weekends Between]*2
   
   Check that your results are right.
   

And that’s it, you’re done! If this is a formula that you want to use in multiple places, then it’s going to be easier to handle if you combine all the formulas into one big variable. The easy way to do this is to work backwards recursively. Copy the last formula into notepad. Then replace the [Days Between] text with the contents of the Days Between variable. Replace the [Weekends Between] text with the contents of the Weekends Between formula, and so on. This is why I didn't use an if statement in any of the variables.

Bonus Tip
This logic can be used to calculate the number of working days in a month. You'll need a date object that's the last day of the previous month, a second that uses LastDayOfMonth() to get the last day of the month you want to count, then count the weekdays between.

Well, this really was a fun one, thanks for reading!

- See more at: http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html#sthash.L1due44N.dpuf