Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to use the max result from a field to produce a value from another field. For example in the simple chart below
I would like have a listbox for month and 2 text boxes for results
first text box would produce max count of sales in the selected month by a company (if month J is selected the result would be 3 because company A has a total of 3 entries in month J)
second text box would produce the company name that has the most entries in a given month(if month J is selected the result would be "A" as the company has 3 entries which is the most for any company in month J).
I have tried aggr and concat but cannot get them to work the way i would like.
//For value
=max(aggr(Count(Sales),Company))
//For company
=FirstSortedValue(distinct Company, -aggr(Count(Sales),Company))
//For value
=max(aggr(Count(Sales),Company))
//For company
=FirstSortedValue(distinct Company, -aggr(Count(Sales),Company))