Display average hourly rates per term

I have a field in my index called 'class', which can have many different string values. With Kibana I can easily plot a line chart showing how the count of 'class' as a time series, and I can also show the top 10 values of 'class' on a line series by using the 'split series' feature.

However, what I want is a list or table or bar chart, showing the average hourly rate of each value of 'class' (top 10 is fine) across the selected timescale (in fact, any rate is fine).

I thought the Top N visualisation in TSVB would do this if I set the panel time interval to be 1hr, but to be honest that was a guess (the documentation for TSVB is quite limited) and I am not at all sure what I am getting, as the results vary a lot when I change the timescale from 24h to 4h to 1hr, even though I know the rates are fairly constant.

a) What does that TSVB Top N panel time interval actually do?
b) Is it possible to achieve my aim, preferably with TSVB (as its a nicer visual experience), but any other visualisation type might do?


Hi @astickler,

thanks for the interesting question.

By using the pipeline aggregations "cumulative sum" and "derivative" you can specify the unit of time a metric is referring to.

The follow config should calculate the hourly rate of documents, splitted by a field (in this case category.keyword) and plotted over time:


Hi Joe,
Thanks for the great answer. However, Cumulative Sum is not available to me for some reason. In fact, all the pipeline aggregations are greyed out and can't be selected in the dropdown. Why would that be?

I am using Kibana V6.7.0 with Elasticsearch V6.7, both AWS variants.


This is a bit unintuitive. You have to create the count aggregation first, then add a new aggregation with the plus button - in this second aggregation the cumulative sum will be available. Then create the derivative of the cumulative sum as a last step. I just noticed I made it extra misleading by reordering them in my screenshot, sorry for that.

Ah I see, that works, however I am getting some odd results.

If I set the time period of the whole visualisation to 'Last 1 hour', the top item in my results has a value of 240 (i.e. a rate of 240/hr for the last hour), but if I run a standard query to return the number of documents of that item type in the 'Last 1 Hour', I get 143.

Similarly, if I set the time range to 'Last 24 hours', the visualisation gives an average value of 138, but the document count is 2825, which works out at a rate of 118/hr.

In my Panel Options, Interval is set to auto - is this relevant?


You already identified the cause of this - the interval matters here because it is the time range you get the average ingest rate for.

In Elasticsearch terms: The last bucket of a date_histogram aggregation on the configured time field in the time range configured in the date picker.

Auto tries to be smart about the interval depending on your time range, if you just look at the last hour it's most likely just around 30 seconds, so you will only get the ingest rate for these 30s. By specifying a bigger interval, you get a more reliable number here.

In more recent versions of Kibana you are able to make it the average of the entire time range selected in the time picker in the data panel too remove this source of confusion:

OK for the 'last 24 hours', if I set interval to 19h, I get a result of 116.737, which is close to the 118 from the search, but if I use 20h or higher, the value is displayed as 0.

Anyhow, as I plan to change the time range of the visualisation often, it appears I cannot get a working solution to this problem until I have access to he 'Entire timerange' feature. Do you agree?

In what version(s) was that feature intoduced, and do you have any insight into when Amazon might move to that version (I realise it is prpobably entirely their decision).


Actually, I see Amazon now have support for 6.8 and 7.1, but don't appear to have provided an upgrade path from 6.7.

I tell a lie - i can upgrade to 6.8, so will try that and report back.

6.8 doesn't have that option :frowning:

It's available starting version 7.3

The problem you are running into is that the interval is bigger than your time range, resulting in the last bucket being dropped and leaving you without any results. You can change this behavior in "Panel options" by setting "Drop last bucket" to false. However be aware that this will always show you the data for the configured interval, so if you have an interval of 1d configured and set the time range to 15 minutes, you are still seeing the average rate for the last day.

Well I am not sure - the 20h interval is not bigger than the 24h time range, and in any case, the bars still have varying lengths, as though there is a rounding issue somewhere. BTW, today I have to drop interval to 16h to get a non-zero value, no idea why 19 doesn't work any more.

Anyhow, I think we are done on this issue and I will wait for Amazon to introduce v7.3. Thank you for all your great help.