Viewing Count of a specific column

[sammondale]

Hello,

I have a table(Patient) which has many ‘Location’ entries in the ‘Location’ column. Which means several patients can have multiple locations. There is also a gender column for the patients. Something like below-
…Patient table…
Name Location Gender
Sam Texas M
David Texas M
Nicola Florida F
…

From this table i want to build a report view which shows the count of this location gender wise something like below-

-------------Report---------------
Location Patients Male Female
Texas 2 2 0
Florida 1 0 1

Please advice if there is a way to achieve this.
Thanks in advance!!

[LeventK]

@sammondale
Create 4 Virtual Columns: xLocation (you can set the Display Name as “Location”), Patients, Male and Female respectively with below expressions:

xLocation

UNIQUE([Location])

Patients

COUNT(
    Patients[Name], [_THISROW].[xLocation] = [Location]
)

Male

COUNT(
    Patients[Name],
    AND(
        [_THISROW].[xLocation] = [Location],
        "M" = [Gender]
    )
)

Female

COUNT(
    Patients[Name],
    AND(
        [_THISROW].[xLocation] = [Location],
        "F" = [Gender]
    )
)

Construct a Table UX View consisting only this 4 Virtual Columns.

[sammondale]

@LeventK
Hi Levent,

Thanks for your reply!! The UNIQUE() function does not work. It says “UNIQUE has invalid inputs”. I have a ‘Location’ column in the ‘Patient’ table which is of type ‘Address’.

[LeventK]

@sammondale
It’s my bad, sorry. I thought that your [Location] column is Text not Address, therefore UNIQUE() function will not work. You can use this expression instead:

SELECT(
    Patients[Location],
    TRUE,
    TRUE
)

[sammondale]

LeventK:

COUNT(
    Patients[Name], [_THISROW].[xLocation] = [Location]
)

@LeventK

thanks Levent!! this piece works but the ‘Patients’ expression is not working again. It says
“Cannot compare List with Address in ([ID].[xLocation] = [Location])”

[LeventK]

@sammondale
Can I look into your app build and the gSheet provided it’s possible? If so, please add levent@able3ventures.com as a co-author to your app and share the gsheet with the same email with Edit access. Thnx.

[sammondale]

LeventK:

levent@able3ventures.com

@LeventK
Hi Levent,

I have shared the app with your email ID. IF you go the ‘Data’ section , I need to update the ‘People’ Data in there (which I have referred as ‘Patients’ here to avoid confusion). I have already created the ‘xLocation’ virtual column there.

Thanks,
Sam

[LeventK]

@sammondale
Please provide gSheet edit access as well. Thnx.

[sammondale]

@LeventK

I think I gave you edit access. Were you able to access?

[LeventK]

@sammondale
I have completed my edits. Please refresh the AppSheet Editor browser before any editing session of yours. Basically I have added a ReportTable sheet to your back-end with a very simple QUERY formula which fetches data from the People table as per Location, Sum of Patients, Sum of Male Patients and Sum of Female Patients respectively. The formula is quite simple:

=QUERY(
	{
		People!A2:G,
		ARRAYFORMULA(IF(People!L2:L="Male",People!L2:L,"")),
		ARRAYFORMULA(IF(People!L2:L="Female",People!L2:L,""))
	},
	"SELECT Col7, count(Col1), count(Col8), count(Col9) where Col7 <> '' group by Col7 label Col7 'Location', count(Col1) 'Patients', count(Col8) 'Male', count(Col9) 'Female'"
)

I have imported this table as READ_ONLY to your app, created a Ref type Table UX for this table and assigned it to one of the small panes of your Reporting Dashboard UX. I have also deleted the unnecessary Virtual Columns from the People table.

Please check and revise as necessary.

---

PROOF OF CONCEPT

---

[sammondale]

@LeventK

Thanks Levent! This is exactly what I needed. I want to know where you have put that query for that ReportTable. I can not find it. Once I understand that, i can tweak my other reports as well.

Thanks!

[LeventK]

@sammondale
It’s in your back-end gSheet. I have assigned a green color to the sheet tab, so that you can notice it easily. The sheet formula is in A1 cell.

[sammondale]

@LeventK
Thanks! I got it. Thanks so much!!

[LeventK]

You’re welcome.