7 Replies Latest reply on Feb 8, 2013 5:29 AM by Jonathan Drummey

    Dynamic Custom Reference Line

    Chris Tsui

      Hi All!


      I have been working on a dashboard for some time now and the community here has been very helpful with many of the elements I struggled with during its development.  I've "almost" got it done and am at the last part where I'd like to plot a dynamically calculated reference line based on a parameter


      What I'm trying to do is create a dashboard where my users can view store performance over time.  I intially setup a crosstab which would show store metrics per quarter filtered by Product Category.  The Metric would change based on a measure select parameter which I got help from here: (http://community.tableau.com/thread/119557)


      Its primarily a parameter that would "pick" a measure to represent.


      We then wanted to look at visual trending.  this is where I created a line graph over time for each store using the same parameter to select the measure.


      My users then wanted a "Category" average to see how the store performed against other stores irrespective of any of the other filter selections.  So to do this (after some reading) I made a second connection to the same data source. I tied the category filter to this new worksheet but no other filter.  I then used some table calcs within a modified metric parameter (with help from the community: (http://community.tableau.com/thread/122895) along with a very helpful article from Johnathan D (http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/) to create average calculations at the Grand total level. 

      (This is where I'm sure experts might have an easier way to acomplish this, but the numbers worked so I..."went with it")


      And we are now where I'm at


      What I was hoping to do was plot the values that I arrived at with the average calculations on the trending line graph I had.

      I attempted to use a  built in average line graph function (which I left in the packaged workbook) and it works well for all the metrics that use average.  However because I have one metric that uses Median (Delivery) the built in "average" numbers on the chart don't match up to my calculated median average number.


      So I guess after my long winded explanation, my question is.  Can I plot a custom reference line that changes based on the selected customized grand total metric value that I arrived at in my average calculations?


      If so, How?


      Once again, thank you everyone for the help!

        • 1. Re: Dynamic Custom Reference Line
          Chris Tsui

          So I gave this another shot and I'm not sure if i'm "closer"


          I was able to plot the Average line onto a chart, but when I tried to "Dual Axis" it together the the table calc used to plot the average would "break"  It was computing using the store number (copy) from my Average datasource, but when I Add the axis together, I'm no longer able to compute using the original data source field.  I changed the compute using to point to the store number from the original data source, but it doesn't seem to like that.  Its tantilizaingly close because I can see it all there   I just can't Jam it together.......... what a tease!


          Any suggestions would be appreciated!




          • 2. Re: Dynamic Custom Reference Line
            Chris Tsui

            I just re-read my previous post and I even confused myself!


            I believe what I was trying to say was that I created a worksheet of the two charts I wanted: Trend & Average Chart.

            I wanted to have a dual axis chart with both of these charts together.


            The trend chart is pretty straight forward with a "Quarter" x axis and a metric Y axis for each store.

            the average chart is similar but instead of using the metric pill I used a table calculation pill computing on a copy of "all store Numbers" to arrive at a "network average"


            When I tried to plot the table calculated pilll on the trend chart I was not able to use the store numbers pill from the average chart data source anymore.  I had attempted to use the trend "store number" data source, but it still didn't like that.  It also wouldn't be acceptable because the store number is a filter on the trend data source and would effect the calculations.


            In the end, the TLDR version of this is, based on the attached workbook above, I'd like to try and combine the trend and average workbook into one working graph where I have individual store performance based on a parameter selected metric with the average of all stores also displayed based on a parameter selected metric.


            Any help would be appreciated!

            • 3. Re: Dynamic Custom Reference Line
              Jonathan Drummey

              Hi Chris,


              I was looking at this yesterday and the intersection of a few different features makes this more difficult, I can understand you having difficulties!


              - Ordinarily, you can get the total/average group line plotted with the per store lines chart using a data blend (with the appropriate data relationships) or a table calculation.


              - For the median, the calc as created WINDOW_MEDIAN(MAX([Median DT])) is returning the median of the 4 or 5 stores per quarter. You'd need to increase the level of detail in the secondary data source in order to get that in the data blend, or use an extract to do TOTAL(MEDIAN([Median DT])). However, you can't increase the level of detail in the secondary data source in a blend, and TOTAL() won't work on blended data. So the solution there is to use table calculations to generate the group avg/total.


              - However, the Global Filters you have set up would cause the group total/avg calcs to return incorrect results because the filters would filter out data needed to compute those calcs. Ordinarily we'd turn to table calculation filters, but you're trying to use filters across the worksheets on a dashboard and table calcs can't be used in Global Filters or Filter Actions.


              The only workaround I've been able to come up with is to use the TOTAL(MEDIAN([Median DT])), and use parameters instead of quick filters for Type, QTR, and Store Number.



              • 4. Re: Dynamic Custom Reference Line
                Chris Tsui

                Thanks for your thoughts on this Jonathan.


                First off I'd want to thank you for your work on the grand total articles.  It helped immensly to get to the point where I'm at right now!


                Secondly, I'm going to explore the Total (Median(Median DT)) option.  My one concern is using a parmeter as a filter vs. a quick filter.  It works when using a parameter to filter for category and metric.  And it would probably work for type.  However i'm worried about the single value nature of a parameter and its application when used with QTR or store number. 


                I think this would make the trending and performance peice difficult if we only had single slect options for these two dimensions.  Do you know if there are ways around it?

                • 5. Re: Dynamic Custom Reference Line
                  Jonathan Drummey

                  I'm a bit stumped, here are a couple of ideas:


                  - Get rid of the text table. Then you'd be able to use table calc filters (which can be multi-select) on Type, QTR, and Store Number.


                  - Look at is to go back to the kinds of questions that are being asked that you're trying to help your users answer. There are 5 different controls on the dashboard, maybe there don't need to be so many options on one dashboard and there could be multiple dashboards for different purposes.



                  1 of 1 people found this helpful
                  • 6. Re: Dynamic Custom Reference Line
                    Chris Tsui

                    Hi Johnathan,


                    I went back to take a little step back and looked at the project.  I think I over complicated it and have started work on simplfying the "query" which should make the work on Tableau a bit easier.


                    This was stemmed from your initial comment about level of detail.


                    With regards to the median calculation, it was coming out of the data source "pre calculated" for each store and i realized that with the table calculation I was doing a Median of a Median value which resulted in inaccurate numbers (even though they looked right at first glance)


                    Also for the Customer Sat and Customer Response measures, they are not available at the order level of detail and thus I just filled every row with the same number (per category/type/qtr) and also did a datasource calculation of the average.


                    This made it MUCH easier to "avoid" table calculations for the average and I simply needed to do single aggregations on the order count average and the median delivery time as well as Max values on the averages for both customer sat and resp.  I then reverted to using your Part 2 Grand total formula of checking for Min/Max Store numbers to determine grand total.


                    Because my selected measure field was no longer a calculated field I was able to plot it (with a data blend) onto the existing trend graft.  I think I explained the steps I took properly, but if not I included what I "think" is where I wanted to be at the onset of this.


                    The numbers are a bit different given that I did most of the work manipulating the data source so that Tableau didn't need to do the heavy lifting, but I think they are more accurate to the source (vs. what might actually be displayed)


                    Thanks for your thoughts and suggestions Johnathan, it was very much appreciated!



                    • 7. Re: Dynamic Custom Reference Line
                      Jonathan Drummey

                      You're welcome!