4 Replies Latest reply on Jun 21, 2018 10:10 AM by Jim Dehner

    Highlighting Min and Max value based on each column

    Piotr Konopnicki

      Hi guys,

       

      So this is my first post here. I've been searching to find an answer and I saw a bunch of workarounds but I think they were not relevant to this specific task.

      I'm trying to highlight a min and max value for each column, and this min and max value should be a min and max of this column, not any other variable.

       

       

      So I want to see on, for example, red a MIN value of CPC, min vlaue of Clicks, min value of Whatever, etc.

      and on GREEN, a max value of CPC, Clicks, Whatever.

      So it would be highlighted as:

       

       

      Example in attachment.

       

      Any help appreciated!

        • 1. Re: Highlighting Min and Max value based on each column
          Jim Dehner

          Hi

          see the attached

           

          The way your file was structured you had separate measures of each set of values you were looking at (and I guess "whatever" was a place holder for what would be many more)

          That creates a wide table where you need to look at each "column" individually

          Tableau and other database oriented systems work most efficiently on tall data sets

          that the long way of saying I pivoted the data Pivot Data from Columns to Rows

          to look like this

           

          this becomes the color code

           

          and you create the viz like this

           

           

           

          it returns this

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Highlighting Min and Max value based on each column
            Mavis Liu

            Hi Piotr,

             

            First pivot your data in the data source view, do this by control or shift select and right click on the value:

             

            2018-06-21_15h57_42.png

             

            Then rename the fields:

            2018-06-21_15h57_52.png

             

            Now rebuild your view and create this calculation:

             

            2018-06-21_16h02_33.png

             

            Then bring this field to colour:

             

            2018-06-21_16h03_04.png

             

            Please see the attached workbook. Anything in true/orange is the max per country.

             

            Thanks,

             

            Mavis

            • 3. Re: Highlighting Min and Max value based on each column
              Piotr Konopnicki

              I love your answer and I love what you did there and it works on this simple example.

              The issue is that my data source is a relatively large database with many measures and dimensions, and when highlighting a certain set of data (or actually any set) I can't seem to pivot it. I don't even have an option for it.

               

              Could you please advise how can I create a subset (or any other way) only to do this pivoting?

              • 4. Re: Highlighting Min and Max value based on each column
                Jim Dehner

                Ok when that happens your data source is the issue - some won't support the pivot

                there are a couple of approaches I know - one is a custom SQL to shape the dat

                another is to force the pivot using a join

                first you create a file with the measure names

                in this case it looks like this

                then you load that into Tableau with a Join

                 

                 

                 

                 

                to create the join

                 

                you will end up with data like this

                the data has been duplicated 1 time for each of the measure names

                 

                now you create a statement to get the results by Name

                 

                this is the same color code statement as before

                with the names and values coming from the Name filed and the Case statement

                and you end up the the same viz as before

                but Name goes on the column shelf

                the Case statement is aggregated to give the values

                color code goes on color

                 

                 

                Jim

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.