# Aggregation bucket sum + max group by time with filtering
**URL:** https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718
**Category:** Kibana
**Created:** [February 18, 2021, 2:38pm UTC](https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718 "2021-02-18T14:38:13Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Tim4](https://discuss.elastic.co/u/Tim4)
#### Post date: [February 18, 2021, 2:38pm UTC](https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718/1 "2021-02-18T14:38:13Z")
Hi, I'm in need of some help.
Let's start of with some sample data:
Datetime \_\_\_\_\_\_\_\_\_\_\_\_| User | Mbps
18/02/2021 12:00:00 | a \_\_\_ | 10
18/02/2021 12:00:00 | b \_\_\_ | 5
18/02/2021 12:20:00 | a \_\_\_ | 10
18/02/2021 12:45:00 | b \_\_\_ | 5
18/02/2021 12:45:00 | a \_\_\_ | 5
So every doc is one datetime, user and Mbps
What i want to do is group the data by datetime and get the sum of the Mbps and users.
So far so simple. Now i want to group the datetime **per hour** and show the max Mbps and max users value.
* * *
Datetime \_\_\_\_\_\_\_\_\_\_\_\_| User | Mbps | MAX Mbps
18/02/2021 12:00:00 | a \_\_\_ | 10 \_\_\_ |
18/02/2021 12:00:00 | b \_\_\_ | 5 \_\_\_\_ | 15
* * *
18/02/2021 12:20:00 | a \_\_\_ | 10 \_\_\_ | 10
* * *
18/02/2021 12:45:00 | b \_\_\_ | 5 \_\_\_\_ |
18/02/2021 12:45:00 | a \_\_\_ | 5 \_\_\_\_ | 10
* * *
Datetime \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_| User Max | Mbps Max
18/02/2021 12:00:00 - 13:00:00| 2 (a+b) \_\_\_| 15 (10 + 5) - This would be one point in the line graph.
I want to show a line graph in kibana showing the result per hour.
So that's my use case.
How can this be done?
And how can this be done with the ability to filter on user. let's say i want the result only for user a.?
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [February 18, 2021, 5:30pm UTC](https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718/2 "2021-02-18T17:30:16Z")
These sorts of complicated use cases don't have a great solution using aggregations themselves, but really can be solved better using "entity-centric indexing". Here's a video that describes the concept:
> **[Entity-Centric Indexing - Mark Harwood](https://www.elastic.co/videos/entity-centric-indexing-mark-harwood)**
The idea here is that every hour we'd have a script that, every hour, looks at the last hour of documents, performs the calculations as you've described, then indexes a new document that looks like the output of your calculations.
---
### Author:  [@Tim4](https://discuss.elastic.co/u/Tim4)
#### Post date: [February 22, 2021, 8:14am UTC](https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718/3 "2021-02-22T08:14:23Z")
Thanks for your reply. Is it possible to use Kibana to create a graph from an external URL.
I can generate a sum of users and Mbps for the time ranges and know i can create the max in like say PHP. The Flow would be.
Elasticsearch → Query for sum per time range → PHP get max for every hour → out put JSON results → show JSON results in graph in Kibana.
Does anyone have experience in doing the last step "show JSON results in graph in Kibana" from an external source?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 22, 2021, 8:14am UTC](https://discuss.elastic.co/t/aggregation-bucket-sum-max-group-by-time-with-filtering/264718/4 "2021-03-22T08:14:27Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.