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

    binod lamichhane

      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