Data extraction & Unique ID question

[drfuadj]

Hi all, let me first share the overall idea of the app im developing,

Its an app for a clinic whereby it contains the database of PATIENTS (main database with basic personal details) and APPOINTMENTS for each unit (separate table in Google Sheet for each appointments & details):

Unit:
1-  Appointment for WOUND CARE
2- Appointment for ACUTE CASE
3- Appointment for PROCEDURES

The way im doing it is that once patient registered with their IC NUMBER (unique ID) in the PATIENT DATABASE, the patient can be given further appointments to WOUND CARE, ACUTE CASE etc whenever its needed. And the patient can be given multiple appointments for each unit.

My issue is with the data extracted from the APPOINTMENTS for each UNIT. How can i make so that I can know how many unique PATIENT registered only for EACH UNIT? Each appointments unit does not use IC Number as a unique ID because the PATIENT DATABASE is already using the IC Number as unique ID. 

Im not sure whether i explained it clearly, hope it gives some idea regarding the issue.

[Marc_Dillon]

COUNT( UNIQUE( SELECT( appointments[ic number] , [unit] = "x" )  ) )

[drfuadj]

Thanks @Marc_Dillon for the help...

Btw can i use this formula in the appointment table in the google sheet? or i can extract the data according to months? 

[Marc_Dillon]

I don't understand that question.

[drfuadj]

Sorry sir, i didnt explain it well. 

This question actually also relates to the data in the google sheet. 
The way im structuring the parent table (PATIENT DATABASE) and the child table (APPOINTMENTS) by referencing doesnt allow me to know how many truly unique number of patient given appointments for each unit every month.

Let say a patient name JOHN was registered in the PATIENT DATABASE and given his 1st APPOINTMENT (child table) on 3 March 2023. This is i consider as truly unique.

In April, if the same patient was given another appointment date, this is not consider unique as he already visited once in March.

So im asking an opinion how can i figure the structure of the app or other ideas on how i can extract the truly unique number of patients visited the clinic for given month.

Ive also asked the question in the Google Doc community and they've come up with this formula to extract the truly unique data for each month. 

The table ACUTECASE is a sample data. The HELP table is the solution theyve provided. Hope this gives you an idea what im trying to explain.

https://docs.google.com/spreadsheets/d/1mORqSWkOHSybvgz2tGImbG7jILCLisDtDMFbfje_tyw/edit?usp=sharing 

Thanks!

[Marc_Dillon]

---

@drfuadj wrote:

how i can extract the truly unique number of patients visited the clinic for given month

---

With the expression that I already gave, just replace the [unit]="x" part with [month]="x".

[drfuadj]

OKay noted. will try it out. Thanks again!