3 Replies Latest reply on May 17, 2016 11:32 AM by Shayna Mullins

    How to Create Calculated Field which separates values above 80th percentile with the rest.

    Gil Ho

      Simply put, the calculated field will allow me to filter values based on whether they are above the 80th percentile or lower. So only two possible categories/values exist for this field: "Above 80th Percentile" or "Below 80th Percentile".

      Context
      The data I have are sum insured values of homes (eg, $100,000; $130,000 etc). The distribution looks like this:


      box2.JPG

       

      I want to be able to remove the outliers easily no matter what dimension I add to this data set.

      I am thinking that I would like to create a calculated field where the values are either "Above 20%" or "Below 80%".

       

      The formula I have chosen is this:


            IF ATTR([Sum Insured]) < PERCENTILE([Sum Insured], 0.8) THEN "Below 80%" ELSE "Above 20%" END]

       

      (note I used the ATTR function on the [Sum Insured] measure as Tableau would not let me use disaggregated and aggregated data on the same function.

       

      Now this formula was syntactically correct but (1) it doesnt allow me to treat it as a Dimension and (2) when I add it to the Columns area of the graph, it doesnt do anything.
      Does anyone know what I am doing wrong?