Latest value of a metric within a time range filter


(Sanjiv Jivan) #1

Hi,
Consider a usecase where I have daily stock price data and would like to have a dashboard display the average price of the stock over the kibana filter range, as well as the current stock price (price at the end date of the selected range).

I see there's an open issue for latest value https://github.com/elastic/kibana/issues/6877 but I was wondering if there's any workaround to having such a dashboard display both metrics.

This is an important part of the our requirements.

Thanks,
Sanjiv


(Joe Fleming) #2

Not currently as a metric.

You could use a saved search, with the field with the value selected, sorted by time descending. Then you'd see the latest values directly.


(Sanjiv Jivan) #3

I didn't completely follow. Did you mean after the saved query I should create a table viz and select 1 row?

Thanks,
Sanjiv


(Joe Fleming) #4

No, sorry for not being clearer.

In Discover, if you simply add the field from the sidebar on the left, it'll show up in the table there, sorting the data in a descending order. So, if you add the price field, you'll get a list of the prices in descending order, so the "latest" value will be at the top.

Save this search in Discover, and then you can add it as a Saved Search on a dashboard. It's not the best experience, but it would show you the information you want.


(Sanjiv Jivan) #5

ah, I see. Yeah, not the best user experience as I'd like to only show the latest value in the table, not the latest / current value at the top.

I've managed to work around it by a brute force approach because as I evaluated my requirements, Kibana was just not going to allow me to display the information I needed in the format required.

For example I need a single table to display the current day Profit & Loss, the Month to Date P&L and Year to Date P&L. While all these pieces of information could be obtained by selecting the Kibana picker at "Today", "This Month" and "This Year", each of these time filters would aggregate the numbers based on the time range, it simply isn't possible to have all three numbers in a table. Further I needed this breakdown of current. MTD and YTD in a table with other groupings like by department.

The issue about kibana supporting separate time filters per visualization would not have helped with such a scenario without making the dashboard kludgy with three separate tables.

Presentation of such data (current, MTD and YTD) is pretty straightforward by traditional reporting or BI tools. I ended up adding MTD and YTD attributes on the document definition and maintain a running total of these numbers at the time of insertion / update into ElasticSearch. It's not the cleanest solution but it works and allows me to deliver on the requirements.


(Sanjiv Jivan) #6

Would the ingest API / scripted fields of Kibana 5 allow me to create scripted fields that did a sum of MTD and YTD? It's for a given document date, sum a given field value starting from the start of month relative to the current document to the current document date?

Thanks,
Sanjiv


(system) #7

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