How to construct class function with > and < as the ends of the range?

[pam1990]

How do I construct the class function to create the following two ranges?

RANGE 1

>2

1.75-2

1.5-1.75

1.25-1.5

1-1.25

<1

RANGE 2

<70

70-80

80-90

>100

[GaryGiles · Accepted solution]

If you are trying to do this in a Qlik Sense chart, you could use:

=aggr(Only(if([fieldname]<1,'<1',if(Value>2,'>2',Replace(Class([fieldname],.25),' <= x < ','-')))), [fieldname])

You would want to sort descending by max([fieldname]).

=aggr(Only(if([fieldname]<70,'<70',if(Value>100,'>100',Replace(Class([fieldname],10),' <= x < ','-')))), [fieldname])

You would want to sort ascending by max([fieldname]).

On Range 2, it looks like you left out 90-100.

Also, you don't have to enclose the if statement within an aggr().  It depends on what behavior you are looking for is a user selects a value in the field.  With aggr() this first expression would look like:

=if([fieldname]<1,'<1',if(Value>2,'>2',Replace(Class([fieldname],.25),' <= x < ','-')))

Also, the if statement is what you could use if you were creating it in the load script.

Hope that helps.

[pam1990]

These worked great. Thank You

[pam1990]

Something odd is happening though. There is a range of 2-2.25, which should be the >2 range. Here is what I added to the load script. 

if(DCR<1,'<1',if(DCR>2,'>2',Replace(Class(DCR,.25),' <= x < ','-'))) as DCR_RANGE

Where you have Value, I put the field name. Is that where I went wrong??

[pam1990]

I know why it is happening. There are a couple where the DCR is exactly 2. I want those to be in the 1.75-2 range.

[GaryGiles]

What is the precision of DCR?  Could you subtract a non-substantial amount from the DCR field in the Class() function like this to keep 2.0 in the 1.75-2 group:

if(DCR<1,'<1',if(DCR>2,'>2',Replace(Class(DCR-.0000001,.25),' <= x < ','-'))) as DCR_RANGE

[pam1990]

I believe I got this working. Thank You @GaryGiles .