Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to summarize records by a field at Max date?

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_IDPCP_PROV_ID2PCPSTARTDATEPCPENDDATE
Z365
Z6271169410/7/201112/26/2012
Z6271252212/27/20127/23/2013
Z627141197/24/2013
Z75460904/26/2010
Z78253154/14/20113/6/2013
Z7821167912/20/20103/8/2011
Z83553151/2/2014
Z883
Z90764975/20/2009
1 Solution

Accepted Solutions
Highlighted

Re: How to summarize records by a field at Max date?

Try this

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

View solution in original post

13 Replies
Highlighted

Re: How to summarize records by a field at Max date?

Try this

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

View solution in original post

Highlighted
Creator
Creator

Re: How to summarize records by a field at Max date?

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? 

Highlighted

Re: How to summarize records by a field at Max date?

Max value for the date aggregated at the PAD_ID level

Highlighted

Re: How to summarize records by a field at Max date?

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

Not sure I understand this

Highlighted
Creator
Creator

Re: How to summarize records by a field at Max date?

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

Highlighted

Re: How to summarize records by a field at Max date?

on the same chart? or in another table?

Highlighted
Creator
Creator

Re: How to summarize records by a field at Max date?

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

Highlighted

Re: How to summarize records by a field at Max date?

How do you imagine seeing it in this chart?

Capture.PNG

Will see a null PCP_PROV_ID2 with the count?

Highlighted

Re: How to summarize records by a field at Max date?

Something like this?

Capture.PNG