Waterfall chart with subtotals

Question

I need to create a waterfall chart with vega but I need to have subtotals. For this subtotals the next bars keep showing as expected but starting at the subtotal quantity.

This is feasible with excel and Tableau but I didn't manage to do it with Vega.

I need to create something like this: Example

Any idea if the vega waterfall chart allows this functionality

Answer 1

Here is an example of Vega waterfall chart with "subtotals" bars the way you described.

There is a waterfall chart in Vega-Lite gallery (but not in Vega), so we start off with using the Vega version that is generated by Vega-Lite in the on-line editor.

The first step is to add records to the input data for where the subtotals will appear. In this example, we added "Qtr_1", "Qtr_2" and "Qtr_3" to represent quarterly subtotals:

"data": [
    {
      "name": "source_0",
      "values": [
        {"label": "Begin", "amount": 4000},
        {"label": "Jan", "amount": 1707},
        {"label": "Feb", "amount": -1425},
        {"label": "Mar", "amount": -1030},

        {"label": "Qtr_1", "amount": 0},

        {"label": "Apr", "amount": 1812},
        {"label": "May", "amount": -1067},
        {"label": "Jun", "amount": -1481},

        {"label": "Qtr_2", "amount": 0},

        {"label": "Jul", "amount": 1228},
        {"label": "Aug", "amount": 1176},
        {"label": "Sep", "amount": 1146},

        {"label": "Qtr_3", "amount": 0},
        
        {"label": "Oct", "amount": 1205},
        {"label": "Nov", "amount": -1388},
        {"label": "Dec", "amount": 1492},
        {"label": "End", "amount": 0}
      ]
    },

Note that there are existing bars for "Begin" and "End". We can modify the code referencing these bars to render bars for quarterly subtotals.

For example, code such as

"fill": [
     {
        "test": "datum.label === 'Begin' || datum.label === 'End',
        "value": "#725a30"
     },

has been changed to:

"fill": [
     {
        "test": "datum.label === 'Begin' || datum.label === 'End' || substring(datum.label, 0, 4) === 'Qtr_'",
         "value": "#725a30"
     },

Often code has been changed to use signal expressions for greater flexibility. For example,

"y": {"scale": "y", "field": "previous_sum"},

has been changed to

"y": {"signal": "scale('y', substring(datum.label, 0, 4) === 'Qtr_' ? 0 : datum['previous_sum'])"},

Here is the result waterfall chart rendered on the Vega online editor. To find code changes, search for "Qtr_" in the code.