Getting an average from specific dimension results

[ebest74]

Hi All!  Hoping for some help here.

I am new to QlikSense and have a clinical healthcare background not technical so I apologize if I use the wrong language here.

I am creating a dashboard to compare hospital discharge times across various sites.  On one sheet I am trying to create a comparison that shows all six sites without the end user having to make the selections.  For the patient count I am using the expression in a KPI of (count({<Organization={'Hospital A'}>}Organization)) which gives me the results of just that facility.  For the average time I am using a KPI with the expression avg(if([DC TAT]<5760,([DC TAT]))) which gives me the average but for all the facilities involved.

Essentially I trying to jam those two expressions together so I can have a KPI that shows the Avg DC TAT only when it equals Hospital A, Hospital B, etc..

I have tried various expressions and have googled like crazy but cannot seem to figure it out.  My guess is I need to use Aggr function but I cannot seem to get an expression that works correctly.

Any thoughts would be greatly appreciated! 

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[ebest74]

I'm using KPIs to display the data now.  One KPI for the total count and one for the average time.  As it stands now the user would have to select the various facilities to compare.  I'm looking for an expression that would essentially only show the average of a specific site.

Below is a cut out of my raw data.  So looking for an expression that shows DC TAT Average only when the Organization equals either Hospital A or Hospital B.

In my brain this would be something like the below which of course doesn't work...just displaying what my mind thinks it should be....

=Avg(if([DC TAT TIME]<5760,([DC TAT TIME])))when(count({<Organization={'Hospital A'}>}Organization))

Organization	Discharge Date & Time	DC TAT
Hospital A	03/03/2020 08:30:00 PM	438
Hospital A	03/04/2020 11:06:27 AM	39
Hospital A	03/13/2020 03:13:46 PM	139
Hospital A	03/05/2020 03:15:00 PM	53
Hospital A	03/11/2020 11:40:07 AM	72
Hospital B	03/07/2020 01:13:00 PM	149
Hospital B	03/08/2020 11:46:00 AM	75
Hospital B	03/08/2020 02:43:09 PM	234
Hospital B	03/09/2020 07:05:51 PM	84
Hospital B	03/10/2020 09:15:13 AM	42
Hospital B	03/11/2020 07:40:00 PM	393
Hospital B	03/11/2020 06:41:00 PM	283

[Taoufiq_Zarra]

can give you more information about the requested formula,
on the basis of the sent extract what is the value of the AVG and how you calculate it, to test it with my suggestion

[ebest74]

I hope I'm answering your question correctly...but in the case of the small sample of data below I would have a KPI for Hospital A for total number of patients which would be 5 then a separate KPI that would show the average DC TAT for just Hospital A which would be 148.

So the expected results of the KPI formula would be to show avg DC TAT only when the Hospital is A, B, C, etc..

[ebest74]

Wanted to reach out on this subject again...anyone smarter than me (casting a REALLY wide net) have any thoughts/ideas how I can write this expression??

Appreciate it!