Canvas: how to make stacked area chart?

[aspyct (Antoine)]

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.

[Catherine_Liu]

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:

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:

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:

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

[aspyct (Antoine)]

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 ?

[Catherine_Liu]

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 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:

{ "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:

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:

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:

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 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.

[aspyct (Antoine)]

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