Vega-lite / add dynamic layer based on range defined by joinaggregate columns

Question

I'm building a milestone trend analysis chart with vega-lite. The vega-lite chart pulls a dataset (milestone date and finish date), calculates the range for the scales based on the subranges and plots the time series of the milestones correctly. Now I need to overlay the chart with a diagonal line and/or a triangle area in the lower right part of the chart for visually excluding the non valid part of the chart.

I tried several options:

using either milestone date or finish date series from my dataset for plotting the line / area doesn't cover the entire range of the chart:

line/area not spanning entire range

Hard coding the dates would work, however, when selecting different milestones the line / area wouldn't adapt.

When referencing parameters/signal for encoding no rule / area is displayed.

What I'm trying to achieve is this:

desired outcome

This is the code used for creating above visualization:

{
  "width": 500,
  "height": 500,
  "title": {
    "text": "Milestone Trend Analysis"
  },
  "data": {"name": "dataset"},
  "transform": [
    {
      "joinaggregate": [
        {
          "op": "min",
          "field": "Report Date",
          "as": "Report_Date_min"
        },
        {
          "op": "max",
          "field": "Report Date",
          "as": "Report_Date_max"
        },
        {
          "op": "min",
          "field": "Milestone Finish Date",
          "as": "Milestone_Finish_Date_min"
        },
        {
          "op": "max",
          "field": "Milestone Finish Date",
          "as": "Milestone_Finish_Date_max"
        }
      ]
    },
    {
      "calculate": "datum.Report_Date_min < datum.Milestone_Finish_Date_min ? datum.Report_Date_min : datum.Milestone_Finish_Date_min",
      "as": "Time_range_min"
    },
    {
      "calculate": "datum.Report_Date_max > datum.Milestone_Finish_Date_max ? datum.Report_Date_max : datum.Milestone_Finish_Date_max",
      "as": "Time_range_max"
    }
  ],
  "params": [
    {
      "name": "Time_range_min_p",
      "expr": "data('data_0')[0]['Time_range_min']"
    },
    {
      "name": "Time_range_max_p",
      "expr": "data('data_0')[0]['Time_range_max']"
    }
  ],
  "layer": [
    {
      "name": "milestone_trace",
      "mark": "line",
      "encoding": {
        "x": {
          "field": "Report Date",
          "type": "temporal",
          "axis": {
            "title": "Report Date",
            "grid": true,
            "orient": "top"
          },
          "scale": {
            "domainMin": {
              "expr": "Time_range_min_p"
            },
            "domainMax": {
              "expr": "Time_range_max_p"
            }
          }
        },
        "y": {
          "field": "Milestone Finish Date",
          "type": "temporal",
          "axis": {
            "title": "Milestone Date",
            "grid": true
          },
          "scale": {
            "domainMin": {
              "expr": "Time_range_min_p"
            },
            "domainMax": {
              "expr": "Time_range_max_p"
            }
          }
        },
        "color": {
          "field": "Task Code",
          "scale": {
            "scheme": "pbiColorNominal"
          }
        }
      }
    },
    {
      "name": "milestone_mark",
      "mark": {
        "type": "circle",
        "opacity": 1,
        "strokeWidth": 1,
        "size": 30,
        "filled": false,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Report Date",
          "type": "temporal",
          "axis": {
            "title": "Report Date",
            "grid": true
          }
        },
        "y": {
          "field": "Milestone Finish Date",
          "type": "temporal",
          "axis": {
            "title": "Milestone Date",
            "grid": true
          }
        },
        "color": {
          "field": "Task Code",
          "scale": {
            "scheme": "pbiColorNominal"
          }
        },
        "tooltip": [
          {
            "field": "Task Code",
            "title": "Milestone Type"
          },
          {
            "field": "Milestone Finish Date",
            "title": "Finish Date",
            "type": "temporal"
          },
          {
            "field": "Report Date",
            "title": "Report Date",
            "type": "temporal"
          }
        ]
      }
    },
    {
      "data": {
        "values": [
          {
            "a": "2021-12-31",
            "b": "2021-12-31"
          },
          {
            "a": "2024-08-25",
            "b": "2024-08-25"
          }
        ]
      },
      "layer": [
        {
          "name": "date_equity",
          "mark": {
            "type": "line",
            "strokeDash": [8, 4],
            "strokeWidth": 1,
            "color": "red"
          },
          "encoding": {
            "x": {
              "field": "a",
              "type": "temporal"
            },
            "y": {
              "field": "b",
              "type": "temporal"
            }
          }
        },
        {
          "name": "whitespace",
          "mark": {
            "type": "area",
            "fill": "white"
          },
          "encoding": {
            "x": {
              "field": "a",
              "type": "temporal"
            },
            "y": {
              "field": "b",
              "type": "temporal"
            }
          }
        }
      ]
    }
  ],
  "config": {
    "style": {
      "cell": {"stroke": "transparent"}
    }
  }
}

data looks like this:

Report Date	Milestone Finish Date	Task Code
2023-07-01	2023-07-01	MSFT
2023-06-01	2023-06-20	MSFT
2023-05-01	2023-06-10	MSFT

the full dataset for producing the screenshots can be found here csv file on github

Answer 1

According to the Vega-Lite documentation (link), it looks like the area chart may expect that one of the axes include a quantitative variable (rather than two temporal variables, as in your case). I noticed a similar behavior as to what you're describing when attempting to replicate your use case on a smaller, toy example with two temporal axes.

Perhaps one workaround would be to leverage a dual-axis encoding with the area (triangle) dynamically generated. While I was not able to test it on your exact data, take a look at the following example (which is based on the simple line chart example in the editor):

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "Google's stock price over time.",
  "data": {"url": "data/stocks.csv"},
  "transform": [{"filter": "datum.symbol==='GOOG'"}],
  "layer": [
    {
      "mark": "line",
      "encoding": {
        "x": {"field": "date", "type": "temporal", "axis": {"orient": "top"}},
        "y": {"field": "date", "type": "temporal"}
      }
    },
    {
      "transform": [
        {
          "aggregate": [
            {"op": "min","field": "date","as": "min_date"},
            {"op": "max","field": "date","as": "max_date"}
          ]
        },
        {"fold": ["min_date", "max_date"]},
        {"calculate": "datum.value==datum.min_date ? 0 : 100", "as": "yvalue"}
      ],
      "mark": {"type": "area", "fill": "white"},
      "encoding": {
        "x": {"field": "value", "type": "temporal"},
        "y": {"field": "yvalue", "type": "quantitative", "axis": null}
      }
    }
  ]
}

Here is what you would see if you change the fill to "red" and unhide the dual axis by removing "axis": null. Note that the blue line here is fairly boring and just shows along the diagonal.

UPDATE 6/1 For the structure of the provided dataset (which already computes the desired Time_range_min and Time_range_max), it should be straightforward to specify the new layer for the triangle as follows:

{
  "transform": [
    {
      "aggregate": [
        {"op": "min","field": "Time_range_min","as": "min_date"},
        {"op": "max","field": "Time_range_max","as": "max_date"}
      ]
    },
    {"fold": ["min_date", "max_date"]},
    {"calculate": "datum.value==datum.min_date ? 0 : 100", "as": "yvalue"}
  ],
  "mark": {"type": "area", "fill": "white"},
  "encoding": {
    "x": {"field": "value", "type": "temporal"},
    "y": {"field": "yvalue", "type": "quantitative", "axis": null}
}

Answer 2

Problem was to get a data column that includes both global min and max value from both input columns ("Report Date" and "Milestone Finish Date"). This column can be then used for plotting both line and area. This column is obtained by calculate transform based on the internal dataset field "__row__" (see code)

Code is as follows:

{
  "width": 500,
  "height": 500,
  "title": {
    "text": "Milestone Trend Analysis"
  },
  "data": {"name": "dataset"},
  "transform": [
    {
      "joinaggregate": [
        {
          "op": "min",
          "field": "Report Date",
          "as": "Report_Date_min"
        },
        {
          "op": "max",
          "field": "Report Date",
          "as": "Report_Date_max"
        },
        {
          "op": "min",
          "field": "Milestone Finish Date",
          "as": "Milestone_Finish_Date_min"
        },
        {
          "op": "max",
          "field": "Milestone Finish Date",
          "as": "Milestone_Finish_Date_max"
        }
      ]
    },
    {
      "calculate": "datum.Report_Date_min < datum.Milestone_Finish_Date_min ? datum.Report_Date_min : datum.Milestone_Finish_Date_min",
      "as": "Global_min"
    },
    {
      "calculate": "datum.Report_Date_max > datum.Milestone_Finish_Date_max ? datum.Report_Date_max : datum.Milestone_Finish_Date_max",
      "as": "Global_max"
    },
    {
      "calculate": "datum['__row__'] <= 0 ? datum.Global_min : datum.Global_max",
      "as": "Time_range"
    }
  ],
  "params": [
    {
      "name": "Global_min_p",
      "expr": "data('data_0')[0]['Global_min']"
    },
    {
      "name": "Global_max_p",
      "expr": "data('data_0')[0]['Global_max']"
    }
  ],
  "layer": [
    {
      "name": "milestone_trace",
      "mark": "line",
      "encoding": {
        "x": {
          "field": "Report Date",
          "type": "temporal",
          "axis": {
            "title": "Report Date",
            "grid": true,
            "orient": "top"
          },
          "scale": {
            "domainMin": {
              "expr": "Global_min_p"
            },
            "domainMax": {
              "expr": "Global_max_p"
            }
          }
        },
        "y": {
          "field": "Milestone Finish Date",
          "type": "temporal",
          "axis": {
            "title": "Milestone Date",
            "grid": true
          },
          "scale": {
            "domainMin": {
              "expr": "Global_min_p"
            },
            "domainMax": {
              "expr": "Global_max_p"
            }
          }
        },
        "color": {
          "field": "Task Code",
          "scale": {
            "scheme": "pbiColorNominal"
          }
        }
      }
    },
    {
      "name": "milestone_mark",
      "mark": {
        "type": "circle",
        "opacity": 1,
        "strokeWidth": 1,
        "size": 30,
        "filled": false,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Report Date",
          "type": "temporal",
          "axis": {
            "title": "Report Date",
            "grid": true
          }
        },
        "y": {
          "field": "Milestone Finish Date",
          "type": "temporal",
          "axis": {
            "title": "Milestone Date",
            "grid": true
          }
        },
        "color": {
          "field": "Task Code",
          "scale": {
            "scheme": "pbiColorNominal"
          }
        },
        "tooltip": [
          {
            "field": "Task Code",
            "title": "Milestone Type"
          },
          {
            "field": "Milestone Finish Date",
            "title": "Finish Date",
            "type": "temporal"
          },
          {
            "field": "Report Date",
            "title": "Report Date",
            "type": "temporal"
          }
        ]
      }
    },
    {
      "name": "date_equity",
      "mark": {
        "type": "line",
        "strokeDash": [8, 4],
        "strokeWidth": 1,
        "color": "red"
      },
      "encoding": {
        "x": {
          "field": "Time_range",
          "type": "temporal"
        },
        "y": {
          "field": "Time_range",
          "type": "temporal"
        }
      }
    },
    {
      "name": "whitespace",
      "mark": {
        "type": "area",
        "fill": "white"
      },
      "encoding": {
        "x": {
          "field": "Time_range",
          "type": "temporal"
        },
        "y": {
          "field": "Time_range",
          "type": "temporal"
        }
      }
    }
  ],
  "config": {
    "style": {
      "cell": {"stroke": "transparent"}
    }
  }
}

Solution looks like this