Canvas calculate % change day on day on the same metric

[samgs (Sam)]

Hello,

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?

[flash1293 (Joe Reuter)]

Hi @samgs,

that's an interesting question.

It should by possible by doing something like this:

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

[samgs (Sam)]

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

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

[flash1293 (Joe Reuter)]

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