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
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.
