Aggr()

[rohitraturi]

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.

[MayilVahanan]

HI

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

[Vegar]

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])

[rohitraturi]

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.

[Vegar]

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.