Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Find the Corresponding Date to Max Value

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Find the Corresponding Date to Max Value

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

4 Replies
Employee
Employee

Re: Find the Corresponding Date to Max Value

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

Not applicable

Re: Find the Corresponding Date to Max Value

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

awhitfield
Esteemed Contributor

Re: Find the Corresponding Date to Max Value

Or you could try:

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

Andy

Not applicable

Re: Find the Corresponding Date to Max Value

So many ways to do the same. Thanks again.

Community Browser