Vega Lite：分组层级总计生成及格式化处理报错求助

问题说明 #

需要创建按Product列分组的数据矩阵：

• 行标题：Column列
• 列标题：YearMonthText列
• 矩阵值：_Value列
• 总计规则：对每个Product的Forecast值求和，对Percentage值计算平均值
• 添加格式化calculate转换时出现报错

错误原因 #

1. calculate表达式语法错误：原if语句缺少闭合的右括号，导致解析失败
2. 聚合逻辑不符合需求：原配置对所有Column类型都使用sum聚合，未区分Forecast（求和）和Percentage（求平均）

修正后的完整配置代码 #

{
  "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": [
              {
                "as": "_New_Value",
                "expr": "if(datum.Column === 'Forecast', sum(datum._Value), mean(datum._Value))"
              }
            ],
            "groupby": ["Product", "Column"]
          },
          {
            "calculate": "if(datum.Column == 'Percentage', format(datum._New_Value, '.0%'), format(datum._New_Value, ',.0f'))",
            "as": "_New_Value_Formatted"
          }
        ],
        "mark": {
          "type": "text",
          "align": "right",
          "fontSize": 16,
          "fontWeight": "bold",
          "x": 200,
          "color": "black"
        },
        "encoding": {
          "y": {
            "field": "Column",
            "type": "nominal",
            "axis": {"title": null}
          },
          "text": {
            "field": "_New_Value_Formatted",
            "type": "nominal"
          }
        }
      }
    ]
  },
  "resolve": {
    "axis": {
      "x": "independent",
      "y": "independent"
    }
  }
}

关键修正点 #

• 聚合逻辑修正：在aggregate转换中使用expr表达式，根据Column类型选择sum（Forecast）或mean（Percentage）
• calculate语法修复：补全if语句的闭合括号，简化冗余的条件分支
• 总计层编码修正：将总计文本的x编码改为y，与主矩阵的行标题对齐，确保总计文本显示在对应行的右侧

内容的提问来源于stack exchange，提问作者KevinN1992