Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rohitraturi
New Contributor II

Aggr()

I am trying to find the maximum appointment date by provider, by patient. I am using the following statement but not getting the desired output.

aggr(max([Appt Date]), [Appt Date],[Provider NPI],[Patient Account No])

The output is attached. I am getting two records but the actual output should be one record with max appointment date.

 

4 Replies

Re: Aggr()

HI

Try like this
Max(aggr(max([Appt Date]),[Provider NPI],[Patient Account No]))
Partner
Partner

Re: Aggr()

By writing your expression...
aggr(max([Appt Date]), [Appt Date],[Provider NPI],[Patient Account No])
...you get the latest appointment per provider, patient AND *Appointment Date*.

You will probably want to exclude the [Appt Date] from you aggr list. Leaving you with this expression:
=aggr(max([Appt Date]),[Provider NPI],[Patient Account No])
???
Celebrating my 300 accepted solution in the Qlik Community
???
rohitraturi
New Contributor II

Re: Aggr()

I tried both the solutions but not getting the desired output. I am using a different patient this time.

Aggr1 - gives all the records for that patient by all provider NPI and all appt dates 

Aggr2 - gives one record for that patient by each provider NPI and each appt date. (However, those appt dates are not the latest one for each provider NPI) . I need the latest date.

Partner
Partner

Re: Aggr()

It is getting hard to understand what the issue is. I have created an mockup application that could help us understand each other.  I've attached the app.

In the pic below I have my "raw data" in the table to the left and my aggr expression calculating the latest date to the right. Is this the correct presentation and calculation? If not please comment. 

image.png

???
Celebrating my 300 accepted solution in the Qlik Community
???