Using Max with Dates

[Anonymous]

I have the following table:

I need to have a calculated dimension "Generic Product Entry Date" calculated as a maximum of a range of dates as follows:

=If([GPatent Expiry Date] > ExclusivityDate, Max([GPatent Expiry Date]), Max(ExclusivityDate))

I get errors as in the above table.  How do i accomplish this with set analysis or whatever is appropriate?

Regards.

[Lisa_P]

Why is this not an expression ?

[vishsaggi · Accepted solution]

‌mMay be use aggr(max()) like

=Aggr(Max(if(Date1> Date2, Date1,Date2)), Date1)

change the fieldnames with your your original names.

[Anonymous]

Thank you very much.  I changed the field names to the following:

=Aggr(Max(if([GPatent Expiry Date]> ExclusivityDate, [GPatent Expiry Date],ExclusivityDate)), [GPatent Expiry Date])

and I get the following results:

For the product circled in red ACALABRUTINIB, it has more than 1 Generic Drug Product Entry Dates, however, this should be a single date which is the maximum of the Patent Expiry Date and Should be 01/07/2036 since the Maximum Exclusivity date is less than the Maximum Patent Expiry Date.

Regards.

Chris

[Anonymous]

Many thanks for your reply.  However i do not understand your question in the reply.

Regards

Chris

[Anonymous]

The correct Expression is as follows, Date1 at the end of the expression should be Date2

=Aggr(Max(if([GPatent Expiry Date]> ExclusivityDate, [GPatent Expiry Date],ExclusivityDate)), ExclusivityDate)

Regards.

Chris