4 Replies Latest reply on Oct 23, 2015 11:43 AM by Troy Brommenschenkel

    Dynamic bullet graph distribution and reference line

    Troy Brommenschenkel

      Hello,

       

      I am attempting to create a bullet graph with a dynamic reference line (average) and distribution.  Essentially there are multiple "events" that are calculated out of a per 10,000 rate.  I would like to show the latest month for each event with a stoplight distribution showing if the rate per 10000 fell into the green, yellow, or red area.  My problem is that for some events the yellow area disappears.  Also when I try to filter the information for the current month, it no longer calculates the rate off of previous months/years information. 

      In simple terms I would like to turn the data trend charts into a 1 month snap shot bullet graph.

       

      The reference line has the same problem, it will just show up on the last value rather than calculate for previous values as well.

       

      I hope I have explained well enough?  Here is what I am working with.

        • 1. Re: Dynamic bullet graph distribution and reference line
          Simon Runc

          hi Troy,

           

          Just so I'm clear on what you are after, can I just check my understanding?...

           

          So you want your Bullet Chart, as it is, but that the Average and Distribution is based over all the months and years, and so not a different one for each Month? If that's the case then by setting the scope of your Reference Line/Bands to 'Entire Table' and not 'Pane' this should do the trick....I've done this on the attached.

           

          Do you also want to only show the last month? but want the Reference Lines/Bands to reflect the entire data-set?...a bit trickier, but should be possible.

          • 2. Re: Dynamic bullet graph distribution and reference line
            Troy Brommenschenkel

            Hi Simon,

             

            Thanks for your reply!  This is close to what I am after.  I am looking to only show the last month, but still calculate with the entire data set as you stated.  Thank you for helping me figure out the reference line/bands!

            • 3. Re: Dynamic bullet graph distribution and reference line
              Simon Runc

              hi Troy,

               

              Glad we're getting there...So originally I thought I could take advantage of Tableau's order of operations and make the Month Filter a Table Calc...Due to Table Calc filters being performed last this generally has the effect of filtering the view and not the underlying data...However this didn't seem to work (I think I need to do a bit of R&D on how Tableau calculates Reference Lines...so a bit of learning for me - this is why I love the forums!)

               

              I was then very much regretting my...

              "Do you also want to only show the last month? but want the Reference Lines/Bands to reflect the entire data-set?...a bit trickier, but should be possible."

               

              However a bit of LoD knowledge and the fact that prior to Tableau 8 we had to build up our own Box and Whiskers from Reference Lines/Bands...and it came to me!!...phew!

               

              So my, a bit tricky, was correct!

               

              So the average was the easiest, so we'll start with this. With LoD we are able to aggregate a measure, but then have it against every row in the data. So by creating the Average as an LoD I can put the Total Data average against every row, and then even when I filter out the other months, the December month still has the full data set value. So my formula for 'Month Count per 10k (Trending) - LoD for Ref Avg' is

              {FIXED [Dashboard Event],[Fleet],[Event]: AVG([Month Count per 10k (Trending)])}

              I can then bring this into my detail shelf, and that gives me access to it as a reference line.

               

              Now for the Standard Deviations the formulas are pretty similar

              'Month Count per 10k (Trending) - LoD for Ref  - SD'

              [Month Count per 10k (Trending) - LoD for Ref Avg]

              +

              {FIXED [Dashboard Event],[Fleet],[Event]: STDEV([Month Count per 10k (Trending)])}

               

              and for 0.75 SD

              'Month Count per 10k (Trending) - LoD for Ref  - 0.75 SD'

              [Month Count per 10k (Trending) - LoD for Ref Avg]

              +

              ({FIXED [Dashboard Event],[Fleet],[Event]: STDEV([Month Count per 10k (Trending)])}*0.75)

               

              I then bring these in to my detail shelf, so I can access in the Reference Line section (btw I've used MIN as my aggregate...as the values are a single value for every row, at the  [Dashboard Event],[Fleet],[Event] level, the aggregation is irrelevant - I tend to use MIN out of habit as when it doesn't matter this is often the most efficient aggregate for many databases)

               

              Now the 'tricky' bit. As I can't use the 'pre-built' SDs bands, I've brought these in a Reference Lines or Bands and Coloured above or below to get the 'Stop-Light' effect (thank god for the new Colour Picker!!)

               

              If you hover over the boundaries, you'll see in my 'Bullet Trend SR - Single Month Global Refs' tab, that is filtered to December, the Avg,0.75-SD, and 1-SD are the same as the 'Bullet Trend SR - Single RefLine/Band' tab where we haven't filtered the months.

               

              Hope that all makes sense, but if any of it doesn't (or doesn't do what you need) please post back.

               

              btw I built my LoD from [Dashboard Event],[Fleet],[Event] as these are the Dims in your view...If any of these isn't needed, or only contains one value you can remove and make the LoD a little faster.

               

              ...nice little challenge!

              • 4. Re: Dynamic bullet graph distribution and reference line
                Troy Brommenschenkel

                Thanks Simon!  This is exactly what I was looking for!  So amazing!  Thanks again.