How to get the patients' city at the given date?

[baylor2016]

Hi,

I am trying to get the summary of patients' city at a given date. The sample data is like this. I used the CITY as dimension and the following as my expression. It always return 4 records instead of 3. It looks like my expression does not exclude the previous cities. Please advice.

Thanks. Sample project attached.

=count(

{

<

YearMonth,

StartDate={"$(='<=' & Date(selectedDate))"},

PAT_ID=P({<

YearMonth,

StartDate={'=MAX(StartDate)'}>} PAT_ID)

>}

PAT_ID

)

PAT_ID	CITY	StartDate	Enddate	ZIP
1	HOUSTON	5/31/2011		77008
2	HOUSTON	1/25/2009	10/9/2009	77071
2	LA PORTE	5/11/2009	12/29/2010	77571
3	HOUSTON	3/15/2009	4/1/2009	77265
3	HOUSTON	12/1/2016		77088
3	ROSHARON	3/26/2010	5/22/2012	77583

Longmatch.

[vvvvvvizard]

=count(

{

<

City={*}-{'ROSHARON'}, 

YearMonth,

StartDate={"$(='<=' & Date(selectedDate))"},

PAT_ID=P({<

YearMonth,

StartDate={'=MAX(StartDate)'}>} PAT_ID)

>}

PAT_ID

)

[baylor2016]

Hard-code is not a solution for this problem. Thanks

[tamilarasu]

Hi Haijunw,

Try this,

=count(

{<

  YearMonth,

   StartDate={"$(='<=' & Date(selectedDate))"},

   PAT_ID=P({<

   YearMonth,

   StartDate={"$(=MAX(StartDate))"}>} PAT_ID)

>}

PAT_ID

)

[baylor2016]

Not working. From other posts, I understand that the Max(StartDate) is only evaluated once. So we cannot get last city for each pat_id by using max(startdate).

[tamilarasu]

I am a bit unclear. What is your expected result? Which cities you should see?

[baylor2016]

I would like to know where the patients are at a given date. In my example, if the selected date is 12/31/2016, then expected results is HOUSTON (2) and LA PORTE(1). If the selected data is 11/30/2016, the results should be HOUSTON(1) LA PORTE(1) and ROSHARON(1).  Thanks.

[Anonymous]

Hi,

Can you please explain the logic behind how you get Houston and La Porte for 12/31/2016.

[Anil_Babu_Samineni]

I don't have access your application, Perhaps this?

=count({<CITY = P({<CITY = {$(=Concat(Chr(39) & GetFieldSelections(CITY, ',', Chr(39)), ','))}>} CITY),YearMonth,StartDate={"$(='<=' & Date(selectedDate))"}, PAT_ID=P({<YearMonth,StartDate={'=MAX(StartDate)'}>} PAT_ID)>}PAT_ID)

[baylor2016]

Not working.

What is the logic for CITY = P({<CITY = {$(=Concat(Chr(39) & GetFieldSelections(CITY, ',', Chr(39)), ','))}>} CITY)?

Can we get the different max(startdate) for each pat_id in set analysis?

Thanks