5 Replies Latest reply on Jan 17, 2017 3:30 PM by Santiago Sanchez

    Annotating Bar Graph

    Nathan Fowler

      Hi All,

       

      I'm looking at shading/annotating a bar graph in my dashboard.  I'm currently running a table behind tableau that is showing current day plus the next 6 days of data.  This changes every day from a table I have set up in the sql server.

       

      Attached you can see a sample of how it looks.  I am currently using an annotation to show the forecast.  However when the table is refreshed this disappears, so I am manually annotating the dashboard daily.  I need the next 6 days, or in the example attached, the next 3 years to maintain the annotation or some type of shading.  For instance 2013-2015 are shaded as a forecast.  The next refresh year 2012 will drop off and only 2014-2016 would be shaded.  Is this possible?  I was thinking along the lines of some T/F?

        • 1. Re: Annotating Bar Graph
          Santiago Sanchez

          Hi Nathan,

           

          You can achieve this using a calculated field, similar to what you are thinking with the T/F statement! Using the example attached, you can create a calculated field with this expression:

           

          DATEADD('year', -2, { MAX([Order Date]) })

           

          { MAX([Order Date]) } returns the latest date in your dataset, so it's dynamic. Because we want to mark the last 3 years, we subtract 2 using the DATEADD function. We can call this calculated field [First Forecast Year].

           

          For the following steps to work, we need to use a continuous date field (a green pill) and this calculated field needs to be on 'Detail':

           

          setup.png

           

          Now, we can add a reference line on the Date Axis (right click on the axis to display the menu shown below).

           

          reference.png

           

          You can use a similar approach for your use case, but instead of DATEADD('year', -2, { MAX([Order Date]) }) you can use DATEADD('day', -6, { MAX([Order Date]) })

           

          Hope this helps!

          1 of 1 people found this helpful
          • 2. Re: Annotating Bar Graph
            Nathan Fowler

            Thanks for the help but the answer didn't me get quite where I wanted.

             

                1. Is there a way to do this without the order date pill in columns being continuous?  This is mainly a formatting thing and related to #3

                2. The reference line (profit per customer) no longer shows per pane.  This was not in the original question but became a problem when I was working with your solution@@

                3. There is a date added before and after the dates in the table (Dec 25 and Jan 1)

             

            Again thanks for your help as I am on the right track. I reattached the workbook.

             

            • 3. Re: Annotating Bar Graph
              Santiago Sanchez

              Happy to help, Nathan! Reference lines need an axis so we will need to use a continuous date field if we want to follow this approach. An axis by default also shows data before and after what's on the data set, as you've described. Because your dates are bound to change, I don't think we can fix this on the Axis formatting, unfortunately. However, there may be something we can do for point 2. What if instead of using a reference line you use label? Dragging [Profit per customer] to the label card, should give you the same effect.

               

              Another alternative, closer to the formatting you're describing (but a little bit of a hack in my opinion), can be achieved using a dual axis. You can create a calculated field that returns a 1 if its a forecast period, or 0 otherwise:

               

              [Forecast - Dual Axis] = IF [Order Date] >= [Forecast] THEN 1 ELSE 0 END.

               

              You can then drag that field to rows and create a dual axis. After that it's a little bit of formatting, specially on the secondary axis setting the range to a fix length between 0 and 1. An modified workbook is attached.

               

              axis.png

               

              Let me know if you have any questions.

              • 4. Re: Annotating Bar Graph
                Nathan Fowler

                I went ahead and marked the answer as correct because it is correct with what I was wanting for that particular graph.  I ended up using the hack and created a "Forecast" calc field = DATEADD('day', -6, { MAX([Order Date]) }) and then added the "forecast- dual axis" field = IF [Order Date] >= [Forecast] THEN 1 ELSE 0 END as a max in the row shelf.  Once the formatting was done it looks exactly as I envisioned.

                 

                Is there a similar way to complete this without the dual axis?  On another graph that I am trying to do the exact same thing I already have a dual axis with a bar graph and a line graph.

                 

                Thanks again for your help.

                • 5. Re: Annotating Bar Graph
                  Santiago Sanchez

                  Glad to hear it worked! No worries.

                   

                  I'm curious to hear from other members of the community about alternatives too. However, one thing that came to mind while looking at this was that, if we are flexible on the formatting, we could simply drag the first calculation into the 'Color' card. This would give each bar a different color, one for actual and one for forecast. While the view will be different, the effect for the end user to identify both sets of bar as different concepts, will be the same.