Displaying Missing Hourly Data in Bold BI Dashboards

When working with time series data in Bold BI, you may require to visualize the data points even for all time periods, even if they are not present in the data source. This can be particularly important when dealing with hourly, weekly or monthly data, where missing date/time should be accounted for to maintain the integrity of the visualization. Here’s how to show missing hourly data in Bold BI dashboards.

Sample Data:

In the data provided above, it is evident that not all hours are available. To display the default 0 for missing hours instead, we can achieve this by writing custom SQL in the data source level.

PostgreSQL:

Query

    WITH hours_series AS (
        SELECT generate_series(0, 23) AS hour
    )
    SELECT
        hs.hour AS missing_hour,
        your_table.*
    FROM
        hours_series hs
    LEFT JOIN
        your_table ON EXTRACT(HOUR FROM your_table.date_field) = hs.hour
    ORDER BY
        missing_hour;

Explanation:

• This query generates a series of hours from 0 to 23 using the generate_series function.
• The EXTRACT function is used to extract the hour portion from the date_field column in the your_table table. It retrieves the hour as an integer value
• It joins the hours_series CTE with the your_table table based on the hour extracted from the date_field column in your_table and the hour column from the CTE.
  The LEFT JOIN ensures that all hours from the hours_series CTE are included in the result, even if there is no corresponding match in the your_table table.
• This produces a result set that includes all hours of the day, with corresponding data from your_table where available, ordered by the missing hours.

Click house:

Query:

    WITH hours_series AS (
        SELECT number AS missing_hour FROM numbers(0, 23)
    )
    SELECT
        hs.missing_hour,
        your_table.*
    FROM
        hours_series hs
    LEFT JOIN
        your_table ON toHour(your_table.date_field) = hs.missing_hour
    ORDER BY
        missing_hour

Explanation:

• This SQL query creates a series of numbers from 0 to 23 representing hours.
• It then performs a left join with a table named ‘your_table’, linking each hour with corresponding records in the table based on the hour extracted from a ‘date_field’.
• If there are no matching records for a particular hour, NULL values are filled in.
• The toHour function is used to extract the hour portion from the date_field column in your_table.
• It performs a similar to the EXTRACT function used in the previous query.
• It orders the result set by the missing_hour column, ensuring that the hours are displayed in ascending order.

Redshift:

Query:

    WITH hours_series AS (
        SELECT generate_series(0, 23) AS hour
    )
    SELECT
        hs.hour AS missing_hour,
      employees.*
    FROM
        hours_series hs
    LEFT JOIN
      employees ON CAST(EXTRACT(HOUR FROM employees.hiredate::timestamp)AS INTEGER) = hs.hour

Explanation:

Common Table Expression (CTE) - hours_series:

• This CTE generates a series of numbers from 0 to 23, representing the 24 hours in a day. It assigns an alias hour to each number.

Main Query:

• The main query selects data from two sources: the hours_series CTE and the employees table.

LEFT JOIN:

• It joins the hours_series CTE with the employees table using the hour column from the CTE and extracting the hour portion from the hiredate column in the employees table.
• The LEFT JOIN ensures that all rows from the hours_series CTE are included in the result, even if there is no corresponding match in the employees table.

SELECT clause:

• It selects two columns:
  • missing_hour: This comes from the hours_series CTE and represents the hours from 0 to 23.
  • employees: This selects all columns from the employees table.

CAST and EXTRACT functions:

• The EXTRACT function extracts the hour portion from the hiredate column in the employees table.
• The CAST function converts the extracted hour value to an integer. This is done because the generate_series function in the CTE returns integer values, so the hour values from hiredate need to be cast to integers for proper comparison.

MSSQL:

     WITH hours AS 
        (SELECT 0 AS hour UNION ALL SELECT hour+1 FROM hours WHERE hour < 23)
    SELECT 
        h.hour AS missing_hour, 
        yt.*
    FROM 
        hours h 
    LEFT JOIN 
        your_table yt ON DATEPART(HOUR, yt.date_field) = h.hour

Explanation:

• This SQL query identifies missing hours in a dataset stored in table “your_table” with a timestamp field.
• It generates a sequence of hours from 0 to 23 using a recursive Common Table Expression (CTE) named “hours”.
• Then, it performs a LEFT JOIN between the “hours” CTE and “your_table”, matching each hour to the corresponding records.
• The output includes the missing hours along with any associated data from “your_table”, if present, with NULL values for missing records.

Connecting in Bold BI:

Please adhere to the specified steps in order to allocate the dates necessary for creating the desired chart.

Step 1:

Sort the “missing hours” field within the assigned data source:

Step 2:

Select the Data Source order:

Step 3:

In the properties panel, ensure that the “Show Items with No Data” option is checked:

Final Output:

Additional References

• Round Off Time in Date Field
• How to create custom sorting for time-based columns
• Display the time as a range in the widget