Count based on condition (not just simple aggregate)

[Jonathan_Dick]

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.

[Joe_Fleming]

Elasticsearch works a little differently.

In this case, you would probably use datemath and range queries 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.

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, do the comparison there and then filter where the value is < 90. You'll want to use the expression syntax 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.

[Jonathan_Dick]

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!