Use of Aggr Max

[Anonymous]

I have the following straight table:

I need to have a calculated dimension named "Constraining Patent" based on the dimensions 'Patent Number', 'DrugSubstanceFlag' and and 'Patent Expiry Date'.

The "Constraining Patent" is the 'Patent Number' where 'DrugSubstanceFlag' is equal to 'Y' and the maximum of the 'Patent Expiry Date'.  In this case it should be 8946235 because it has the highest 'Patent Expiry Date'.

I used the following as a calculated dimension:

=if(GDrugSubstanceFlag = 'Y',Aggr(Max(GPatentNumber, GFPatentExpiryDate),GFPatentExpiryDate))

and this gives me the following results which are not what i am looking for as explained above.

How can this be accomplished?

Regards.

Chris

[Anonymous]

I have tried the following and it does not work:

=Aggr(Max({<GDrugSubstanceFlag = {'Y'}>}GPatentNumber),GFPatentExpiryDate)

Regards

Chris

[Anil_Babu_Samineni]

Try this? And Don't forget do Suppress When value is null "For that Calculated Dimension"

If(GFPatentExpiryDate=Max(TOTAL GFPatentExpiryDate) and GDrugSubstanceFlag = 'Y', GPatentNumber)