topic Re: Expression showing between certain values in QlikView

Expression showing between certain values

Anonymous — Thu, 06 Jul 2017 08:06:05 GMT

Hello

I am looking to create an expression which groups and shows the values between certain thresholds

An idea is

=
If(Rate like (<[Rate]={">$(0)<=$(50)"}>} Rate), 'Under 50',
If(Rate like (<[Rate]={">$(50)<=$(500)"}>} Rate), 'Between 50 and 500',
If(Rate like (<[Rate]={">$(500)<=$(1000)"}>} Rate), 'Between 500 and 1000',
If(Rate like (<[Rate]={">$(1000)<=$(5000)"}>} Rate), 'Above 1000',
null()))))

Rate being the Field it is being pulled from

I am looking to use this as a filter for some charts

Any help would be appreciated

Thank you

Re: Expression showing between certain values

Muthu — Thu, 06 Jul 2017 08:40:49 GMT

Hi Michael,

Can you please explain in detail what you are actually expecting?

Re: Expression showing between certain values

Anonymous — Thu, 06 Jul 2017 08:51:58 GMT

Hello

I want to show in a filter

'Under 50',

'Between 50 and 500',

'Between 500 and 1000',

'Above 1000',

And if the Under 50 filter is selected it will affect the charts

i.e.

if there are 100 Rates under 50 when that filter is selected then it will change the charts to only show those 100 Rates

Hopefully that made it a bit clearer

Thank you

Re: Expression showing between certain values

Kushal_Chawda — Thu, 06 Jul 2017 09:02:03 GMT

why not to create this field in script

LOAD *,

if( [Rate]>0 and Rate <=50, 'Under 50',

if( [Rate]>50 and Rate <=500, 'Between 50 and 500',

if( [Rate]>500 and Rate <=1000, 'Between 500 and 1000',

if( [Rate]>1000 and Rate <=5000, 'Above 1000',

null())))) as Bucket

now you can use this Bucket field as selection

Re: Expression showing between certain values

effinty2112 — Thu, 06 Jul 2017 09:04:41 GMT

Hi Michael,

Create a listbox with an Expression. New Sheet Object>List Box ... and for field go to the bottom of the list and pick Expression.

Use this expression:

Aggr(

if(Rate<=50, 'Under 50',

if(Rate<=500, 'Between 50 and 500',

if(Rate<=1000, 'Between 500 and 1000',

'Above 1000'))),Rate)

cheers

Andrew

Re: Expression showing between certain values

jonathandienst — Thu, 06 Jul 2017 09:15:56 GMT

It is usually better to do this in the load as kushal said above.

You can simplify this to:

LOAD *,

if(Rate > 0 and Rate <=50, 'Under 50',

if(Rate <= 500, 'Between 50 and 500',

if(Rate <= 1000, 'Between 500 and 1000',

if(Rate <= 5000, 'Above 1000',

null())))) as Bucket,

...

or perhaps even

LOAD *,

if(Rate <= 50, 'Under 50',

if(Rate <= 500, 'Between 50 and 500',

if(Rate <= 1000, 'Between 500 and 1000', 'Above 1000'))) as Bucket,

...

Selections on calculated dimensions or calculated expressions in a list box may not perform well or work quite as you might expect. Such selections cause a selection of the underlying fields, not of the actual value in the list box.