Create a vega-lite chart showing change since last year/month
Just wanted to share a way you can display revenue, expenses, profit values and show variance based on previous year. Expense colors should be reversed since less is better. Answer below.
Solution
Here we go:
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"description": "Using window transform to impute missing values in a line chart by averaging the previous and next values.",
"title": [
"Revenue, Expenses, Profit: 2024",
"Variance based on previous year/month",
""
],
"data": {
"values": [
{"period": "2024-01", "type": "Revenue", "CY": 1000, "PY": 900},
{"period": "2024-02", "type": "Revenue", "CY": 1100, "PY": 800},
{"period": "2024-03", "type": "Revenue", "CY": 1300, "PY": 1350},
{"period": "2024-04", "type": "Revenue", "CY": 900, "PY": 1110},
{"period": "2024-05", "type": "Revenue", "CY": 950, "PY": 750},
{"period": "2024-06", "type": "Revenue", "CY": 1030, "PY": 1000},
{"period": "2024-07", "type": "Revenue", "CY": 1080, "PY": 950},
{"period": "2024-08", "type": "Revenue", "CY": 900, "PY": 910},
{"period": "2024-09", "type": "Revenue", "CY": 960, "PY": 800},
{"period": "2024-10", "type": "Revenue", "CY": 1200, "PY": 1000},
{"period": "2024-11", "type": "Revenue", "CY": 1280, "PY": 1350},
{"period": "2024-12", "type": "Revenue", "CY": 1400, "PY": 1350},
{"period": "2024-01", "type": "Expenses", "CY": 600, "PY": 500},
{"period": "2024-02", "type": "Expenses", "CY": 550, "PY": 500},
{"period": "2024-03", "type": "Expenses", "CY": 600, "PY": 500},
{"period": "2024-04", "type": "Expenses", "CY": 500, "PY": 500},
{"period": "2024-05", "type": "Expenses", "CY": 450, "PY": 550},
{"period": "2024-06", "type": "Expenses", "CY": 630, "PY": 750},
{"period": "2024-07", "type": "Expenses", "CY": 380, "PY": 650},
{"period": "2024-08", "type": "Expenses", "CY": 200, "PY": 350},
{"period": "2024-09", "type": "Expenses", "CY": 660, "PY": 450},
{"period": "2024-10", "type": "Expenses", "CY": 700, "PY": 500},
{"period": "2024-11", "type": "Expenses", "CY": 480, "PY": 700},
{"period": "2024-12", "type": "Expenses", "CY": 300, "PY": 400},
{"period": "2024-01", "type": "Profit", "CY": 100, "PY": 200},
{"period": "2024-02", "type": "Profit", "CY": 200, "PY": 150},
{"period": "2024-03", "type": "Profit", "CY": 300, "PY": 350},
{"period": "2024-04", "type": "Profit", "CY": 100, "PY": 50},
{"period": "2024-05", "type": "Profit", "CY": 250, "PY": 300},
{"period": "2024-06", "type": "Profit", "CY": 330, "PY": 500},
{"period": "2024-07", "type": "Profit", "CY": 180, "PY": 100},
{"period": "2024-08", "type": "Profit", "CY": 200, "PY": 300},
{"period": "2024-09", "type": "Profit", "CY": 360, "PY": 450},
{"period": "2024-10", "type": "Profit", "CY": 100, "PY": 200},
{"period": "2024-11", "type": "Profit", "CY": 280, "PY": 100},
{"period": "2024-12", "type": "Profit", "CY": 300, "PY": 300}
]
},
"usermeta": {"embedOptions": {"renderer": "svg"}},
"transform": [
{
"calculate": "datum.CY - datum.PY < 0 && (datum.type === 'Revenue' || datum.type === 'Profit') ? 'neg' : datum.CY - datum.PY > 0 && (datum.type === 'Expenses') ? 'neg' :'pos'",
"as": "diff"
},
{
"calculate": "(datum.type === 'Expenses' ? (datum.CY == datum.PY ? '🠊' : datum.CY - datum.PY > 0 ? '🠉' : '🠋') : (datum.CY == datum.PY ? '🠊' : datum.CY - datum.PY < 0 ? '🠋' : '🠉'))+ format((datum.CY - datum.PY < 0 ? (datum.CY - datum.PY)*-1 : (datum.CY - datum.PY)), '.0f')",
"as": "icon"
},
{
"calculate": "datum.CY > datum.PY ? datum.PY : datum.CY",
"as": "smallVal"
},
{"calculate": "datum.CY <= datum.PY ? datum.PY : datum.CY", "as": "bigVal"},
{
"joinaggregate": [{"op": "min", "field": "smallVal", "as": "minValTemp"}],
"groupby": ["type"]
},
{"calculate": "round(datum.minValTemp * 0.8,0)", "as": "minVal"},
{
"joinaggregate": [{"op": "max", "field": "bigVal", "as": "maxValTemp"}],
"groupby": ["type"]
},
{"calculate": "round(datum.maxValTemp * 1.2,0)", "as": "maxVal"}
],
"vconcat": [
{
"width": 500,
"height": 200,
"transform": [{"filter": "datum.type == 'Revenue'"}],
"encoding": {
"x": {
"timeUnit": "yearmonth",
"field": "period",
"type": "ordinal",
"axis": {"format": "%b", "title": null}
},
"y": {
"axis": {"grid": false, "title": "Revenue"},
"scale": {
"domain": [
{"expr": "data('data_0')[0].minVal"},
{"expr": "data('data_0')[0].maxVal"}
]
}
}
},
"layer": [
{
"transform": [
{
"window": [{"op": "lag", "field": "CY", "as": "CY2"}],
"frame": [-1, 0]
}
],
"mark": {
"type": "rule",
"color": "black",
"xOffset": -21,
"strokeWidth": 1,
"strokeCap": "square",
"strokeDash": [4, 3]
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"},
"y2": {"field": "CY2"}
}
},
{
"mark": {"type": "bar", "point": true, "color": "black", "width": 30},
"encoding": {
"y": {"field": "PY", "type": "quantitative"},
"y2": {"field": "CY"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
},
"opacity": {"value": 0.5}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 42
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 30
},
"encoding": {"y": {"field": "PY", "type": "quantitative"}}
},
{
"mark": {"type": "text", "point": true, "color": "black"},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "text",
"point": true,
"yOffset": 12,
"fontSize": 10
},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "icon"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
}
}
}
]
},
{
"width": 500,
"height": 200,
"transform": [{"filter": "datum.type == 'Expenses'"}],
"encoding": {
"x": {
"timeUnit": "yearmonth",
"field": "period",
"type": "ordinal",
"axis": {"format": "%b", "title": null}
},
"y": {
"axis": {"grid": false, "title": "Expenses"},
"scale": {
"domain": [
{"expr": "data('data_0')[13].minVal"},
{"expr": "data('data_0')[13].maxVal"}
]
}
}
},
"layer": [
{
"transform": [
{
"window": [{"op": "lag", "field": "CY", "as": "CY2"}],
"frame": [-1, 0]
}
],
"mark": {
"type": "rule",
"color": "black",
"xOffset": -21,
"strokeWidth": 1,
"strokeCap": "square",
"strokeDash": [4, 3]
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"},
"y2": {"field": "CY2"}
}
},
{
"mark": {"type": "bar", "point": true, "color": "black", "width": 30},
"encoding": {
"y": {"field": "PY", "type": "quantitative"},
"y2": {"field": "CY"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
},
"opacity": {"value": 0.5}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 42
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 30
},
"encoding": {"y": {"field": "PY", "type": "quantitative"}}
},
{
"mark": {"type": "text", "point": true, "color": "black"},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "text",
"point": true,
"yOffset": 12,
"fontSize": 10
},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "icon"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
}
}
}
]
},
{
"width": 500,
"height": 200,
"transform": [{"filter": "datum.type == 'Profit'"}],
"encoding": {
"x": {
"timeUnit": "yearmonth",
"field": "period",
"type": "ordinal",
"axis": {"format": "%b", "title": null}
},
"y": {
"axis": {"grid": false, "title": "Profit"},
"scale": {
"domain": [
{"expr": "data('data_0')[25].minVal"},
{"expr": "data('data_0')[25].maxVal"}
]
}
}
},
"layer": [
{
"transform": [
{
"window": [{"op": "lag", "field": "CY", "as": "CY2"}],
"frame": [-1, 0]
}
],
"mark": {
"type": "rule",
"color": "black",
"xOffset": -21,
"strokeWidth": 1,
"strokeCap": "square",
"strokeDash": [4, 3]
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"},
"y2": {"field": "CY2"}
}
},
{
"mark": {"type": "bar", "point": true, "color": "black", "width": 30},
"encoding": {
"y": {"field": "PY", "type": "quantitative"},
"y2": {"field": "CY"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
},
"opacity": {"value": 0.5}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 42
},
"encoding": {
"opacity": {"value": 1},
"y": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "tick",
"point": true,
"color": "black",
"thickness": 1,
"width": 30
},
"encoding": {"y": {"field": "PY", "type": "quantitative"}}
},
{
"mark": {"type": "text", "point": true, "color": "black"},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "CY", "type": "quantitative"}
}
},
{
"mark": {
"type": "text",
"point": true,
"yOffset": 12,
"fontSize": 10
},
"encoding": {
"y": {"field": "maxVal", "type": "quantitative"},
"text": {"field": "icon"},
"color": {
"condition": {"test": "datum.diff === 'neg'", "value": "red"},
"value": "green"
}
}
}
]
}
],
"config": {"view": {"stroke": "transparent"}}
}
- How to Change x-axis to logarithmic in PLOTLY histogram
- Visualization of calendar events. Algorithm to layout events with maximum width
- Using Django to visualise scraped data
- Vega multi-line chart messed up by moving brushed area but works with fold
- month and year wise label on X-axis in Vega Lite
- Vega-Lite line charts: labelling final point on hover
- Interactive proportional value of a pandas column displayed using altair pie chart in python?
- Vega performant toggling visibility of marks
- ggplot2 in R: mean summary point for subgroups in grids
- How do I do a jointplot in R the same way as I do it python (seaborn package)
- Add and remove items from an array using Vega signals
- select specific columns from a dataset using Vega
- How do I get the diagonal of sns.pairplot?
- center bar in dynamic number of columns
- How to interpolate 3d variable like RAINC from netcdf file using wrf-python
- sample or density transform based on zoom domain
- R - Add a column to a heatmap that is independent of it to visually see correlations between grouping and a response variable
- add a linebreak in lengthy expressions
- Vega: access updated x and y of text after label transformation
- Stacked barplot for multi-level grouped barplot
- plotting wind direction from netcdf file using quiver
- How to extract hex color codes from a colormap
- Create a vega-lite chart showing change since last year/month
- show strokeWidth on bars using Deneb
- How to remove or hide y-axis ticklabels from a plot
- Vega lite deneb boxplot whisker width changed since update how to change whisker tick width
- Matplotlib: Can we draw a histogram and a box plot on a same chart?
- 2x2 interaction plot when you only have summary statistics for each group
- Facing issues in plotting stacked bar chart with binning applied in vega lite
- Power BI - drill down on line chart