# Count based on condition (not just simple aggregate)

**URL:** <https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792>\
**Category:** Kibana\
**Created:** [January 2, 2018, 2:15pm UTC](https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792 "2018-01-02T14:15:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jonathan\_Dick](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@Jonathan\_Dick](https://discuss.elastic.co/u/Jonathan_Dick)\
**Post date:** [January 2, 2018, 2:15pm UTC](https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792/1 "2018-01-02T14:15:07Z")

</div>

Hello, I need to produce a line graph which counts the number of patients who are active in care in a given month. A patient is deemed active within a given month if they have a "return to clinic" date \< 90 days from the end of the month. For example, say a patient's last encounter datetime was June, 1, 2017 and return to clinic date is July 31, 2017, This patient is considered to be active in June, July, August, Sept, October (even if the patient has not yet returned to clinic). Each document has the encounter\_datetime, rtc\_date and next\_encounter\_datetime. In sql, we could have a condition like:

count(if(timestampdiff(day, rtc\_date, reporting\_month\_date) \< 90 and (next\_encounter\_datetime is null or next\_encounter\_datetime \> reporting\_month\_date),1,0))

I should add that we create a dataset (via sql) that for each month in the analysis, we join the most recent patient encounter relative to that month.

Is there a way to create a similar count in Elastic and visualize this in kibana?

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![Joe\_Fleming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_fleming/32/3561_2.png) [@Joe\_Fleming](https://discuss.elastic.co/u/Joe_Fleming)\
**Post date:** [January 2, 2018, 11:28pm UTC](https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792/2 "2018-01-02T23:28:00Z")

</div>

Elasticsearch works a little differently.

In this case, you would probably use [datemath and range queries](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-range-query.html#_date_math_and_rounding) to get the matching documents, and then just look at the number of hits to get the count.

I don't quite follow how all your date fields work, but the short of it is you'll craft a query and apply it as a filter in Kibana. Click on "Add a filter" at the top, and then "Edit Query DSL", and in here you can type a raw Elasticsearch query.

![07](https://us1.discourse-cdn.com/elastic/original/3X/3/c/3c6b2ceab46dacf6ffc677f2e2544321adccbf7a.png)

You can get the `and` operation by applying two filters.

I'm unfortunately not super well versed in Elasticsearch queries though, so I'm not sure how you would compare the dates across two fields. I _think_ you could fall back on [scripted fields](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/search-request-script-fields.html), do the comparison there and then filter where the value is \< 90. You'll want to use the [expression syntax](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/modules-scripting-expression.html#_date_field_api) to get date operators.

You'll also want to use a scripted field to compare `next_encounter_datetime` and `reporting_month_date`, and maybe use that comparison to return a boolean.

So you'll have two filters, one that uses a scripted field to get the difference of `reporting_month_date` and `rtc_date` and filters on the value being less than 90, and another that uses a scripted field to compare `next_encounter_datetime` and `reporting_month_date` and filters where result is true.

That'll give you all the matching documents, which you can create as a saved search. Then you can use that saved search to make a metric visualization that simply shows the count.

---

<div class="post-metadata">

**Author:** ![Jonathan\_Dick](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@Jonathan\_Dick](https://discuss.elastic.co/u/Jonathan_Dick)\
**Post date:** [January 4, 2018, 9:12am UTC](https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792/3 "2018-01-04T09:12:03Z")

</div>

Thanks @Joe_Fleming. After re-evaluating this, I think the easiest solution is probably to change the datasets we are using to report on rather than come with more complexing querying of the existing dataset. Thanks again for your help!

---

<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:** [February 1, 2018, 9:12am UTC](https://discuss.elastic.co/t/count-based-on-condition-not-just-simple-aggregate/113792/4 "2018-02-01T09:12:31Z")

</div>

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