Display Date for Max Value

[ACarroll]

I need to show all dates for when a value is at its maximum.

My table has 3 columns. Member_Number, Max(Advance_Equivalent) and Business_Date.

I need to show the Business_Date when the Advance_Equivalent is at its maximum for each Member_Number.

Can anyone help?

[patilamay]

If I understood your requirement correctly then I think you can use firstsortedvalue function to resolve your issue.

else please provide a sample set of data to work and provide solution.

Thanks,

[ACarroll]

Thank you! It seemed to work with this expression

Date(firstsortedvalue(Distinct Business_Date,-Advance_Equivalent),'MM/DD/YYYY')

However, there may be a few different dates that contain the maximum Advance Equivalent. Is there a way to modify this expression to show each date?

Please let me know if sample data is still needed to provide clarity.

[patilamay]

You can use group by to show the dates. Surely that will help if can you provide sample data.

[ACarroll]

The results I'm getting are shown here, but it is only showing 1 date value for each member number.

As you can see here, there are several date values that are equal to the Max(Advance_Equivalent) for each Member_Number. I would like my table to list them the same as below, but only for maximum values.

[ACarroll · Accepted solution]

For anyone interested, here is the solution I came up with. I'm sure there is an easier way, but this is the only way i was able to get this to work. I left Business_Date as just the field name, as well as Member_Number. For Advance Equivalent I used the below expression as a dimension and excluded NULL values.

=Num(If(

Advance_Equivalent = (Max(Total{<Member_Number = {'111111'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'222222'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'333333'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'444444'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'555555'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'666666'}>}Advance_Equivalent))
or Advance_Equivalent = (Max(Total{<Member_Number = {'777777'}>}Advance_Equivalent))
,Advance_Equivalent),'$#,##0.00')

[Kushal_Chawda]

I know you got the answer but just one suggestion as your expression can be simplified. May be this solution won't work in your scenario but try it

Dimension:

Member_Number

Business_Date

Expression:

=if(sum(total <Member_Number>aggr(max(Advance_Equivalent),Member_Number))=sum(Advance_Equivalent),Sum(Advance_Equivalent),0)

Note: Please check suppress zero value option in chart properties

[ACarroll]

For some reason, this expression only showed values for one member number.

[Kushal_Chawda]

I hope you are using the same expression and using dimension as suggested. It should work

[ACarroll]

Yes, I am using all the fields exactly how you mentioned. Here's the result.

It displays in the way i would like to see, but only for one member number, not all of them.