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"}}
}