Skip to main content

Holiday Data Identification and Comparison

Scenario

In many industries, such as retail, sales data can differ greatly between holidays and workdays. When analyzing data, holiday data and workday data need to be distinguished. However, databases generally store only dates and do not specifically store holiday dates or lunar calendar dates. This makes Chinese lunar holidays difficult to identify. In addition, public holidays in China are accompanied by adjusted working weekends, so simply treating Monday to Friday as workdays is not rigorous enough. For example, it is inconvenient for users to compare sales data from Spring Festival across two years in Guandata BI. This article explains how to simply and quickly mark statutory holiday information and use it in period-over-period or year-over-year calculations.

Procedure

Prepare a Manual Holiday Table

  1. Create an Excel document, manually enter the first date of the year, then drag down to fill through the last date of the year.

  2. Based on the annual holiday arrangements announced by the State Council, such as 2021 holidays, manually add holiday and adjusted workday information in the second and third columns. Non-statutory holidays can be added as needed.

  3. Upload the file to BI. At the end of each year, append the next year's holidays to this table. The figure below is for reference only.

image.png

Process the File with ETL

  1. Use the holiday table as the input dataset in ETL.

  2. Add a calculated column and use the function weekday() + 1 to get the day of week for each date.
    The returned result is an integer between 1 and 7. In the WEEKDAY function, 0 = Monday, 1 = Tuesday, ..., and 6 = Sunday.

image.png

  1. Add another calculated column. Based on the original holiday information and the weekday information obtained in the previous step, re-determine whether each date is a holiday or a workday. This case does not separately distinguish holidays and weekends. The formula and preview result are shown below:

image.png

  1. Use a join node to join the business dataset that contains the date field with the processed holiday dataset above. Then process the data as needed, save, and run ETL.

image.png

Create a Card

  1. Place the field Holiday Name into the dimension field well, and place Date (Year) into the dimension field well or comparison field well.

  2. Add the required filter conditions to compare data from the same holiday across two years.

Final Result

  1. When comparing only values, Date (Year) can be placed in either the comparison field well or the dimension field well. The example is shown below:

image.png

  1. When calculating growth value or growth rate, place Date (Year) in the dimension field well, then use Advanced Calculation -> YoY/PoP -> Year-over-Year for the value field. The example is shown below:

image.png