Multiple Count Aggregations With Multiple Fields

Is it possible to filter individual Count aggregations? I'm trying to get a count of all items that were opened in a month vs all items that were closed in a month. Each document will have an OPEN_DT field and if closed a CLOSE_DT.

If I split the series by the CLOSE_DT below it causes chaos. Unique counts of the two fields breaks cause some documents could have the same open/closed dates. Would the filters aggregation work here? Any help or advice would be appreciated. Would really prefer not to have two separate bar charts with date histograms of each field.

Hi,

As far as I know, it's not possible to use multiple datehist aggregation on different field in a single visualization in kibana.

I will achieve it in 2 steps.

  1. Create 2 transform.
  • transform1:
    • Group by
      • name: date
      • aggregation: date_histogram
      • field: OPEN_DT
    • Aggregations
      • name: open_count
      • field: OPEN_DT
      • aggregation: value_count
    • Destination index: index1
  • transform2:
    • Group by
      • name: date
      • aggregation: date_histogram
      • field: CLOSE_DT
    • Aggregations
      • name: close_count
      • field: CLOSE_DT
      • aggregation: value_count
    • Destination index: index2
  1. create index pattern (index*) and make barplot with it.

You can do this using only kibana GUI. Take care about transform limitations.

Hi @bsmith

in recent version of Kibana Lens you can have what is called a "filtered metric", where you could apply a filter to a metric like count for instance.

In your example:

I'm trying to get a count of all items that were opened in a month vs all items that were closed in a month.

you can build something like:

In my example I've used a Filter by for all products which price is above 100 and there's a discount applied. A similar Count metric can be built with the opposite Filter by for all products cheaper than 100 with a discount, and visualize something like this:

As for your example the two filters can be:

  • OPEN_DT: * AND NOT CLOSE_DT: * - Open items
  • OPEN_DT: * AND CLOSE_DT: * - Closed items (perhaps CLOSE_DT: * may work as well in this case).

Would that help?

I'll move the data set into an upgraded environment where transform/lens are available and give them a shot.

1 Like

I've actually found that timelion does the job well enough with just a few visual quirks.

1 Like

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