Canvas calculate % change day on day on the same metric


Im trying to workout if its possible to show % movement day on day, on canvas?

For example, I have a sum of the number of page visits today and I want to compare if thats higher or lower than yesterday.

Is this possible on canvas?

Hi @samgs,

that's an interesting question.

It should by possible by doing something like this:

| essql 
  query="SELECT DAY_OF_MONTH(timestamp) as day, COUNT(*) as count FROM \"kibana_sample_data_logs\" WHERE timestamp <= CURRENT_DATE() AND timestamp > CURRENT_DATE() - INTERVAL '2' DAY GROUP BY DAY_OF_MONTH(timestamp)"
| math "(last(count) - first(count))  / first(count)"
| render

The SQL query here returns a table like this:

day | count
 6  | 400
 7  | 200

The math function takes the first count and calculates the difference in percent to the second count which is current day and previous day.

If you want to be even more flexible (but with more complicated syntax), you can also use a Vega visualization - this blog article also explains how to build a trend indicator.

1 Like

Thanks for the code @flash1293, I'll give that a go.

Do you know how to use vega on canvas? Is that even possible?

You can't use vega on canvas currently, it's just a regular visualization on the dashboard in that case.

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