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.