Bar chart visualization with timestamp delta/difference as the y-axis

[girvandip]

Hi!

I am trying to create a bar chart using the visualize section. Below is the example of the data in tabular format:

| timestamp |  Id  |  category  |
---------------------------------
| 08:49:10  |  1   |     A      |
| 08:50:20  |  1   |     B      |
| 09:00:10  |  1   |     C      |
| 09:00:50  |  2   |     A      |
| 09:01:20  |  2   |     C      |
| 09:20:00  |  3   |     A      |
| 09:20:10  |  3   |     C      |
| 09:20:30  |  3   |     B      |

What I want to achieve is a bar chart where the y-axis is the delta/difference in timestamp (in seconds) between category 'A' and 'C' with the same Id. Below is an example of the bar chart that I want to achieve using Kibana:

Is this at all achievable? I have tried creating a bar chart in Visualize but I can't seem to find the right aggregation to use.

Many thanks!

[wylie (Wylie Conlon)]

You are asking for a complex calculation, which means that you have limited options for how to achieve this:

1. Use Vega to build a fully custom visualization with custom data
2. Create a new index in Elasticsearch which stores the pre-calculated data that you want, for example it would only contain id and delta fields.

[girvandip]

Thank you for your reply @wylie.

I have successfully made the visualization in Vega.

However, I have another use case which I have to use Canvas for the same specification.

Is this at all achievable with Canvas? Again, I have tried it but still unable to do so because it seems that Canvas does not support complex calculations for my use case.

Below is the expression that I used:

filters
| essql 
  query="SELECT DATE_DIFF('seconds', \"@timestamp\", NOW()) AS old, Id FROM \"" {var "IndexName"} "\" where category = 'C'"
| staticColumn name="new" 
  value={filters | essql query={string "SELECT DATE_DIFF('seconds', \"@timestamp\", NOW()) AS new FROM \"" {var "IndexName"} "\" where category = 'A'"}}
| pointseries x="Id" y="old-new"
| plot defaultStyle={seriesStyle bars=3}
| render

I am aware that this is not how to use static column. However, is there any function that allows us to store a table of value that can later be used for calculation purposes?

Many thanks!

[wylie (Wylie Conlon)]

You can use var_set to create a variable, which could be set based on the result of a query. You can use the string function to construct strings from parts, which can be used to generate SQL queries. Also, you can use the ply function to perform math similar to what you'd do in a pivot table.

[girvandip]

Just a quick question on that, can we store the result of a query that produces multiple rows of values (time difference for each Id) in a variable or anything?

[wylie (Wylie Conlon)]

Variables can store any type.