• 13
• 3

• 13
• 3

• 13
• 3

I've been working on a personal project to create an election tracker and then a published dashboard in google looker. I have a google sheet imported and a simple line chart working already but I can't get the vega-lite visual to work.

This is how the sheet is structured:

And this is a table I have working now: setup chart

What I want is to essentially replicate how the shaded error bands work in power bi, minimum and maximum being the values per party.

so for the line which is a simple

SUM(VVD*Calc weight)/SUM(Calc weight)

the error band should be min(VVD) and max(VVD), shaded appropriately between.

I've gotten this far with this code which renders the chart well enough but no data is showing up:

{
 "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
 "description": "Line chart showing weighted average party support with min/max error bands.",
 "title": {
 "text": "Weighted Average Party Support with Min/Max Bands",
 "anchor": "middle",
 "fontSize": 16,
 "offset": 10
 },
 "data": {"name": "data"},
 "transform": [
 {
 "fold": ["PVV", "VVD", "GLPvdA", "CDA"],
 "as": ["party", "value"]
 },
 {"calculate": "toDate(datum['Fieldwork date'])", "as": "Fieldwork date"},
 {"calculate": "toNumber(datum.value)", "as": "value"},
 {"calculate": "toNumber(datum['Calc weight'])", "as": "Calc weight"},
 {
 "calculate": "datum.value * datum['Calc weight']",
 "as": "weighted_value"
 },
 {
 "aggregate": [
 {"op": "sum", "field": "weighted_value", "as": "sum_weighted_value"},
 {"op": "sum", "field": "Calc weight", "as": "sum_calc_weight"},
 {"op": "min", "field": "value", "as": "min_party_value"},
 {"op": "max", "field": "value", "as": "max_party_value"}
 ],
 "groupby": ["Fieldwork date", "party"]
 },
 {
 "calculate": "datum.sum_weighted_value / datum.sum_calc_weight",
 "as": "weighted_avg"
 },
 {"filter": "isValid(datum.weighted_avg)"}
 ],
 "layer": [
 {
 "mark": {
 "type": "area",
 "opacity": 0.2,
 "line": false
 },
 "encoding": {
 "x": {
 "field": "Fieldwork date",
 "type": "temporal",
 "title": "Date",
 "axis": {"format": "%Y-%m"}
 },
 "y": {
 "field": "min_party_value",
 "type": "quantitative",
 "title": "Party Support (%)",
 "scale": {"zero": false}
 },
 "y2": {"field": "max_party_value"},
 "color": {
 "field": "party",
 "type": "nominal",
 "title": "Party",
 "legend": {"orient": "bottom", "columns": 2}
 },
 "tooltip": [
 {"field": "Fieldwork date", "type": "temporal", "title": "Date", "format": "%Y-%m"},
 {"field": "party", "type": "nominal", "title": "Party"},
 {"field": "weighted_avg", "type": "quantitative", "title": "Weighted Avg", "format": ".1f"},
 {"field": "min_party_value", "type": "quantitative", "title": "Min Value", "format": ".1f"},
 {"field": "max_party_value", "type": "quantitative", "title": "Max Value", "format": ".1f"}
 ]
 }
 },
 {
 "mark": {"type": "line", "point": true},
 "encoding": {
 "x": {
 "field": "Fieldwork date",
 "type": "temporal"
 },
 "y": {"field": "weighted_avg", "type": "quantitative"},
 "color": {"field": "party", "type": "nominal"},
 "tooltip": [
 {"field": "Fieldwork date", "type": "temporal", "title": "Date", "format": "%Y-%m"},
 {"field": "party", "type": "nominal", "title": "Party"},
 {"field": "weighted_avg", "type": "quantitative", "title": "Weighted Avg", "format": ".1f"},
 {"field": "min_party_value", "type": "quantitative", "title": "Min Value", "format": ".1f"},
 {"field": "max_party_value", "type": "quantitative", "title": "Max Value", "format": ".1f"}
 ]
 }
 }
 ],
 "resolve": {
 "axis": {"y": "independent"}
 }
}

chart setup

The calculation works in the basic chart, so I assume the json is the problem here, which wouldn't be surprising, it's really not my forte.

Can you take a look at it and help me clean this up if it's a reasonable thing to achieve/request?

Many thanks!!