Sigma #WOW 2025 Week 29

|

Sigma #WOW 2025 Week 29 |

Date Scaffold Gantt Chart

(Unofficial) Solution Overview

Platform: Sigma Computing

Link to original challenge: https://workout-wednesday.com/2025-week-29-sigma-gantt-touch-this/

Key Takeaways:

  • Date Scaffolding is a data modeling method to create calendar-style visualizations on tables with non-consecutive dates.

  • Compared to visuals created with relative dates, a Date Scaffold allows users to interact with actual date objects.

  • Date Scaffolds expand the possibility of actions, drill-downs, and offer a holistic view of time-series data.

Why Date Scaffold?

  • Users are used to looking at calendar views that are continuous, but most tables are not built that way.

    • For example, in the original table from the Sigma WOW challenge, the flattened project management table only records the start date and end date of a particular project. The dates before the start, after the end, and during the task are not recorded.

    • Even in a transactional data set, dates without any active activities would not exist in the table.

    • If a date is not recorded in the source table, it is not presented in the visualization.

  • Date Scaffold can fill in the “missing” date records by transforming the shape of the data table.

    • This allows us to create calendar-style visualizations that users are used to. Even if there isn’t any activity taking place on a specific date, the date record is presented in the visualization.

How to Create a Date Scaffold?

  • The basic structure of the scaffold is cross joining every date throughout the entire time frame to every existing record.

    • There are multiple ways to create the scaffold, depending on what tech stack you have access to. If you only have Excel, it is doable in Excel. (We will demo it in Sigma)

  • We want to create a cross join between the flat table to a date helper table that simply records every single date throughout the time frame as a single row.

  • We will add a column to the joined table to indicate what is happening at every single date for each row in the original flat table.

  • (Please refer to the demo video to see how this is done step by step in Sigma)

Finally, visualization - Gantt Chart

  • We need to hack the system a little bit, since there is no default “Gantt Chart” type.

    • Line Chart and Scatter Plot are both capable of creating visually appealing Gantt Charts, but Line Chart offers the possibility of creating on-click actions for drill-downs.

  • Setting up the Line Chart Visual:

    • X axis: “All Date” field

    • Y axis: “Project Name” field (We are not using the Task Name field because it wouldn’t allow us to add separate reference bands later)

    • Trellis: “Task Name” field (This is the workaround to allow reference band per Task Name, more details in the demo video)

    • Label: Create a custom calc to return Task Name only if the period = “Project Ends” (This ensures the Task Name is only displayed once per task)

    • Reference band: Set it up to be “per chart”

      • Band start: If([Period_Start_End] = "Start", [Day of Date])

      • Band end: If([Period_Start_End] = "End", [Day of Date])

    • Color: Set the color of the line to match the color of the reference band