将一个字段的总和设置为另一个字段的总和如何操作？

huangapple
go评论63阅读模式
英文:

How to set the domain from to the sum of a field to the sum of another field?

问题

I would like to calculate the sum of "SUM A" to set this as minimum scale
value for all "X" axis.
And the sum of "SUM B" to set als maximum scale for all "X" axis.

I can't get my head around how to calculate the sum of the rows and use them in the "domain".

Hope the edit and example makes it more understandable.

I'm sorry if my first attempt to phrase the question wasn't clear.

{
"data": { "values":[
{ "EBENE_1": "A", "EBENE_2": "A", "EBENE_3": "A", "EBENE_4": "A" , "SUM A": -70 , "SUM B": 41 },
{ "EBENE_1": "A", "EBENE_2": "A", "EBENE_3": "B", "EBENE_4": "B" , "SUM A": -22 , "SUM B": 81 },
{ "EBENE_1": "A", "EBENE_2": "B", "EBENE_3": "C", "EBENE_4": "C" , "SUM A": -31 , "SUM B": 89 },
{ "EBENE_1": "A", "EBENE_2": "B", "EBENE_3": "D", "EBENE_4": "D" , "SUM A": -28 , "SUM B": 8 },
{ "EBENE_1": "A", "EBENE_2": "C", "EBENE_3": "E", "EBENE_4": "E" , "SUM A": -39 , "SUM B": 55 },
{ "EBENE_1": "A", "EBENE_2": "C", "EBENE_3": "E", "EBENE_4": "F" , "SUM A": -78 , "SUM B": 78 },
{ "EBENE_1": "A", "EBENE_2": "C", "EBENE_3": "F", "EBENE_4": "G" , "SUM A": -99 , "SUM B": 88 },
{ "EBENE_1": "A", "EBENE_2": "C", "EBENE_3": "F", "EBENE_4": "H" , "SUM A": -85 , "SUM B": 67 }
]
},
"facet": {
"row": {
"field": "EBENE_1",
"header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
"type": "nominal"
}
},
"spec": {
"hconcat": [
{
"height": 10,
"mark": {"type": "bar"},
"encoding": {
"x": {"field": "SUM A", "aggregate": "sum"},
"y": {"field": "EBENE_1", "title": null}}
},
{
"facet": {
"row": {
"field": "EBENE_2",
"header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
"type": "nominal"
}
},
"spec": {
"hconcat": [
{
"height": 10,
"mark": {"type": "bar"},
"encoding": {
"x": {"field": "SUM A", "aggregate": "sum"},
"y": {"field": "EBENE_2", "title": null}}
},
{
"facet": {
"row": {
"field": "EBENE_3",
"header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
"type": "nominal"
}
},
"spec": {
"hconcat": [
{
"height": 10,
"mark": {
"type": "bar"
},
"encoding": {
"x": {"field": "SUM A", "aggregate": "sum"},
"y": {"field": "EBENE_3", "title": null}}
},
{
"facet": {
"row": {
"field": "EBENE_4",
"header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
"type": "nominal"
}
},
"spec": {
"hconcat": [
{
"height": 10,
"mark": {
"type": "bar"
},
"encoding": {
"x": {"field": "SUM A", "aggregate": "sum"},
"y": {"field": "EBENE_4", "title": null}}
}
]
}
}
]
}
}
]
}
}
]
}
}
}

英文:

I would like to calculate the sum of "SUM A" to set this as minimum scale
value for all "X" axis.
And the sum of "SUM B" to set als maximum scale for all "X" axis.

I can´t get my head around how to calculate the sum of the rows and use them in the "domain".

Hope the edit and example makes it more understandable.

I'm sorry if my first attempt to phrase the question wasn't clear.

{
  "data": { "values":[
{"EBENE_1":	"A",	"EBENE_2":	"A",	"EBENE_3":	"A",	"EBENE_4":	"A"	,	"SUM A":	-70	,	"SUM B":	41	},
{"EBENE_1":	"A",	"EBENE_2":	"A",	"EBENE_3":	"B",	"EBENE_4":	"B"	,	"SUM A":	-22	,	"SUM B":	81	},
{"EBENE_1":	"A",	"EBENE_2":	"B",	"EBENE_3":	"C",	"EBENE_4":	"C"	,	"SUM A":	-31	,	"SUM B":	89	},
{"EBENE_1":	"A",	"EBENE_2":	"B",	"EBENE_3":	"D",	"EBENE_4":	"D"	,	"SUM A":	-28	,	"SUM B":	8	},
{"EBENE_1":	"A",	"EBENE_2":	"C",	"EBENE_3":	"E",	"EBENE_4":	"E"	,	"SUM A":	-39	,	"SUM B":	55	},
{"EBENE_1":	"A",	"EBENE_2":	"C",	"EBENE_3":	"E",	"EBENE_4":	"F"	,	"SUM A":	-78	,	"SUM B":	78	},
{"EBENE_1":	"A",	"EBENE_2":	"C",	"EBENE_3":	"F",	"EBENE_4":	"G"	,	"SUM A":	-99	,	"SUM B":	88	},
{"EBENE_1":	"A",	"EBENE_2":	"C",	"EBENE_3":	"F",	"EBENE_4":	"H"	,	"SUM A":	-85	,	"SUM B":	67	}
]
},
"facet": {
    "row": {
      "field": "EBENE_1",
      "header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
      "type": "nominal"
    }
  },
  "spec": {
    "hconcat": [
      {
        "height": 10,
        "mark": {"type": "bar"},
   "encoding": {
    "x": {"field": "SUM A", "aggregate": "sum"},
    "y": {"field": "EBENE_1", "title": null}}
      },
      {
        "facet": {
          "row": {
            "field": "EBENE_2",
      "header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
            "type": "nominal"
          }
        },
        "spec": {
          "hconcat": [
            {
              "height": 10,
              "mark": {"type": "bar"},
   "encoding": {
    "x": {"field": "SUM A", "aggregate": "sum"},
    "y": {"field": "EBENE_2", "title": null}}
            },
            {
              "facet": {
                "row": {
                  "field": "EBENE_3",
      "header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
                  "type": "nominal"
                }
              },
              "spec": {
                "hconcat": [
                  {
                    "height": 10,
                    "mark": {
                      "type": "bar"
                    },
   "encoding": {
    "x": {"field": "SUM A", "aggregate": "sum"},
    "y": {"field": "EBENE_3", "title": null}}
                  },
                  {
                    "facet": {
                      "row": {
                        "field": "EBENE_4",
      "header": {"labelAngle": 0, "title": null, "labelAnchor": "start", "labelLimit": 50},
                        "type": "nominal"
                      }
                    },
                    "spec": {
                      "hconcat": [
                        {
                          "height": 10,
                          "mark": {
                            "type": "bar"
                          },
   "encoding": {
    "x": {"field": "SUM A", "aggregate": "sum"},
    "y": {"field": "EBENE_4", "title": null}}
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

答案1

得分: 1

I struggle with these too. Here's my view of the problem and my workaround.

Scale domains want hard-coded values. To my knowledge, the only way to make a scale domain dynamic is to use signals. Vega-lite doesn't have many ways to go from datum to signal. Params are the only way I know. They are funky to use.

Step 1: Get your scale max and min in a column
We need a max and min. If you have it in your data, that will work. Otherwise, you need to calculate them with a transform. I use a joinaggregate transform so that it adds a column to the data. In your data, you are using sum. In most cases, this will be "op": "max" and "op": "min".

  "transform": [
    {"joinaggregate": [{"op": "sum", "field": "SUM_A", "as": "SumA"}]},
    {"joinaggregate": [{"op": "sum", "field": "SUM_B", "as": "SumB"}]}
  ],

Step 2: Convert datum to signal
To get the data values into signals, use params. The hacky notation here uses data_0 (which should be the dataset after the transforms), the first row [0], and the columns we just added [SumA] and [SumB].

  "params": [
    {
      "name": "lowerLimit",
      "expr": "data('data_0')[0]['SumA']"
    },
    {
      "name": "upperLimit",
      "expr": "data('data_0')[0]['SumB']"
    }

  ],

Check the signal viewer to make sure these values are as expected.
将一个字段的总和设置为另一个字段的总和如何操作？

Step 3: Add signal values to scale domain
Reference the signal values in an expression object {"expr": "lowerLimit"}. Note that you just use the signal name.

          "x": {
            "field": "SUM_A", 
            "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }
          },

Result:
将一个字段的总和设置为另一个字段的总和如何操作？

Here's the full spec:
Open the Chart in the Vega Editor

英文:

I struggle with these too. Here's my view of the problem and my workaround.

Scale domains want hard-coded values. To my knowledge, the only way to make a scale domain dynamic is to use signals. Vega-lite doesn't have many ways to go from datum to signal. Params are the only way I know. They are funky to use.

Step 1: Get your scale max and min in a column
We need a max and min. If you have it in your data, that will work. Otherwise, you need to calculate them with a transform. I use a joinaggregate transform so that it adds a column to the data. In your data, you are using sum. In most cases, this will be "op": "max" and "op": "min".

  "transform": [
    {"joinaggregate": [{"op": "sum", "field": "SUM_A", "as": "SumA"}]},
    {"joinaggregate": [{"op": "sum", "field": "SUM_B", "as": "SumB"}]}
  ],

Step 2: Convert datum to signal
To get the data values into signals, use params. The hacky notation here uses data_0 (which should be the dataset after the transforms), the first row [0], and the columns we just added [SumA] and [SumB].

  "params": [
    {
      "name": "lowerLimit",
      "expr": "data('data_0')[0]['SumA']"
    },
    {
      "name": "upperLimit",
      "expr": "data('data_0')[0]['SumB']"
    }

  ],

Check the signal viewer to make sure these values are as expected.
将一个字段的总和设置为另一个字段的总和如何操作？

Step 3: Add signal values to scale domain
Reference the signal values in an expression object {"expr": "lowerLimit"}. Note that you just use the signal name.

          "x": {
            "field": "SUM_A", 
            "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }
          },

Result:
将一个字段的总和设置为另一个字段的总和如何操作？

Here's the full spec:
Open the Chart in the Vega Editor

{
  "data": {
    "values": [
      {
        "EBENE_1": "A",
        "EBENE_2": "A",
        "EBENE_3": "A",
        "EBENE_4": "A",
        "SUM_A": -70,
        "SUM_B": 41
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "A",
        "EBENE_3": "B",
        "EBENE_4": "B",
        "SUM_A": -22,
        "SUM_B": 81
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "B",
        "EBENE_3": "C",
        "EBENE_4": "C",
        "SUM_A": -31,
        "SUM_B": 89
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "B",
        "EBENE_3": "D",
        "EBENE_4": "D",
        "SUM_A": -28,
        "SUM_B": 8
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "C",
        "EBENE_3": "E",
        "EBENE_4": "E",
        "SUM_A": -39,
        "SUM_B": 55
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "C",
        "EBENE_3": "E",
        "EBENE_4": "F",
        "SUM_A": -78,
        "SUM_B": 78
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "C",
        "EBENE_3": "F",
        "EBENE_4": "G",
        "SUM_A": -99,
        "SUM_B": 88
      },
      {
        "EBENE_1": "A",
        "EBENE_2": "C",
        "EBENE_3": "F",
        "EBENE_4": "H",
        "SUM_A": -85,
        "SUM_B": 67
      }
    ]
  },
  "transform": [
    {"joinaggregate": [{"op": "sum", "field": "SUM_A", "as": "SumA"}]},
    {"joinaggregate": [{"op": "sum", "field": "SUM_B", "as": "SumB"}]}
  ],
  "params": [
    {
      "name": "lowerLimit",
      "expr": "data('data_0')[0]['SumA']"
    },
    {
      "name": "upperLimit",
      "expr": "data('data_0')[0]['SumB']"
    }

  ],
  "facet": {
    "row": {
      "field": "EBENE_1",
      "header": {
        "labelAngle": 0,
        "title": null,
        "labelAnchor": "start",
        "labelLimit": 50
      },
      "type": "nominal"
    }
  },
  "spec": {
    "hconcat": [
      {
        "height": 10,
        "mark": {"type": "bar"},
        "encoding": {
          "x": {
            "field": "SUM_A", 
            "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }
          },
          "y": {"field": "EBENE_1", "title": null}
        }
      },
      {
        "facet": {
          "row": {
            "field": "EBENE_2",
            "header": {
              "labelAngle": 0,
              "title": null,
              "labelAnchor": "start",
              "labelLimit": 50
            },
            "type": "nominal"
          }
        },
        "spec": {
          "hconcat": [
            {
              "height": 10,
              "mark": {"type": "bar"},
              "encoding": {
                "x": {"field": "SUM_A", "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }},
                "y": {"field": "EBENE_2", "title": null}
              }
            },
            {
              "facet": {
                "row": {
                  "field": "EBENE_3",
                  "header": {
                    "labelAngle": 0,
                    "title": null,
                    "labelAnchor": "start",
                    "labelLimit": 50
                  },
                  "type": "nominal"
                }
              },
              "spec": {
                "hconcat": [
                  {
                    "height": 10,
                    "mark": {"type": "bar"},
                    "encoding": {
                      "x": {"field": "SUM_A", "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }},
                      "y": {"field": "EBENE_3", "title": null}
                    }
                  },
                  {
                    "facet": {
                      "row": {
                        "field": "EBENE_4",
                        "header": {
                          "labelAngle": 0,
                          "title": null,
                          "labelAnchor": "start",
                          "labelLimit": 50
                        },
                        "type": "nominal"
                      }
                    },
                    "spec": {
                      "hconcat": [
                        {
                          "height": 10,
                          "mark": {"type": "bar"},
                          "encoding": {
                            "x": {"field": "SUM_A", "aggregate": "sum", 
            "scale": {
              "domain": [{"expr": "lowerLimit"},{"expr": "upperLimit"}]
            }},
                            "y": {"field": "EBENE_4", "title": null}
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

答案2

得分: 0

以下是翻译好的部分：

{
  "data": {"name": "dataset"},
  "resolve": {
    "scale": {
      "x": "shared"
    }
  },
  "repeat": {
    "layer": ["STORNO", "EINGANG"],
    "column": [
      "EBENE_1",
      "EBENE_2",
      "EBENE_3",
      "EBENE_4"
    ]
  },
  "spec": {
    "height": 400,
    "width": 250,
    "mark": {"type": "bar"},
    "encoding": {
      "x": {
        "field": {"repeat": "layer"},
        "aggregate": "sum"
      },
      "y": {
        "field": {"repeat": "column"}
      }
    }
  }
}
英文:

Another solution to my problem was suggested by the creator of Deneb, as there are at the moment problems using data expressions and repeat/concate/facet:

{
"data": {"name": "dataset"},
"resolve": {
"scale": {
"x": "shared"
}
},
"repeat": {
"layer": ["STORNO", "EINGANG"],
"column": [
"EBENE_1",
"EBENE_2",
"EBENE_3",
"EBENE_4"
]
},
"spec": {
"height": 400,
"width": 250,
"mark": {"type": "bar"},
"encoding": {
"x": {
"field": {"repeat": "layer"},
"aggregate": "sum"
},
"y": {
"field": {"repeat": "column"}
}
}
}
}

huangapple
  • 本文由 发表于 2023年3月4日 00:52:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/75629828.html
  • deneb
  • powerbi
  • vega-lite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定