3 Replies Latest reply on Jul 17, 2018 9:46 PM by Mahfooj Khan

# how to write formula to select values in the specified range

may be it is silly to ask such question,

i am going to create three sets that selects sales amount of 0k to 10k, 10k to 20k and over 20k. (as shown i figure)      sales category - Album on Imgur       i wrote the formula as follows:

([Sales Amt] >=0 AND [Sales Amt] <=10000)      for (0 to 10k)

([Sales Amt] >10000 AND [Sales Amt] <=20000)     for [10 to 20k]

([Sales Amt]>20000)      for [over 20 k]

after this i am going to create a calculated field like this. calculated field - Album on Imgur

previously i added  max function like this        MAX(([Sales Amt] >=0 AND [Sales Amt] <=10000))  with all three formula  but didn't get correct result. i am not sure is this formula is correct or not and don't know how to write.

one possible work would be

SalesAmt <= 10k:- By field: Sales_Amt; Sum; <= 10,000 2. SalesAmt >= 20k: By field: Sales_Amt; Sum; >= 20,000

and in calculated field,

IF [SA <= 10k] then "Band <=10k"

ELSEIF [SA >=20k] then "Band => 20k"

ELSE "Band 10k to 20k"

END

but i think there is better approach than this.

i took reference from this Writing Conditions in Tableau   question also  but in my case i am applying with sets.

so could you please help me how to write formula get these categorical sales amount?? Mahfooj KhanLuisa Bez

• ###### 1. Re: how to write formula to select values in the specified range

Hi Binod,

Instead of creating sets ,why can't you use the logic directly in the field:

if [Sales]>=0 and [Sales]<=10000 then 'Band 0 to 10k'

ELSEIF [Sales]>=10000 and [Sales]<=20000 then 'Band 10 to 20k'

ELSEIF [Sales]>=20000 THEN 'Band over 20k'

END

• ###### 2. Re: how to write formula to select values in the specified range

meenu choudhary      yes it could also be one the approach, but what will happen if followed my method?

also i tried with your above method also and got something like this, result - Album on Imgur      which is not as per my exception. here is what i want to achieve expected - Album on Imgur  .   could you please suggest about this?

• ###### 3. Re: how to write formula to select values in the specified range

Hi Binod,

You can try something like this,

IF [Sales]<=10000 then 'Band <=10K'

ELSEIF <=20000 then 'Band >10K - 20K'

ELSEIF [Sales]>20000 THEN 'Band >20K'

ELSE "Null Sales"

END

Let us know if this help.

Mahfooj