vega-litedeneb
Vega Lite - Create totals on grouped level
I'm trying to create a matrix grouped on the column 'Product'. As row headers I use the column 'column' and as column headers I use the column 'YearMonthText'. The values that are shown in my matrix comes from the column '_Value'.
I want to create totals based on my row header 'Column' so that it sums the Forecast per Product and if possible an average of the Percentage per Product (don't know if possible to use a different aggregation)
Here is my code:
{
"data": {
"values": [
{"Customer": "Kevin N.V.", "Product": "PH114", "YearMonthText": "2024.01", "Column": "Forecast", "_Value": 24},
{"Customer": "Kevin N.V.", "Product": "PH114", "YearMonthText": "2024.01", "Column": "Percentage", "_Value": 0.90},
{"Customer": "Kevin N.V.", "Product": "PH114", "YearMonthText": "2024.02", "Column": "Forecast", "_Value": 74},
{"Customer": "Kevin N.V.", "Product": "PH114", "YearMonthText": "2024.02", "Column": "Percentage", "_Value": 0.75},
{"Customer": "Kevin N.V.", "Product": "PH878", "YearMonthText": "2024.01", "Column": "Forecast", "_Value": 744},
{"Customer": "Kevin N.V.", "Product": "PH878", "YearMonthText": "2024.01", "Column": "Percentage", "_Value": 0.05},
{"Customer": "Kevin N.V.", "Product": "PH878", "YearMonthText": "2024.02", "Column": "Forecast", "_Value": 4},
{"Customer": "Kevin N.V.", "Product": "PH878", "YearMonthText": "2024.02", "Column": "Percentage", "_Value": 0.07},
{"Customer": "Klaas N.V.", "Product": "PH200", "YearMonthText": "2024.01", "Column": "Forecast", "_Value": 77},
{"Customer": "Klaas N.V.", "Product": "PH200", "YearMonthText": "2024.01", "Column": "Percentage", "_Value": 0.65},
{"Customer": "Klaas N.V.", "Product": "PH200", "YearMonthText": "2024.02", "Column": "Forecast", "_Value": 80},
{"Customer": "Klaas N.V.", "Product": "PH200", "YearMonthText": "2024.02", "Column": "Percentage", "_Value": 0.8},
{"Customer": "Klaas N.V.", "Product": "PH527", "YearMonthText": "2024.01", "Column": "Forecast", "_Value": 4},
{"Customer": "Klaas N.V.", "Product": "PH527", "YearMonthText": "2024.01", "Column": "Percentage", "_Value": 0.77},
{"Customer": "Klaas N.V.", "Product": "PH527", "YearMonthText": "2024.02", "Column": "Forecast", "_Value": 88},
{"Customer": "Klaas N.V.", "Product": "PH527", "YearMonthText": "2024.02", "Column": "Percentage", "_Value": 0.9}
]
},
"facet": {
"row": {
"field": "Product",
"title": null,
"header": {"labelFontSize": 14}
}
},
"spec": {
"layer": [
{"mark": {"type": "rect"}},
{
"mark": {
"type": "text",
"fontSize": 12
},
"encoding": {
"y": {
"field": "Column",
"title": null
},
"x": {
"field": "YearMonthText",
"title": null,
"axis": {"orient": "top"}
},
"text": {
"field": "_Value",
"type": "quantitative"
}
}
},
{
"name": "Totals",
"transform": [
{
"aggregate": [
{
"op": "sum",
"field": "_Value",
"as": "_New_Value"
}
],
"groupby": [
"Product",
"Column"
]
}
],
"mark": {
"type": "text",
"align": "right",
"fontSize": 16,
"fontWeight": "bold",
"x": 200,
"color": "black"
},
"encoding": {
"x": {
"field": "Column",
"type": "nominal",
"axis": {"title": null}
},
"text": {
"field": "_New_Value_Formatted",
"type": "nominal"
}
}
}
]
},
"resolve": {
"axis": {
"x": "independent",
"y": "independent"
}
}
}
As soon as I add my 'calculate' for the formatting" inside my 'transform', I get an error stating:
{
"calculate": "if( datum['Column'] == 'Percentage', format( datum['_New_Value'], '.0%'), if( datum['Column'] == 'Forecast', format( datum['_New_Value'], ',.0f'), format( datum['_New_Value'], ',.0f')",
"as": "_New_Value_Formatted"
}
Is there anyone who can help me solve this problem?
Thank you!
Solution
try this:
{
"data": {
"values": [
{
"Customer": "Kevin N.V.",
"Product": "PH114",
"YearMonthText": "2024.01",
"Column": "Forecast",
"_Value": 24
},
{
"Customer": "Kevin N.V.",
"Product": "PH114",
"YearMonthText": "2024.01",
"Column": "Percentage",
"_Value": 0.9
},
{
"Customer": "Kevin N.V.",
"Product": "PH114",
"YearMonthText": "2024.02",
"Column": "Forecast",
"_Value": 74
},
{
"Customer": "Kevin N.V.",
"Product": "PH114",
"YearMonthText": "2024.02",
"Column": "Percentage",
"_Value": 0.75
},
{
"Customer": "Kevin N.V.",
"Product": "PH878",
"YearMonthText": "2024.01",
"Column": "Forecast",
"_Value": 744
},
{
"Customer": "Kevin N.V.",
"Product": "PH878",
"YearMonthText": "2024.01",
"Column": "Percentage",
"_Value": 0.05
},
{
"Customer": "Kevin N.V.",
"Product": "PH878",
"YearMonthText": "2024.02",
"Column": "Forecast",
"_Value": 4
},
{
"Customer": "Kevin N.V.",
"Product": "PH878",
"YearMonthText": "2024.02",
"Column": "Percentage",
"_Value": 0.07
},
{
"Customer": "Klaas N.V.",
"Product": "PH200",
"YearMonthText": "2024.01",
"Column": "Forecast",
"_Value": 77
},
{
"Customer": "Klaas N.V.",
"Product": "PH200",
"YearMonthText": "2024.01",
"Column": "Percentage",
"_Value": 0.65
},
{
"Customer": "Klaas N.V.",
"Product": "PH200",
"YearMonthText": "2024.02",
"Column": "Forecast",
"_Value": 80
},
{
"Customer": "Klaas N.V.",
"Product": "PH200",
"YearMonthText": "2024.02",
"Column": "Percentage",
"_Value": 0.8
},
{
"Customer": "Klaas N.V.",
"Product": "PH527",
"YearMonthText": "2024.01",
"Column": "Forecast",
"_Value": 4
},
{
"Customer": "Klaas N.V.",
"Product": "PH527",
"YearMonthText": "2024.01",
"Column": "Percentage",
"_Value": 0.77
},
{
"Customer": "Klaas N.V.",
"Product": "PH527",
"YearMonthText": "2024.02",
"Column": "Forecast",
"_Value": 88
},
{
"Customer": "Klaas N.V.",
"Product": "PH527",
"YearMonthText": "2024.02",
"Column": "Percentage",
"_Value": 0.9
}
]
},
"facet": {
"row": {"field": "Product", "title": null, "header": {"labelFontSize": 14}}
},
"spec": {
"width": {"step": 60},
"layer": [
{
"mark": {
"type": "rect",
"color": "#2A6AC8",
"cornerRadius": 5,
"stroke": "#ffffff",
"strokeWidth": 0
}
},
{
"mark": {"type": "text", "fontSize": 12, "color": "#ffffff"},
"encoding": {
"y": {
"field": "Column",
"title": null,
"axis": {
"grid": false,
"domain": false,
"ticks": false,
"offset": 5
}
},
"x": {
"field": "YearMonthText",
"title": null,
"axis": {
"orient": "top",
"grid": false,
"domain": false,
"ticks": false,
"offset": 3,
"labelAngle": 0
}
},
"text": {"field": "_Value", "type": "quantitative"}
}
},
{
"name": "Totals",
"transform": [
{
"aggregate": [{"op": "sum", "field": "_Value", "as": "_New_Value"}],
"groupby": ["Product", "Column"]
},
{
"calculate": "datum.Column === 'Percentage' ? format( datum._New_Value, '.0%') : datum.Column === 'Forecast' ? format( datum._New_Value, ',.0f') : format( datum._New_Value, ',.0f')",
"as": "_New_Value_Formatted"
}
],
"mark": {
"type": "text",
"align": "left",
"fontSize": 14,
"fontWeight": "bold",
"dx": 70,
"color": "black"
},
"encoding": {
"y": {"field": "Column", "title": null},
"text": {"field": "_New_Value_Formatted", "type": "nominal"}
}
}
]
},
"resolve": {"axis": {"x": "independent", "y": "independent"}},
"config": {"view": {"stroke": "transparent"}}
}
- How to Set Default Collapsed State in Deneb-Vega Gantt Chart for Power BI
- y axis scale ticks for small decimal value in Vegalite
- How do I get complete interval tick grid for top most value in VegaLite
- Vega Lite Percent of a count for a rolling year,
- Vega-lite Gradient: Specify Where the Color Changes Relative to the Axis Scale
- Deneb (Vega lite) fixed hconcat widths when filtering
- Vega lite tooltip when hovering stacked bar text marks
- First bar highlight by a specific color in VegaLite Bar chart
- Set the x axis limits (min and max) on categorical scale
- IBCS profit loss waterfall chart in Vega-lite, issues regarding bar positions
- vega lite colour text using a field
- vega lite format zeros and negative values as blank and have a K or M suffix depending on the number size
- Deneb vega-lite hconcat different spacing or padding between charts
- Vega-lite assigning parameter to axis labels
- Vega-Lite - Different aggregation on totals based on row header
- Share x axes in a vega-lite concatenated plot when one subplot is using timeunit and aggregation in the encoding
- Deneb click on axis label to cross filter
- Conditional opacity for stacked bar chart text
- Deneb - scatterplot x & y sorted asc
- Concatenate Charts in Vega (rows, columns, grid)
- Vega Lite - Create totals on grouped level
- How to get store a changing signal value in a java variable using the VEGA API?
- Convert Vega (Deneb) Gauge to Work in PowerBI
- How to hide labels on X-Axis in Vegalite chart on excess amount of data
- Bars not extending the full size in Vega/Altair plot
- VEGA Lite, How to create separate Legends for concatenated charts
- Why line chart doesn't plot measures that have same value for X-axis in Power BI?
- Time and Date Data Incorrectly Plotted
- How to show only ten results in vega-lite with this sorted chart
- Sankey Diagram (Alluvial Diagram) in Vega-lite