topic Re: Find the Corresponding Date to Max Value in QlikView

Find the Corresponding Date to Max Value

Wed, 25 Mar 2015 12:48:40 GMT

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))

Re: Find the Corresponding Date to Max Value

JonnyPoole — Wed, 25 Mar 2015 13:02:18 GMT

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)

Re: Find the Corresponding Date to Max Value

Wed, 25 Mar 2015 13:06:36 GMT

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

Re: Find the Corresponding Date to Max Value

awhitfield — Wed, 25 Mar 2015 13:12:03 GMT

Or you could try:

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

Andy

Re: Find the Corresponding Date to Max Value

Wed, 25 Mar 2015 13:17:10 GMT

So many ways to do the same. Thanks again.