4 Replies Latest reply on May 22, 2019 8:24 AM by JOHANNES LOW JUN WEI

    Problem with Calculated Field

    JOHANNES LOW JUN WEI

      Hello.

      This is my first time trying Tableau for my Data Analytics project. I am self-taught and just troubleshoot as I encounter any problem, but this proves to be quite weird.

      I have a set of data for immunisation coverage and I tried to classify each bar to a specific color correlating to the coverage i.e. <30 = red, and so on. For this, I attempted to use a calculated field. I have my data measured in average, thus it stumped me when a bar which represents 76.00 is classified to 30 -50% bracket, which gave it a different colour.

       

      IF

      [Measles Coverage (Singapore)] < 30 THEN "RED"

      ELSEIF

      [Measles Coverage (Singapore)] <= 50 THEN "ORANGE"

      ELSEIF

      [Measles Coverage (Singapore)] <= 80 THEN "YELLOW"

      ELSE "GREEN"

      END

       

      I will appreciate it if anyone can help point out my errors, and help me be more efficient with my workings et cetera.

      Thank you.

        • 1. Re: Problem with Calculated Field
          Mark Holtz

          Hi Johannes,

          I was unable to open your workbook because I'm not on the latest version, but I'm willing to bet that you have multiple records contributing to each "bar" that is colored on your display.

          If a bar represents multiple records, it is using some aggregation calculation (the default is SUM).

          In other words, your Color By calculation evaluates on each individual record in your data, NOT at the "aggregate all the records in a given partition and then evaluate."

           

           

          You can make your "Color By" calculation evaluate in aggregates rather than individual records by changing it to this:

          IF SUM([Measles Coverage (Singapore)]) < 30 THEN "RED"

          ELSEIF SUM([Measles Coverage (Singapore)]) <= 50 THEN "ORANGE"

          ELSEIF SUM([Measles Coverage (Singapore)]) <= 80 THEN "YELLOW"

          ELSE "GREEN"

          END

           

          This will "break" the Color By field if it is being used in any views (the little pill turns red) because this calculation has become an aggregation.

          You just need to drag any pill that is red off the view and then drag it back onto the view from the data pane.

          It will now show with AGG because the calculation contains an aggregation (SUM) function within its syntax.

          1 of 1 people found this helpful
          • 2. Re: Problem with Calculated Field
            Zhouyi Zhang

            Hi, Johannes

             

            Just as Mark suggested, but use avg instead in your case

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Problem with Calculated Field
              JOHANNES LOW JUN WEI

              Thank you for your insight. I finally manage to get my colour coding right. Much thanks and have a nice day!

              • 4. Re: Problem with Calculated Field
                JOHANNES LOW JUN WEI

                Thank you very much, I managed to get it right. Much cheers!