Change in a field for the selected time period

[baahubali (Paras Lehana)]

We can find the Daily, Weekly, Monthly or Yearly changes for a field using Lens now. Great!

But how do we find Change in a field for the selected time period in the top right?

This is a very basic requirement but I have found very few resources on this and thus have kept the title simpler. I have tried and experimented many things and I think I am very close to it.

Let me get straight to the example:

So this is the data we have:

For example, assume we have these documents with fields code, price and date:

{ code: BTC, price: 30000, date: 23/02/2022 },
{ code: ETH, price: 5000, date: 23/02/2022 },
...
{ code: ETH, price: 2000, date: 24/05/2022 },
{code: BTC, price: 25000, date: 24/05/2022 },
...
{code: BTC, price: 20000, date: 24/06/2022 },
{ code: ETH, price: 1000, date: 24/06/2022 }

So basically, I have different prices for different codes on different dates. Very simple.

Now let's get to the objective:

Calculate the overall price change for the selected period. For example, if I choose the period in top right bar of Kibana to be May 2022 to Today, the change should be the average of difference between last and first prices for each code.

For the sake of simplicity suppose we have data only for 24th day of each month. So, if I choose a period to be Feb 2022 to May 2022:

Change in code BTC: ((btc.price on 24/05/2022) - (btc.price on 24/02/2022))/(btc.price on 24/02/2022) = (25000 - 30000)/30000 = 16.7% loss.

Change in code ETH: ((eth.price on 24/05/2022) - (eth.price on 24/02/2022))/(eth.price on 24/02/2022) = (2000 - 5000)/5000 = 60% loss.

Average of change in BTC, ETH = ((-16.7) + (-60))/2 = 38.4% loss

So, the average change is -38.4% for the selected period Feb 2022 to May 2022.

This is what I could do:

I made a TSVB Metric that basically calculates top hit by descending and ascending order for the selected period and calculates the change.

So yes this would work perfect for a single code (ASX here) but fails for multiple codes. To understand, see the entry for May 2022, ETH comes before BTC entry so the calculation will have the oldest price as BTC price on Feb 2022 while the latest price will be of ETH price on May 2022.

I think I could do some bucketing here so that first I calculate the change (Maths) for each code and then average them but I cannot figure out how to do that.

Please help with any direction and I will try the modifications and reply here only after trying everything.

[baahubali (Paras Lehana)]

Any one? I thought it was a basic requirement. I hope I explained it fine. I am trying stuff and I think we only need some correct bucketting here.

[LeeDr (Lee Drengenberg)]

Hi Paras,

I think you might need to try something like this which is supported in TSVB; Serial differencing aggregation | Elasticsearch Guide [8.3] | Elastic

And then in your Group by section, select Terms and use the field for your code.

Regards,
Lee

[baahubali (Paras Lehana)]

Hi Lee,

Thank you so much for replying, as I had tried multiple forums and even paid support but have not gotten the solution yet.

I went through Serial differencing. The Group By Terms will show the calculation for each bucket but I need the averages of these group by actually.

What I essentially need is the average of change % for each code. That is a single value.

Have a look at my TSVB configuration in OP. If I apply Group By on it, I get this:

This is almost what I want. I just want to calculate the averages of these values.

So, we have found the Price Change % of each code with my calculation followed by Group By. The next and final step is just to calculate average of this price change % for each code!