Canvas: Cumulative sum of counts

Elastic Stack Kibana
1

Kibana novice here...

I'm using ElasticseachSQL queries to populate elements in Canvas.

I'd like to find a way of creating a Line chart to display a DAILY cumulative count of row entries for a given month.

To get the entries i do the following:

SELECT COUNT(*) AS entries, DAY_OF_MONTH(entry_date) AS day FROM data_table WHERE MONTH_OF_YEAR(entry_date) = MONTH_OF_YEAR(NOW()) AND YEAR(entry_date) = YEAR(NOW()) GROUP BY day ORDER BY day

This gives me this lovely table:

day | entries
------+------------
1 |464
2 |180
3 |179
4 |378

Now i want another column where it keeps track of the cumulative count:

day | entries | cumulative count
------+-------------+--------------------------
1 |464 | 464
2 |180 | 644
3 |179 | 823
4 |378 | 1,201

how do i do this?

Most answers elsewhere mention windowing, but this doesn't seem supported in ESSQL.

Here's hoping...

2

@superfly86

not in Canvas, but in the regular Visualizations of Kibana you can do this with the cumulative sum pipeline aggregation

image
image.png2267×783 87.2 KB

you could create a regular Dashboard iso a Canvas workpad with this table.

3

@thomasneirynck

not in Canvas, but in the regular Visualizations of Kibana you can do this with the cumulative sum pipeline aggregation

Yes, exactly, i have been able to produce the exact chart i need in Visualisations, however i'd like it part of a Canvas alongside other metrics (for display purposes)

you could create a regular Dashboard iso a Canvas workpad with this table.

Could you clarify - can i create a visualisation and copy it across to Canvas?

4

no, you cannot add visualizations from the Kibana Visualize app to a Canvas workpad now.

I meant that perhaps a dashboard with that visualization would work for your purposes as well. Although I do understand that Canvas has more styling options for look&feel.