June 10, 2015 at 6:20 am
I have a requirement to add a Lead Time calculated column to a shipping fact table. The calculation is supposed to be the number of business days between the shipping date and the order date. Business days are defined as Monday to Friday.
The complication (for me at least) comes from the fact that the shipping fact table does not have shipping date and order date in it. The fact table instead has surrogate keys that point to two dimension tables for shipping dates and order dates. I came up with the following code that would work if I didn't have to worry about business days:
factShipments[LEAD_TIME]:=1*RELATED(dimShipDate[SHIP_DATE] - RELATED(dimOrdDate[ORD_DATE])
My date dimension tables both include a column [IS_WORKDAY] that has a 1 for weekdays and 0 for weekends. I was thinking of maybe doing a count on that column somehow but can't get it to work properly. Anyone able to help?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply