5 Replies Latest reply on Dec 12, 2016 4:54 AM by Waseem Khalifa

    Dynamic conditional formatting or highlighting bars depending on the condition of the other bars

    Waseem Khalifa

       

      Hi All,

       

      I hope someone can help me with this, please have a look at my attached workbook and the screenshot above.

       

      On the graph, I want for each measure (bounce rate, orders & sessions), the bar for 'Last Week' to highlight or be a different color (red?) if the period 'Last Week' is worse off than the '4 Week Avg.' or the '12 Week Avg.

       

      So for the above graph, I would like

      - For Bounce rate column, 'Last Week' bar for both 'New Visitor' and 'Returning Visitor' to be highlighted/ be a different color as it's 'Last Week' figure is worse than the '4 Week Avg' and '12 Week Avg' figures

      - For Orders column, 'Last Week' bar for both 'New Visitor' and 'Returning Visitor' to be highlighted/ be a different color as it's 'Last Week' figure is worse than either the '4 Week Avg' or '12 Week Avg' figures

      - For the sessions column, 'Last Week' bar for 'Returning Visitor' to be highlighted/ be a different color as the sessions are lower than the '4 Week Avg.' and the '12 Week Avg.'

       

      Is this at all possible in Tableau?

       

      Many Thanks in advance,

      Waseem

       

      *the figures are made up for example purposes.

        • 1. Re: Dynamic conditional formatting or highlighting bars depending on the condition of the other bars
          Srinidhi Narayan

          Please see attached answer.

           

          I have two solutions, the second one is quite involved.  The first is a bit simpler.    The trick here is to use a Table Calculation formula that checks for a True/False condition of whether the bar is greater than or less than previous.    And the table calculation should be set to calculate "Pane Down".

           

          1.  On sheet one - I used color to depict the difference from previous.  Since you cant "overload" color on the bars, this replaces your existing color by dimension.

           

          2.  Sheet two is a bit more involved, but it allows you to preserve your existing coloration.  It uses table calculations just like #1, but incorporates dual axis charts, and shapes with colors to represent if the bar was up or down compared to previous.

           

           

          • 2. Re: Dynamic conditional formatting or highlighting bars depending on the condition of the other bars
            Waseem Khalifa

            Hi Srinidhi,

             

            Firstly many thanks for your two solutions, the second solution would work for what I'm looking for, but the calculation you have done is not quite right for what I need.

             

            I only want the calculation to show the shape on the 'Last Week' bar and no shapes on the '4 Week Avg.' and '12 Week Avg.' bars.

             

            So the condition should be that the 'last week' looks at the '4 Week Avg.' and '12 Week Avg.' and if the 'last weeks' figure is better than both the '4 Week Avg.' and '12 Week Avg.' then the green up arrow should display, else the red arrow should display.

             

            I've tried to edit and customise your calculated fields for this, but so far no luck.

             

            Is what I've suggested in this reply possible in Tableau.

             

            Once again many thanks in advance.

             

            Thanks,

            Waseem

             

             

            • 3. Re: Dynamic conditional formatting or highlighting bars depending on the condition of the other bars
              Srinidhi Narayan

              Ah.. I will look into it later today.  Meanwhile though, seems to me that you are aggregating this data before you pull it into tableau.  Is there a reason for that?  You might be better off getting the individual rows, and using tableau to calculate the 4 week, 12 week averages using a table calc. The reason I propose this is because then you have better control over your data and visualization, giving you a different way of looking at this data.

               

              My response earlier was to specifically address a particular viz challenge you have.  However, on looking at what your data intends to convey, I am wondering if you would consider ways to "answer the intended question".

               

              For example something like this... gives a week over week trend of orders, and where the line is black it is basically over the 4 wk, and 12 wk avg.  This was done using the sample superstore data.

               

              • 4. Re: Dynamic conditional formatting or highlighting bars depending on the condition of the other bars
                Srinidhi Narayan

                Back to your original question -

                 

                Use this formula in place of the existing one:  iif(FIRST() = 0, LOOKUP(SUM([Sessions]), FIRST()) = WINDOW_MAX(SUM([Sessions])), null)

                 

                Attached is the solution.

                 

                I wrote the final answer using just the "Sessions" measure.  But you can apply same logic to the other measures.  The reason I used Sessions was that it was the only one that gave me both positive and negative values so I could test it better.  As you will see in the answer, when you apply this - you will get a shape and a color for the null values as well.  All you will need to do is right click on the value in the legend, and just "hide" it.

                 

                Please note that this solution works only if the "Last week" bar is the first one on your viz.

                 

                1 of 1 people found this helpful
                • 5. Re: Dynamic conditional formatting or highlighting bars depending on the condition of the other bars
                  Waseem Khalifa

                  Hi Srinidhi,

                   

                  Thank you! This is exactly what i need.

                   

                  Regarding your reply on aggregating the data outside of tableau, I did this as I could not aggregate the data inside Tableau for the specific periods i needed (I am still very new to Tableau at the moment).

                   

                  If i was to aggregate the data in Tableau, would it be possible for me to have the Viz i have above (the bar chart).

                   

                  Thanks,

                  Waseem