9 Replies Latest reply on Jun 11, 2018 1:27 AM by carlo.fuggetta

    Enhancing specific rows in Tableau desktop (latest release)

    carlo.fuggetta

      Hi all,

       

      I have made a simple table sheet with sales by client in two consecutive years (say 2016 and 2017). Years are in the columns and client names are in the rows. That's it. Clearly some clients have a sales value in both columns while some others have sales only in the first year (lost clients) or in the second (new clients).

       

      I would simply like to have new and lost client rows enhanced (say in shaded green for new and red for lost). Is there a simple way to do so? At a later stage I would also like to count the number of new clients, lost clients, growing clients, decreasing clients...

       

      I can't post an example from this machine but hope the request is clear enough?

       

      Many thanks in advance,

      Carlo

        • 1. Re: Enhancing specific rows in Tableau desktop (latest release)
          Simon Runc

          hi Carlo,

           

          So there are several ways to do this. I'll go with one using LoDs, as this will create a real row level dimension, so is independent of the vizLoD. I'm also going to hard code the 2016 & 2017, but we could calculated these dynamically (so it would work the same way when the data gets to 2018, without needing to change the calculations)

           

          My formula is

           

          IF {FIXED [Customer Name]: COUNTD(YEAR([Order Date]))} = 2 THEN 'Existing'

          ELSEIF {FIXED [Customer Name]: ZN(SUM(IIF(YEAR([Order Date]) = 2017 ,[Sales],NULL)))} > 0 THEN 'New'

          ELSE 'Lost'

          END

           

          I've attached an example using Superstore

           

          Hope that does the trick, and makes sense

          1 of 1 people found this helpful
          • 2. Re: Enhancing specific rows in Tableau desktop (latest release)
            carlo.fuggetta

            Hi Simon, thanks for the swift reply. I arrived at a similar result but using table calculation. My proble is that I would like to enhance the entire row and not just the value. The ideal would be a band on each client line. Also your example does not seem to work if you change the years (try 2017 and 2018) but as you said will have to tweak the formula

            • 3. Re: Enhancing specific rows in Tableau desktop (latest release)
              Simon Runc

              Ah I see. So we aren't able to colour a header dynamically in Tableau, so in order to do this, we need to bring the Customer Name (in my example) into the canvas pane. This leads to another issue, in that all panes are the same width!

               

              If you look at the attached I've used -1 and 1 to layout the table (I've dual axis-ed the 1 and -1 so they (together) have the same width as the name. I've had to create separate calculations for the Current Year Sales and Previous Year Sales. Using this method, we also get ugly headers for the columns, so the best way (I've found) is to create them in a separate sheet & put the 2 together in a dashboard.

               

              I've also amended all the calculation to work off {MAX(YEAR([order date]))} to it will be dynamic (assuming the user selects 2 consecutive years)...again we could handle for when they don't by using {MIN(YEAR([Order Date])} rather than Max Year -1

               

              So a bit of work, but we can get there in the end!

               

              Let me know if anything doesn't make sense.

              1 of 1 people found this helpful
              • 4. Re: Enhancing specific rows in Tableau desktop (latest release)
                carlo.fuggetta

                You did it !!! That's great. Now I just need to understand how...  For example how were you able to shade the full rows?

                • 5. Re: Enhancing specific rows in Tableau desktop (latest release)
                  Simon Runc

                  Cool. So I've used the Bar Mark Type, so it will draw a bar from the value (either 1 or -1) to the axis (0) and I fix the axis to only be the range of the bar (if you re-show the headers for the 1.0, -1.0 it'll make more sense)

                   

                   

                  3 of 3 people found this helpful
                  • 6. Re: Enhancing specific rows in Tableau desktop (latest release)
                    carlo.fuggetta

                    Absolutely beautiful - many thanks indeed !

                     

                    Carlo

                    • 7. Re: Enhancing specific rows in Tableau desktop (latest release)
                      carlo.fuggetta

                      Dear Simon,

                       

                      unfortunately I wasn't able to replicate in my workbook and with my data what you did (I am definitely a newbie). I replicated all calculated fields (Sales Current Year, Sales Previous Year, Max Year) but I wasn't able to make the main sheet looking as yours. Particularly I can't get value figures in. If you have a chance, could you please list the steps from the initial empty sheet?

                      • 8. Re: Enhancing specific rows in Tableau desktop (latest release)
                        Simon Runc

                        No probs...This one is actually a little bit tricky (we're, kind of, getting Tableau to do something it doesn't really want to!). At a high level, I think of Tableau as a drawing canvas, where green pills create axis, and blue pills headers (or pane up the screen). As such we can use this thinking to create 'fake' axis that we use to position stuff where we want them.

                         

                        I've done it as a series of GIFs

                         

                        First we, bring in the Customer Names, the create the first 'fake' axis (1.0 as a continuous, green, dimension). We then bring the customer name onto the label, set the mark type to bar and set the axis to be between 0 and 1. We can then un-tick the "show header" for the customer name header

                         

                        Tableau Table 1.gif

                         

                        Following a bit of formatting (changing the labels to smaller font, and reducing the transparency of the colours), we then create the other 2 fake axis (-1.0, and 1.0), but this time we bring in each of the values (one on the -1.0 and the other on the 1.0)

                         

                        Tableau Table 2.gif

                         

                        Finally we can dual axis these 2 (sync the axis), and set the axis range between -1 and 1...and we're pretty much there (when we dual axis Tableau adds measure name to the colour, so we just need to remove that

                         

                        Tableau Table 3.gif

                         

                        We can then untick the "show header" on our 'fake' axis...and then create the header sheet.

                         

                        Hope that's enough for you to replicate.

                        1 of 1 people found this helpful
                        • 9. Re: Enhancing specific rows in Tableau desktop (latest release)
                          carlo.fuggetta

                          Perfect Simon, I got it.

                           

                          Many thanks indeed and have a good day.

                           

                          Regards,

                          Carlo