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

[baylor2016]

Yes. A count for patients with null PCP_PROV_ID2 will be ok.

[sunny_talwar]

Just posted a solution above....

[baylor2016]

Well done!

[baylor2016]

Hi Sunny,

I posted another question about how to use dynamic set analysis and alternate state to build a cohort builder. Could you please review that post to see whether you can help me. Someone suggested to use And  mode, I do not think it is an ideal solution. Thanks as always.

Longmatch