Find Value Range with maximum count

Fri, 29 Nov 2013 00:10:53 GMT

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!

Re: Find Value Range with maximum count

Fri, 29 Nov 2013 01:47:22 GMT

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

topic Re: Find Value Range with maximum count in QlikView

Find Value Range with maximum count

Re: Find Value Range with maximum count