waterfall overlay

spec.json

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "width": "container",
  "layer": [
    {
      "mark": {"type": "line", "point": false},
      "transform": [{"filter": "datum.type != 'Waterfall'"}],
      "encoding": {
        "x": {"field": "year", "type": "ordinal"},
        "y": {
          "field": "value",
          "type": "quantitative",
          "scale": {"zero": false}
        },
        "detail": {"field": "detail", "type": "ordinal"}
      }
    },
    {
      "mark": "rule",
      "transform": [{"filter": "datum.year == 9 && datum.type=='Actual'"}],
      "encoding": {
        "x": {"field": "year"},
        "color": {"value": "green"},
        "size": {"value": 0.1}
      }
    },
    {
      "mark": "rule",
      "transform": [{"filter": "datum.year == -1 && datum.type=='Actual'"}],
      "encoding": {
        "x": {"field": "year"},
        "color": {"value": "green"},
        "size": {"value": 0.1}
      }
    },
    {
      "mark": {"type": "line", "point": false},
      "transform": [{"filter": "datum.type == 'Waterfall'"}],
      "encoding": {
        "x": {"field": "year", "type": "ordinal"},
        "y": {
          "field": "value",
          "type": "quantitative",
          "scale": {"zero": true}
        },
        "detail": {"field": "detail", "type": "ordinal"},
        "color": {"value": "red"},
        "size": {"value": 0.5}
      }
    },
    {
      "mark": {"type": "point"},
      "transform": [{"filter": "datum.type == 'Actual'"}],
      "encoding": {
        "x": {"field": "year", "type": "ordinal"},
        "y": {
          "field": "value",
          "type": "quantitative",
          "scale": {"zero": true}
        },
        "detail": {"field": "detail", "type": "ordinal"},
        "color": {"value": "red"},
        "size": {"value": 20}
      }
    },
    {
      "mark": {"type": "rect","width":7},
      "data": {"name": "waterfall"},
      "transform": [
        {"window": [{"op": "lag", "field": "value", "as": "lag"}]},
        {"calculate": "datum.value - datum.lag", "as": "amount"},
        {"calculate": "datum.type", "as": "label"},
        {"window": [{"op": "sum", "field": "amount", "as": "sum"}]},
        {"window": [{"op": "lead", "field": "label", "as": "lead"}]},
        {
          "calculate": "datum.lead === null ? datum.label : datum.lead",
          "as": "lead"
        },
        {
          "calculate": "datum.label === 'End' ? 0 : datum.sum - datum.amount",
          "as": "previous_sum"
        },
        {
          "calculate": "datum.label === 'End' ? datum.sum : datum.amount",
          "as": "amount"
        },
        {
          "calculate": "(datum.label !== 'Begin' && datum.label !== 'End' && datum.amount > 0 ? '+' : '') + datum.amount",
          "as": "text_amount"
        },
        {"calculate": "(datum.sum + datum.previous_sum) / 2", "as": "center"},
        {
          "calculate": "datum.sum < datum.previous_sum ? datum.sum : ''",
          "as": "sum_dec"
        },
        {
          "calculate": "datum.sum > datum.previous_sum ? datum.sum : ''",
          "as": "sum_inc"
        },
        { "filter": "datum.type != 'Expected' && datum.type != 'End'" },
        { "calculate": "9", "as": "year"},
        { "calculate": "datum.year + datum.type", "as": "yeartype"}
      ],
      "encoding": {
        "x": {"field": "yeartype"},
        "xOffset": {"field": "type", "sort": "-color"},
        "y": {
          "field": "previous_sum",
          "type": "quantitative", "axis": { "title": "value"}
        },
        "y2": {
          "field": "value"
        },
        "color": {"field": "type", "type": "nominal"},
        "opacity": { "value": 0.3 }
      }
    }
  ],
  "datasets": {
    "waterfall": [
      {"type": "Expected", "function": "subs", "value": 118},
      {"type": "-> Actual Sales", "function": "subs", "value": 148},
      {"type": "-> Actual Churn (=Actual)", "function": "subs", "value": 111},
      {"type": "End", "function": "subs", "value": 111}
    ],
    "AEs": [
      {
        "detail": -1,
        "type": "Actual",
        "function": "subs",
        "year": -1,
        "value": 100
      },
      {
        "detail": -1,
        "type": "Expected",
        "function": "subs",
        "year": 0,
        "value": 101.65
      },
      {
        "detail": 0,
        "type": "Actual",
        "function": "subs",
        "year": 0,
        "value": 95
      },
      {
        "detail": 0,
        "type": "Expected",
        "function": "subs",
        "year": 1,
        "value": 96.56750000000001
      },
      {
        "detail": 1,
        "type": "Actual",
        "function": "subs",
        "year": 1,
        "value": 100
      },
      {
        "detail": 1,
        "type": "Expected",
        "function": "subs",
        "year": 2,
        "value": 101.65
      },
      {
        "detail": 2,
        "type": "Actual",
        "function": "subs",
        "year": 2,
        "value": 112
      },
      {
        "detail": 2,
        "type": "Expected",
        "function": "subs",
        "year": 3,
        "value": 113.848
      },
      {
        "detail": 3,
        "type": "Actual",
        "function": "subs",
        "year": 3,
        "value": 102
      },
      {
        "detail": 3,
        "type": "Expected",
        "function": "subs",
        "year": 4,
        "value": 103.68299999999999
      },
      {
        "detail": 4,
        "type": "Actual",
        "function": "subs",
        "year": 4,
        "value": 103
      },
      {
        "detail": 4,
        "type": "Expected",
        "function": "subs",
        "year": 5,
        "value": 104.6995
      },
      {
        "detail": 5,
        "type": "Actual",
        "function": "subs",
        "year": 5,
        "value": 112
      },
      {
        "detail": 5,
        "type": "Expected",
        "function": "subs",
        "year": 6,
        "value": 113.848
      },
      {
        "detail": 6,
        "type": "Actual",
        "function": "subs",
        "year": 6,
        "value": 117
      },
      {
        "detail": 6,
        "type": "Expected",
        "function": "subs",
        "year": 7,
        "value": 118.9305
      },
      {
        "detail": 7,
        "type": "Actual",
        "function": "subs",
        "year": 7,
        "value": 115
      },
      {
        "detail": 7,
        "type": "Expected",
        "function": "subs",
        "year": 8,
        "value": 116.8975
      },
      {
        "detail": 8,
        "type": "Actual",
        "function": "subs",
        "year": 8,
        "value": 113
      },
      {
        "detail": 8,
        "type": "Expected",
        "function": "subs",
        "year": 9,
        "value": 114.86449999999999
      },
      {
        "detail": 9,
        "type": "Actual",
        "function": "subs",
        "year": 9,
        "value": 111
      },
      {
        "detail": 9,
        "type": "Expected",
        "function": "subs",
        "year": 10,
        "value": 112.83149999999999
      },
      {
        "detail": 9,
        "type": "Expected",
        "function": "subs",
        "year": 11,
        "value": 114.69321975
      },
      {
        "detail": 9,
        "type": "Expected",
        "function": "subs",
        "year": 12,
        "value": 116.58565787587499
      },
      {"type": "Waterfall", "function": "subs", "year": -1, "value": 100},
      {"type": "Waterfall", "function": "subs", "year": 0, "value": 101.65},
      {"type": "Waterfall", "function": "subs", "year": 1, "value": 103.327225},
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 2,
        "value": 105.0321242125
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 3,
        "value": 106.76515426200625
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 4,
        "value": 108.52677930732936
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 5,
        "value": 110.3174711659003
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 6,
        "value": 112.13770944013766
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 7,
        "value": 113.98798164589994
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 8,
        "value": 115.86878334305729
      },
      {
        "type": "Waterfall",
        "function": "subs",
        "year": 9,
        "value": 117.78061826821774
      },
      {"type": "Waterfall", "function": "subs", "year": 9, "value": 111}
    ]
  },
  "data": {"name": "AEs"},
  "resolve": {"scale": {"x": "independent"}}
}