Attached are the US Bank Holiday and Date List CSVs that have been used for the example.
Download and Import the relevant CSVs to the ElastiCube and build the cube.
Please note that the provided lists and Holiday Bank includes dates between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020.
Create the business-day flag
For each date within the date list, we will attach ‘1’ for business days and ‘0’ for weekends or holidays. For that, we will use a custom column with the following syntax:
WHEN lookup([US Bank Holidays 2012-2020.csv],[Bank Holiday],Date,Date) = 'TRUE' OR dayofweek([Date])>5 THEN 0
Please perform a Schema Changes build for applying changes for upcoming steps.
Calculate the ‘Business Value’ for each date
In the previous step, we added the business day flag to each date. In this step we will create a custom table that will incrementally sum the business day flags for each date resulting in a ‘Business Value’. This value will represent the number of passed business-days relative to the first day in that range.
For that we will create a custom SQL table with the following syntax:
FROM [Date list 2012-2020.csv] a JOIN [Date list 2012-2020.csv] b
ON a.Date >= b.Date
GROUP BY a.Date
Set the tables we added to “Invisible” and build schema changes again.
Step 2 - Lookup the 'Business Value'
In the Example above, we calculated the average business days it takes to ship for each country. For that, we need the business days difference between the ‘ShippedDate’ and ‘OrderDate’. We will lookup the ‘Business Value’ of each date from the table we created\imported and the difference will be the ‘business days difference’ between those two dates.