Find a list base on date

[earlcool]

I have many facilities where patients can go for treatment.

The patients have a unique Id (patients_id).  I want to assign a patient to the last facility they want to. If Patient 001 goes to facility A on 05/06/2021 and then that same patient goes to facility B on 05/07/2021. The patient will be in facility B because he wants here last.

When I click on the name of the facility, I want a count of all the patients that facility.

=Count({<

patient_id={"=Age >=18"}

,visit_date={"$(=Date(max(visit_date)))"}

>}DISTINCT patient_id)

I was help with the above but I think max only returns one date which is the last one in the list

I keep getting zero when I should get thousands

[stevedark]

Hi @earlcool 

There are a couple of issues in your statement, the first is the selection on patient_id. You need do the set based on Age rather than patient_id.

=Count({<Age*={">=18"},visit_date={"$(=Date(max(visit_date)))"}>}DISTINCT patient_id)

The other problem is that the max(visit_date) is looking at the latest date for any patient, not the specific patient being counted.

Should the latest facility for a patient be based on current selections?

If not I would suggest working out the latest record and facility for the patient in the load:

Patients:
LOAD
   patient_id,
   visit_date,
   patient_id & date(visit_date, 'YYYYMMDD') as patientvisitkey,
   facility,
   ... other patient detail fields ...
FROM ... ;

tmpMaxKey:
LOAD
   maxstring(patientvisitkey) as MaxKey
RESIDENT Patients
GROUP BY patient_id;

LatestFacility:
LOAD
   patient_id,
   visit_date as latest_visit,
   facility as latest_facility
RESIDENT Patients
WHERE EXISTS (MaxKey, patientvisitkey);

DROP TABLE tmpMaxKey;

Once you have that new table with latest_facility and latest_visit you should be in a better place. You should probably make an Is18 field in the load also, with a 0/1 value, to make that selection more efficient in the front end also.

Hope that helps,

Steve