Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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)
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)
Thank you for the clear response. This is a great approach.
Or you could try:
=FirstSortedValue(ActivityWeek,-aggr(sum(ActSales),ActivityWeek))
Andy
So many ways to do the same. Thanks again.