9 Replies Latest reply on Aug 3, 2012 4:22 AM by Peter Hopwood

    Conditional formatting

    Michael Sonu

      Hi

       

      I want to highlight the highest sales by Model/Year. Example attached. How can i do that in Tableau?

       

      chk.jpg

        • 1. Re: Conditional formatting
          Tracy Rodgers

          Hi Michael,

           

          This can be done by creating a calculated field that is very specific and placing it on the color shelf, i.e.:

           

          if year([Date])=2008 and [Model]='Premium' then 'yellow'

          elseif year([Date])=2009 and [Model]='truck' or [Model]='Van' or [Model]='SUV' then 'yellow'

          elseif year([Date])=2011 and [Model]= 'car' then 'yellow'

          else 'blue'

          end

           

          Hope this helps a bit!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Conditional formatting
            Peter Hopwood

            Michael,

             

            This is possible using the window functions. What you need to to is build a table calc that tests whether each  value is equal to the maximum value for the row - and then turning that into a 1 or a 0.. Then you can use that calc as a colour in the marks pane - ensuring you compute  using Table (Across).

             

            I've put together an example using Superstore sales - take a look at the Max Value calculation on the colour of the

            marks pane.

             

            Hope this makes sense

             

            Cheers

            Peter

            • 3. Re: Conditional formatting
              Michael Sonu

              Thank you Peter, This is what i am exactly looking for. Just one more question, if i want to highlight only if the highest sales in 2012, How to add if condition?

               

              if sum([Sales])=WINDOW_MAX(SUM([Sales])) and if(year(date)="2012"

              then 1

              else 0

              end

              • 4. Re: Conditional formatting
                Peter Hopwood

                Michael,

                 

                Yep that approach works - I've just tweaked your syntax a little:

                 

                if (sum([Sales])=WINDOW_MAX(SUM([Sales])) and year(attr([Order Date]))= 2012)

                then 1

                else 0

                end

                 

                Because we are doing an aggregation calculation we need to use attr() to get a single value of the year  and we need to ensure that both parts of the if statements  are assessed together so I've put in some brackets, lastly the year function returns a number not a string so I removed the "".

                 

                Glad I could be of help

                 

                Peter

                • 5. Re: Conditional formatting
                  Michael Sonu

                  Hi Peter,

                   

                  Thank you very much for the formula, I have entered the same formula in the Superstore sales. But iam not getting the correct result

                  • 6. Re: Conditional formatting
                    Michael Sonu

                    Thank you peter,

                     

                     

                    It works... super... Super... you are the master...

                     

                    Thanks a lot

                    • 7. Re: Conditional formatting
                      Michael Sonu

                      Hi Peter,

                       

                      what is the formula to highlight only the highest sales number irrespective of year. Max_ window highlights highest sales by year.

                       

                      Thanks

                      Michael.

                      • 8. Re: Conditional formatting
                        Peter Hopwood

                        Michael,

                         

                        To highlight the overall maximum value in the sample data set I included - you just need to change the 'Compute using'  value:

                         

                        Compute using.png

                         

                        Of course if your data set is laid out differently you may need to use a different order.

                         

                        Your other option might be to use Window_Max and change the Default Table Calculation to get the right aggregation level.

                         

                        Hope this helps

                         

                        Peter

                        • 9. Re: Conditional formatting
                          Peter Hopwood

                          Update

                           

                          I realised the last version I sent did work but wasn't very tidy - as the Max_Value calculation didn't work very consistently.

                           

                          I've attached a version that is much cleaner - that works for 'State', 'Year' or the whole table - depending on the Compute using setting. I've used the window_max() function and an Iff() statement to return the highlighted value.

                           

                          I've left the previous example in the workbook too for comparison.

                           

                          Cheers

                           

                          Peter