Visualize difference in growth between two time series

[snicoll (Stéphane Nicoll)]

Hello,

I have an index where each document in the index provides the number of open items and the number of closed items. I'd like to build a visualization that shows if the team is catching up closing issues or, if at times, there are more open issues added than closed. The end result I'd like is two lines where the color changes depending on that factor (more closed issues than created, green, the reverse, red).

Here is an example of data:

timpestamp: epoch_mill1, total: 2000, open: 200, closed: 1800
timpestamp: epoch_mill2, total: 2200, open: 250, closed: 1950
timpestamp: epoch_mill3, total: 2300, open: 150, closed: 2150
timpestamp: epoch_mill4, total: 2500, open: 200, closed: 2300

Thanks!

[wylie (Wylie Conlon)]

You're asking for a slightly more powerful visualization, so you might need to use Vega to get this level of power. Specifically the conditional coloring is not supported unless you use Vega.

However, I think you can answer most of your question using any of the visualizations in Kibana. You won't get conditional coloring though. To do this, you need to translate your question into aggregations. It sounds like you are storing the count of issues, and you are looking for change in count, which is a "differences" function in Lens or a "derivative" function in other parts of Kibana. You also need to choose the metric that you are taking the differences of: you can use Sum or Last Value for your example.

[snicoll (Stéphane Nicoll)]

Thank you, derivative is what I ended up doing. I've used two layers with open issues and closed issues and the two gives me an idea of how the team performs. I am not 100% sure that I've got what I want.

You also need to choose the metric that you are taking the differences of: you can use Sum or Last Value for your example.

I didn't get that. Can you please elaborate?

[wylie (Wylie Conlon)]

@snicoll If you're already configured your Derivative function then you have seen that the Derivative function requires a sub-aggregation. I was recommending Sum or Last Value as the sub-aggregation to choose.

[snicoll (Stéphane Nicoll)]

I used Max and then derivative on max of closed (same for open). I don't understand how sum could be applicable to my case since each document stores that total of open (and closed) issues. Not the number of open/closed issues for that particular timestamp.

What am I missing?

[wylie (Wylie Conlon)]

The main factor is the time interval. You can easily get multiple documents in the same date bucket, and using the Max function could give you incorrect results. Using Last Value (which is called Top Hits in Elasticsearch) will always guarantee that you are comparing a single document at a time. Using Sum will guarantee that you've considered all documents in the time range. In your case, it sounds like Last Value/Top Hits is the correct choice because you can guarantee only one document per metric.

[snicoll (Stéphane Nicoll)]

I've considered using Top Hits but using that + a derivative leads to the following:

[wylie (Wylie Conlon)]

Got it, it does appear that TSVB does not support it. It was a bug that you are allowed to configure derivative of a top hit.

Lens does support Differences of Last Value in Kibana 7.12, because we calculate the differences without requiring Elasticsearch.

[snicoll (Stéphane Nicoll)]

Thank you very much. I was using 7.10 and didn't find the options in lens. I've updated to 7.12 locally and I have something that seems to do what I want.

It would be perfect if we could change the color of the area if the value if positive or negative (i.e. have a color for positive number and another one for negative numbers).

[wylie (Wylie Conlon)]

I agree that it would be good to have conditional coloring at that level, but that's a long-term goal for Lens, not short term. We will support conditional coloring of tables in Lens soon, it's currently being developed.