cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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

Community_Admin_0-1634478483204.png

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).

 

Community_Admin_1-1634478483202.png

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

 

Community_Admin_2-1634478540282.png

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

 

Community_Admin_3-1634478586686.png

Set the tables we added to “Invisible” and build schema changes again.

Community_Admin_4-1634478586558.png

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)

Community_Admin_5-1634478586635.png

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.

Community_Admin_6-1634478586641.png

Download:

Date list 2012-2020.csv

AverageShippingBusinessDays.dash

Ecube

US Dates With Business Values.csv

US Bank Holidays 2012-2020.csv

 

Version history
Last update:
‎10-17-2021 06:51 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request