# Sum operator in stacked bar vega

**URL:** <https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722>\
**Category:** Kibana\
**Created:** [February 9, 2021, 11:49am UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722 "2021-02-09T11:49:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pranusha119](https://avatars.discourse-cdn.com/v4/letter/p/47e85d/32.png) [@Pranusha119](https://discuss.elastic.co/u/Pranusha119)\
**Post date:** [February 9, 2021, 11:49am UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/1 "2021-02-09T11:49:08Z")

</div>

Hi,

I have aggregated StatusOpen field using sum operator, grouped by text\_sentiment and subject\_line fields.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/b/2bf5883233bacff1c8bbc09d820311ffd340a5ec.png)

It's giving correct results in kibana.

I tried it in vega using below spec :

```auto
{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "width": 400,
      data: {
    url: {
      %context%: true
      index: index_dash_perf
      body: {
        size:500000
        _source: ["text_sentiment", "StatusOpen", "StatusSent", "StatusCode", "subject_line", "duplicates"]
        }
      }
      
      format: {property: "hits.hits"}
    },
  "transform": [
    {"filter": "datum._source.text_sentiment != null"},
      {"filter": "datum._source.duplicates != 0"},
      {
        "aggregate": [
        {"op": "sum", "field": "_source.StatusOpen","as": "aud_count"}
        ],
              "groupby": ["_source.subject_line","_source.text_sentiment"]
      }
    ],
  "encoding": {
    "x": {"field": "_source.subject_line", "type": "nominal", "title": null},
    "y": {"field": "aud_count", "type": "quantitative", "title": null},
    "color": {
        "field": "_source.text_sentiment",
        "type": "nominal",
        "title": "Sentiment",
        "scale":{"scheme": "lighttealblue"}
      }

  },
  "layer": [
  {
    "mark": "bar"
  }, {
    "mark": {
      "type": "text",
      "align": "left",
      "dx": -6,
      "dy": -5
    },
    "encoding": {
      "text": {"field": "aud_count", "type": "quantitative"}
    }
  }]
} 

```

Output :

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/9/593b72337f58b491a69841cd0165d869fd313b27.png)

for count operator its giving correct results, sum operator is just giving 0.

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [February 9, 2021, 3:37pm UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/2 "2021-02-09T15:37:11Z")

</div>

I have some questions:

1. If it's giving you correct results in Kibana, then why are you reproducing it in Vega?
2. Are you aware that the way you're trying to reproduce it is _not the same_ as what Kibana is using? From your Kibana bar chart, look at `Inspect -> Request` and you will see what the Elasticsearch request is showing

I wrote an example of using aggregated queries in the [Vega docs](https://www.elastic.co/guide/en/kibana/master/vega.html), I think you can directly use those examples.

---

<div class="post-metadata">

**Author:** ![Pranusha119](https://avatars.discourse-cdn.com/v4/letter/p/47e85d/32.png) [@Pranusha119](https://discuss.elastic.co/u/Pranusha119)\
**Post date:** [February 10, 2021, 4:28am UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/3 "2021-02-10T04:28:38Z")

</div>

Hi @wylie

1. The main visualization I require is % graph by dividing sum of StatusOpen/ sum of StatusSent, which was not giving expected output in vega-lite. So just wanted to check ouput for simple graph ( with sum of single field).

2. Tried the same spec using sample data, it was giving correct results

```auto
{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "description": "Vega-Lite version of bar chart from https://observablehq.com/@d3/learn-d3-scales.",
  "width": 400,
  "data": {
    "values": [
      {"name": ":tangerine:", "count": 20, "gcount": "A", "hcount":21},
      {"name": ":tangerine:", "count": 21, "gcount": "A", "hcount": 8},
       {"name": ":tangerine:", "count": 8, "gcount": "B", "hcount": 8},
          {"name": ":tangerine:", "count": 11, "gcount": "B", "hcount": 8},
      {"name": ":banana:", "count": 10, "gcount": "C", "hcount": 5},
      {"name": ":grapes:", "count": 11, "gcount": "D", "hcount": 5},
      {"name": ":grapes:", "count": 10, "gcount": "E", "hcount": 20},
      {"name": ":watermelon:", "count": 11, "gcount": "F", "hcount": 21}
    ]
  },
  "transform": [
    
      {
        "aggregate": [{"op": "sum", "field": "count","as": "aud_count"}],
              "groupby": ["name","gcount"]
      },
        
    ],
  "encoding": {
    "x": {"field": "name", "type": "nominal", "title": null},
    "y": {"field": "aud_count", "type": "quantitative", "title": null},
    "color": {
        "field": "gcount",
        "type": "nominal",
        "title": "Sentiment",
        "scale":{"scheme": "paired"}
      }

  },
  "layer": [
  {
    "mark": "bar"
  }, {
    "mark": {
      "type": "text",
      "align": "left",
      "dx": -6,
      "dy": -5
    },
    "encoding": {
      "text": {"field": "aud_count", "type": "quantitative"}
    }
  }]
} 

```

Output :

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/8/581e4844017a4210dcddfcd0a465415471370b6f.png)

---

<div class="post-metadata">

**Author:** ![Pranusha119](https://avatars.discourse-cdn.com/v4/letter/p/47e85d/32.png) [@Pranusha119](https://discuss.elastic.co/u/Pranusha119)\
**Post date:** [February 10, 2021, 6:27am UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/4 "2021-02-10T06:27:31Z")

</div>

If joinaggregate is used in place of aggregate in the main spec, it's giving correct results but Y axis is going upto 9000000. It's weird that bar length is coming right for Y axis values where as Y-axis labels showing way higher values.

```auto
{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "width": 400,
      data: {
    url: {
      %context%: true
      index: index_dash_perf
      body: {
        size:20000
        _source: ["text_sentiment", "StatusOpen", "StatusSent", "StatusCode", "subject_line", "duplicates"]
        }
      }
      
      format: {property: "hits.hits"}
    },
  "transform": [
    {"filter": "datum._source.text_sentiment != null"},
      {"filter": "datum._source.duplicates != 0"},
      {
        "joinaggregate": [
        {"op": "sum", "field": "_source.StatusOpen","as": "aud_count"}
        ],
              "groupby": ["_source.subject_line","_source.text_sentiment"]
      }
    ],
  "encoding": {
    "x": {"field": "_source.subject_line", "type": "nominal", "title": null},
    "y": {"field": "aud_count", "type": "quantitative", "title": null},
     "color": {
        "field": "_source.text_sentiment",
        "type": "nominal",
        "title": "Sentiment",
        "scale":{"scheme": "blues"}
      }

  },
  "layer": [
  {
    "mark": "bar"
  }, {
    "mark": {
      "type": "text",
      "align": "left",
      "dx": -6,
      "dy": -5
    },
    "encoding": {
      "text": {"field": "aud_count", "type": "quantitative"}
    }
  }]
} 

```

Output :

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/2/828661e242f1208f0d55be44d0c32c375f41a66e.png)

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [February 10, 2021, 3:26pm UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/5 "2021-02-10T15:26:17Z")

</div>

You still aren't running the same query as Visualize:

> Are you aware that the way you're trying to reproduce it is _not the same_ as what Kibana is using? From your Kibana bar chart, look at `Inspect -> Request` and you will see what the Elasticsearch request is showing

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 10, 2021, 3:27pm UTC](https://discuss.elastic.co/t/sum-operator-in-stacked-bar-vega/263722/6 "2021-03-10T15:27:11Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.