# How to make histogram by grouped aggregation count?

**URL:** <https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709>\
**Category:** Kibana\
**Created:** [March 20, 2018, 8:56am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709 "2018-03-20T08:56:39Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 8:56am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/1 "2018-03-20T08:56:40Z")

</div>

Hello,  
I have some events, which user did.  
/events:  
{"type":"register", "user.id":"1", "event.id": "1"}  
{"type":"register", "user.id":"1", "event.id": "2"}  
{"type":"register", "user.id":"1", "event.id": "3"}  
{"type":"register", "user.id":"2", "event.id": "1"}  
{"type":"register", "user.id":"2", "event.id": "4"}  
{"type":"register", "user.id":"2", "event.id": "5"}  
{"type":"register", "user.id":"3", "event.id": "1"}  
{"type":"register", "user.id":"3", "event.id": "3"}

I want to visualise count of users by events count in date range.

So, I do aggregation by user.id and have events count:

- user #1 register to event 3 times
- user #2 register to event 3 times
- user #3 register to event 2 times

However, the next step is group by events count. I don't understand how to make it in Kibana.  
Please, help me.

 ![Screen_Shot_2018-03-20_at_11_29_14](https://us1.discourse-cdn.com/elastic/original/3X/3/9/39aeec0fa4474b88e7a06ee1426d5416b1b234d3.png)

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 9:43am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/2 "2018-03-20T09:43:57Z")

</div>

Hey, that is unfortunately not possible. If I understand you correctly, you want to group on the result of the aggregation, which Kibana can't do for you.

Could you perhaps describe your use-case a bit more, what should the chart present, that you are trying to achieve? Maybe there is another solution to the problem.

Cheers,  
Tim

---

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 9:55am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/3 "2018-03-20T09:55:54Z")

</div>

Thanks.

Sure, this is a cohort analysis.  
I want to know the retention of users and see how often and how many users visit my events per specific time interval.

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 9:58am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/4 "2018-03-20T09:58:50Z")

</div>

I think in that case I would switch the aggregations a bit.

You could do a terms aggregation on the event id, so you get one bar chart per event. If you now are interested in how many users visited those events per specific time interval, instead of drawing the count of documents on the y-axis, which would be the overall visits (of all users) for that event, switch the metrics aggregation to "Unique Count" of the user id field. That way you will get only the number of unique user ids, that caused that event.

And the time interval can of course be specified using the time picker on top, but you could also add further limiting time filters to the visualization via the filter bar on top.

Hope that visualization is the direction you need.

Cheers,  
Tim

---

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 10:18am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/5 "2018-03-20T10:18:16Z")

</div>

I thinks this is not correct, because each bar will be contain count of user in an event, but I want to see how many users visited 10 events, how many users visited 9 events, etc...

---

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 10:46am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/6 "2018-03-20T10:46:36Z")

</div>

I need something like this SQL query:

```
SELECT
  t.events_count AS visited_events_count,
  count(t.events_count) AS users_count
FROM (
       SELECT
         user_id,
         count(*) AS events_count
       FROM event_user
       WHERE created_at BETWEEN '2018-03-18 00:00:00' AND '2018-03-20 00:00:00'
       GROUP BY user_id
       ORDER BY count(*) DESC
     ) AS t
GROUP BY t.events_count

```

| visited\_events\_count | users\_count |
| --- | --- |
| 1 | 193 |
| 2 | 82 |
| 3 | 38 |
| 4 | 15 |
| 5 | 4 |
| 6 | 3 |
| 7 | 2 |

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 10:47am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/7 "2018-03-20T10:47:33Z")

</div>

Yeah, that's true. Sorry I misunderstood your goal there.

So drawing the count of events a user had on the x-axis and the amount of users that hit that many events on the y-axis, is unfortunately not possible, since you cannot achieve that within a single Elasticsearch query.

---

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 10:49am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/8 "2018-03-20T10:49:04Z")

</div>

Ok, thanks. 😢

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 10:55am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/9 "2018-03-20T10:55:31Z")

</div>

You SQL example shows this very well, that you need multiple requests to achieve the desired result. Unfortunately Kibana is currently mostly build upon the concept of a single request per visualization using aggregations. Nevertheless you could achieve the result via Elasticseach, but not with classical Kibana visualizations. You could do a request with a terms aggregation on the user id and a unique count on the event id. That way you would get a list of buckets one for each user with the value of how many events s/he visited. Now you would just need to group by the event count in your system.

If you run Kibana 6.2+ you might be able to achieve that visualization from within Kibana using Vega visualization, which is a way more advanced visualization grammar allowing for customized requests and some post processing.

I will try to figure out a way to visualize that chart via Vega - but classical visualizations for sure won't work right now.

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 11:40am UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/10 "2018-03-20T11:40:04Z")

</div>

I was able to achieve the desired Graph via Vega visualizations available from 6.2+ in Kibana core as an experimental visualization and you can use the [plugin](https://github.com/nyurik/kibana-vega-vis) if you are running an older version.

The following Vega lite specification draws your desired bar chart:

```auto
{
  $schema: https://vega.github.io/schema/vega-lite/v2.json
  data: {
    url: {
      %timefield%: @timestamp
      %context%: true
      index: /events-* [or your index pattern]
      body: {
        size: 0
        aggs: {
          users: {
            terms: {
              field: "user.id"
              size: 10000
              order: {eventCount: "desc"}
            }
            aggs: {
              eventCount: {
                cardinality: {field: "event.id"}
              }
            }
          }
        }
      }
    }
    format: {property: "aggregations.users.buckets"}
  }
  mark: bar
  transform: [
    {
      aggregate: [
        {op: "count", field: "key", as: "usercount"}
      ]
      groupby: ["eventCount.value"]
    }
  ]
  encoding: {
    x: { field: "eventCount\\.value", type: "ordinal", sort: "descending" }
    y: { field: "usercount", type: "quantitative" }
  }
}

```

If you make sure the field names are correct, that will do a terms aggregation on the `user.id` field and calculate the amount of unique `event.id`s each user had.

After that we use Vega's `transform` to aggregate all buckets by their `eventCount.value` (i.e. the value of the unique count of events), so the result will look like:

```auto
[
  { "eventCount.value": 5, usercount: 1 },
  { "eventCount.value": 4, usercount: 3 },
  { "eventCount.value": 3, usercount: 20 }, // ...
]

```

We'll then just use Vega lite encodings to draw this as a bar chart (or you could of course use any [other chart](https://vega.github.io/vega-lite/examples/) you want). The period in `eventCount.value` must be escaped in the encoding, since Vega will otherwise try to find a nested field `eventCount: { value: ... }`, but in our case it's just the name of the field containing a period, caused by the groupby aggregation.

Hope that chart is closer to what you are looking for.

Cheers,  
Tim

---

<div class="post-metadata">

**Author:** ![Artur\_Zhdanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur_zhdanov/32/29019_2.png) [@Artur\_Zhdanov](https://discuss.elastic.co/u/Artur_Zhdanov)\
**Post date:** [March 20, 2018, 12:11pm UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/11 "2018-03-20T12:11:14Z")

</div>

Thank you so much. It works perfectly! 😍

 ![38](https://us1.discourse-cdn.com/elastic/original/3X/e/6/e6e9a2a43765a68aff5eec343ff9010238a6f285.png)

---

<div class="post-metadata">

**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\
**Post date:** [March 20, 2018, 12:13pm UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/12 "2018-03-20T12:13:33Z")

</div>

Great that it could help. For any additional styling, the official [Vega lite](https://vega.github.io/vega-lite/) docs are usually quite helpful and also have a look at Yuri's [blog post](https://www.elastic.co/blog/custom-vega-visualizations-in-kibana) about Vega in Kibana for a general introduction. ☀

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 17, 2018, 12:13pm UTC](https://discuss.elastic.co/t/how-to-make-histogram-by-grouped-aggregation-count/124709/13 "2018-04-17T12:13:36Z")

</div>

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