How to summarize records by a field at Max date?

[baylor2016]

Dear all,

I have a table storing PCP changes per patients over time (see blow). I would like to summarize the patients by their last PCP. I was able to get the last PCP by using the =FirstSortedValue(PCP_PROV_ID2,-PCPSTARTDATE)  , but I cannot get the correct number of patients by last PCP.  I have tried to use Calculated dimension, aggr() in expression even set analysis, but the numbers are not correct. Please help. The sample project is attached.

Another question is how to add patients who have no PCP to the chart?

Thanks

Longmatch

PAT_ID	PCP_PROV_ID2	PCPSTARTDATE	PCPENDDATE
Z365
Z627	11694	10/7/2011	12/26/2012
Z627	12522	12/27/2012	7/23/2013
Z627	14119	7/24/2013
Z754	6090	4/26/2010
Z782	5315	4/14/2011	3/6/2013
Z782	11679	12/20/2010	3/8/2011
Z835	5315	1/2/2014
Z883
Z907	6497	5/20/2009

[sunny_talwar · Accepted solution]

Try this

=COUNT(DISTINCT Aggr(If(max(TOTAL <PAT_ID>PCPSTARTDATE, PAT_ID) = PCPSTARTDATE, PAT_ID), PAT_ID,PCPSTARTDATE ))

[baylor2016]

Worked perfectly. Thanks

What does TOTAL <PAT_ID> do here. Without it, it returns more PCP records.

By the way, is there any way to add patients who have not PCP in the chart? 

[sunny_talwar]

Max value for the date aggregated at the PAD_ID level

[sunny_talwar]

By the way, is there any way to add patients who have not PCP in the chart?

Not sure I understand this

[baylor2016]

Some patients (Z365) do not have PCP selected. I would like to show how many patients have no PCP(PCP_PROV_ID2 is null).

[sunny_talwar]

on the same chart? or in another table?

[baylor2016]

If it can be displayed in the same chart, that will be perfect.

[sunny_talwar]

How do you imagine seeing it in this chart?

Will see a null PCP_PROV_ID2 with the count?

[sunny_talwar]

Something like this?