Find Value Range with maximum count

[#1]

Hi everyone,

I've got an expression in my Chart that categorises all the numerical values in a column and places them within a certain range. This is what it looks like:

=aggr(

  IF( colName > 0 and colName  <=75, '0.01% - 75%',

  IF( colName > 75 and colName <= 85, '75.01% - 85%',

  IF( colName > 85 and colName <=95, '85.01% - 95%',

  '>=95.01%'

  ))),

  colName

  )

My question is - if I want to have a Text Object that displays the range with the highest number of values within it, how would I do that? I've thought about nesting it in max() or FirstSortedValue(), but don't really know how to go about it.

Appreciate your help,

Thanks!

[#2 · Accepted solution]

Create the new Field in the Script called "Range_Value" by below:

LOAD *,

  IF( colName > 0 and colName  <=75, '0.01% - 75%',

  IF( colName > 75 and colName <= 85, '75.01% - 85%',

  IF( colName > 85 and colName <=95, '85.01% - 95%',

  '>=95.01%'

  ))) AS Range_Value

From Source;

Use the below Expression in the Text Object:

=FirstSortedValue(Range_Value, -Aggr(Count(ColName),Range_Value) )

Please post some sample data so Community will help more