Introduction
This article demonstrates how to avoid counting weekends and holidays when calculating the difference between two dates.
Example - Average Shipping Time in Business Days
Steps
In order to ‘subtract’ two dates and avoid counting the weekends and holidays , we will \ calculate a “business value” for each date within a certain date range.
Step 1
Option 1 - Using Pre-calculated Business Days CSV file
The business days in the csv file were calculated based on the Bank Holiday for the US. Please verify the dates before using the file.
If your use case requires different business days, please create a CSV file with the relevant holidays and calculations. You can also refer to option 2 (using the ElastiCube Manager).
The CSV File holds a table with 3 columns:
-
A continuous date list for the entire date range
-
Business Day flag - 0 for holidays and weekends, 1 for business days
-
Business Value - a running sum of the ‘Business Days’ flag
Download the 'US Dates with Business Values.csv' file and import the table to your ElastiCube, build it and skip to "STEP 2".
Please note that the provided table includes the Holiday Bank between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020
Option 2 (Advanced) - Calculating Business Days in the ElastiCube Manager
The external resources needed are:
1. Relevant “Bank Holiday” which can be easily downloaded online
2. A continuous date list according to the Bank Holiday range, the list can be easily prepared using Excel.
In the above example I used the US Bank holidays which can be found at https://gist.github.com/shivaas/4758439
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:
CASE
WHEN lookup([US Bank Holidays 2012-2020.csv],[Bank Holiday],Date,Date) = 'TRUE' OR dayofweek([Date])>5 THEN 0
ELSE 1
END
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:
SELECT a.Date, sum(b.BusinessDay) [Business Value]
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.
For that we will use the following syntax:
Lookup([Dates Bsiness Values],[Business Value],ShippedDateFixed,Date) - Lookup([Dates Bsiness Values],[Business Value],OrderDateFixed,Date)
Note - If the dates are not stored in the same table, each ‘Business Value’ should be looked up to each table separately.
Step 3 - Create the widget
For the example above, we will select the ‘Ship Country’ as category and add the custom field we calculated as value in Average mode.
You can find the Example Dashboard and Elasticube attached.