# Canvas: how to make stacked area chart?

**URL:** <https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [May 9, 2019, 3:38pm UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399 "2019-05-09T15:38:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![aspyct](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aspyct/32/45911_2.png) [@aspyct](https://discuss.elastic.co/u/aspyct)\
**Post date:** [May 9, 2019, 3:38pm UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/1 "2019-05-09T15:38:38Z")

</div>

Hi,

I can't figure out how to create stacked area charts within Canvas. I've noticed the `stack` parameter in the `plot` function, but I don't know how to send multiple series into it.

I'm using `essql` to output a datatable, at the moment. I'd rather keep using that, but may be able to switch to other data sources if necessary.

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 9, 2019, 7:29pm UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/2 "2019-05-09T19:29:24Z")

</div>

To make a stacked chart, you need to pass the `stack` parameter to the `seriesStyle` function used by the `defaultStyle` or `seriesStyle` parameters for plot. The `stack` parameter takes a `number` that serves as the id for the stack and stacks any series given that same id number. If you set `stack` in `defaultStyle`, this stack id will be applied to all of the series in your plot. If you set `stack` in `seriesStyles`, it'll stack any series given the same stack id number.

Here's an example of a line chart using `demodata`:

 ![34%20PM](https://us1.discourse-cdn.com/elastic/original/3X/d/3/d35ad870fbd454227d61c82f6e6eea73146803b6.png)

```auto
filters
| demodata
| mapColumn "time" expression={getCell "time" | formatdate format="MMM YYYY"}
| pointseries x="time" y="mean(price)" color="state"
| plot defaultStyle={seriesStyle lines=1 fill=1 stack=1} 
     palette={palette "#1ea593" "#2b70f7" "#ce0060" "#38007e" "#fca5d3" "#f37020" "#e49e29" "#b0916f" "#7b000b" "#34130c" gradient=false}
| render

```

Here's an example as a bar chart using the same data:

 ![43%20PM](https://us1.discourse-cdn.com/elastic/original/3X/8/a/8a58e86a37742b6c153345398b206f3c88994ac0.png)

```auto
filters
| demodata
| mapColumn "time" expression={getCell "time" | formatdate format="MMM YYYY"}
| pointseries x="time" y="mean(price)" color="state"
| plot defaultStyle={seriesStyle bars=0.5 fill=1 stack=1} 
    palette={palette "#1ea593" "#2b70f7" "#ce0060" "#38007e" "#fca5d3" "#f37020" "#e49e29" "#b0916f" "#7b000b" "#34130c" gradient=false}
| render

```

Here's an example with only the `done` and `running` series stacked:  
 ![44%20PM](https://us1.discourse-cdn.com/elastic/original/3X/9/7/97223070ffa75e61dee162c03a5e2bb7b6c1acb7.png)

```auto
filters
| demodata
| mapColumn "time" expression={getCell "time" | formatdate format="MMM YYYY"}
| pointseries x="time" y="mean(price)" color="state"
| plot defaultStyle={seriesStyle bars=0.5 fill=0.3} 
    seriesStyle={seriesStyle label="done" stack=1 fill=0.3} 
    seriesStyle={seriesStyle label="running" stack=1 fill=0.3}
    palette={palette "#1ea593" "#2b70f7" "#ce0060" "#38007e" "#fca5d3" "#f37020" "#e49e29" "#b0916f" "#7b000b" "#34130c" gradient=false}
| render

```

---

<div class="post-metadata">

**Author:** ![aspyct](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aspyct/32/45911_2.png) [@aspyct](https://discuss.elastic.co/u/aspyct)\
**Post date:** [May 10, 2019, 8:18am UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/3 "2019-05-10T08:18:46Z")

</div>

Thank you, that's a bit more clear.

I'm still unsure how to extract multiple series using ESSQL. Here's my data:

```
{ "timestamp": "2019-05-01 00:00:00", "count_active": 176, "count_inactive": 100 }
{ "timestamp": "2019-05-01 00:00:00", "count_active": 96, "count_inactive": 67 }
{ "timestamp": "2019-05-01 00:00:00", "count_active": 126, "count_inactive": 53 }
...

```

My query so far, for one series, is:

```
SELECT
    (count_active + count_inactive) as count,
    timestamp
FROM my_index

```

How can I split this into two series, one for `count_active` and the other one for `count_inactive` ?

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 31, 2019, 11:38pm UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/4 "2019-05-31T23:38:53Z")

</div>

That is possible but you would need to change the shape of your data. Here's an example of one (slightly hacky) way to achieve this in Canvas without changing your Elasticsearch index.

The `color` arg in [`pointseries`](https://www.elastic.co/guide/en/kibana/current/canvas-server-functions.html#_pointseries) splits a column into multiple series per unique value in a single column. There currently isn't a way to assign a series to a specific column in Canvas. So ideally, your data would be in a shape like this:

```auto
{ "timestamp": "2019-05-01 00:00:00", "count_type": "active", "count": 176 }
{ "timestamp": "2019-05-01 00:00:00", "count_type": "inactive", "count": 100 }

```

and you could set `pointseries color="count_type"`.

I used `csv` to mock your data, and here is what I ended up with:

![45%20PM](https://us1.discourse-cdn.com/elastic/original/3X/6/f/6f67df38bdb65ec0c7ce4eb3fbbe55d59aaaf1b4.png)

```auto
csv 
  "timestamp,count_active,count_inactive 
  2019-05-01 00:00:00, 176, 100
  2019-05-02 00:00:00, 96, 67
  2019-05-03 00:00:00, 126, 53"
| alterColumn count_active type="number"
| alterColumn count_inactive type="number"
| ply 
    by="timestamp" 
    expression={csv 
     data={string 
       "count_type,count
       " "active," {getCell "count_active"} "
       " "inactive," {getCell "count_inactive"} "
       " "total," {math "count_inactive + count_active"}
      } 
    }
| alterColumn "count" type="number"
| alterColumn "timestamp" type="date"
| pointseries x="timestamp" y="count" color="count_type"
| plot 
    defaultStyle={seriesStyle lines=1 fill=1} 
    palette={palette "#01A4A4" "#CC6666" "#D0D102" "#616161" "#00A1CB" "#32742C" "#F18D05" "#113F8C" "#61AE24" "#D70060" gradient=false}
| render

```

And a stacked version:

![39%20PM](https://us1.discourse-cdn.com/elastic/original/3X/7/0/7021427e748381cec7b31328d929dc3894924c6d.png)

```auto
csv 
  "timestamp,count_active,count_inactive 
  2019-05-01 00:00:00, 176, 100
  2019-05-02 00:00:00, 96, 67
  2019-05-03 00:00:00, 126, 53"
| alterColumn count_active type="number"
| alterColumn count_inactive type="number"
| ply 
    by="timestamp" 
    expression={csv 
     data={string 
       "count_type,count
       " "active," {getCell "count_active"} "
       " "inactive," {getCell "count_inactive"} "
       " "total," {math "count_inactive + count_active"}
      } 
    }
| alterColumn "count" type="number"
| alterColumn "timestamp" type="date"
| pointseries x="timestamp" y="count" color="count_type"
| plot 
    defaultStyle={seriesStyle lines=1 stack=1 fill=1} 
    palette={palette "#01A4A4" "#CC6666" "#D0D102" "#616161" "#00A1CB" "#32742C" "#F18D05" "#113F8C" "#61AE24" "#D70060" gradient=false}
| render

```

You should be able to replace the first 3 functions: `csv` and the two `alterColumns` with your datasource to achieve a similar chart.

The important (and hacky) piece is this:

```auto
ply 
    by="timestamp" 
    expression={csv 
     data={string 
       "count_type,count
       " "active," {getCell "count_active"} "
       " "inactive," {getCell "count_inactive"} "
       " "total," {math "count_inactive + count_active"}
      } 
    }

```

which uses [`ply`](https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_ply) to group your `datatable` by the timestamp and maps your `count_active` value to a row with `count_type:"active"` and `count: 176`, your `count_inactive` value to a 2nd row with `count_type:"inactive"` and `count: 100` for each unique `timestamp`, and the sum of `count_active` and `count_inactive` to a 3rd row with `count_type: total` and `count:276`.

---

<div class="post-metadata">

**Author:** ![aspyct](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aspyct/32/45911_2.png) [@aspyct](https://discuss.elastic.co/u/aspyct)\
**Post date:** [June 14, 2019, 8:25am UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/5 "2019-06-14T08:25:50Z")

</div>

Hacky indeed, but does the job until I can change the format of the data. Thanks!

---

<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:** [July 12, 2019, 8:37am UTC](https://discuss.elastic.co/t/canvas-how-to-make-stacked-area-chart/180399/6 "2019-07-12T08:37:51Z")

</div>

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