Find the Corresponding Date to Max Value

[#1]

I am attempting to find the week in which the max sales occurred. The date is needed to feed back into another formula to show the daily trend in the week.

I have the max sales number but have hit a bump in trying to now return the ActivityWeek the amount is associated with.

Max(AGGR(sum(ActSales), ActivityWeek))

[JonnyPoole · Accepted solution]

You can use a search condition in Set analysis to fund the Week where the sum(ActSales) is the same week as the week with the maximum ActSales:

=Concat(  {<ActivityWeek={"=Max( total aggr( sum(ActSales),ActivityWeek))=aggr(sum(ActSales),ActivityWeek)"}>}  DISTINCT ActivityWeek) 

[#3]

Thank you for the clear response. This is a great approach.

[awhitfield]

Or you could try:

=FirstSortedValue(ActivityWeek,-aggr(sum(ActSales),ActivityWeek))

Andy

[#5]

So many ways to do the same. Thanks again.