Expression showing between certain values

[Anonymous]

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

[Muthu]

Hi Michael,

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

[Anonymous]

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

[Kushal_Chawda · Accepted solution]

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

[effinty2112]

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

[jonathandienst]

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.