11 Replies Latest reply on Mar 16, 2012 10:02 AM by Jonathan Drummey

    Static Reference Line

    Alan Kwan

      Hi all,


      I have 2 containers, first one with some business units and the second one with some metrics.


      I am using the first container as "Use as filter", so when I click on a business unit the corresponding metrics will change on the 2nd container.


      I have a reference line on the 2nd container (metrics), but it fluctuates whenever I select a business unit as a filter.


      How do I fix the reference line to be static, so that the "new" metrics after I clicked a business unit to filter, will compare to the "all business unit metric reference line?"




        • 1. Re: Static Reference Line
          Tracy Rodgers

          Hi Alan,


          You can add a static reference line by choose Constant in the drop down box in the Add Reference Line dialogue box where the value can be typed in. This allows you to choose the desired value and will not be altered no matter what filters are applied to it. Hope this helps!



          • 2. Re: Static Reference Line
            Alan Kwan

            Hi Tracy,


            That is a solution but not exactly what I am looking for. Because my data is populated once everyday, so the ALL business unit reference line should fluctuate. By using a constant, my reference line will only be set to that point, and I will have to change it everyday.


            Say the total average of all my business unit is x (it might be x + 1 or x - 1 tomorrow), and when I select a business unit, I want the reference line to remain as x so that I can compare the average of the selected business unit to ALL business unit average in total.

            • 3. Re: Static Reference Line
              Jonathan Drummey

              Hi Alan,


              The easiest option is to duplicate your connection, then set up the "All business unit reference line" to point to the correct field in the secondary connection. Verify that you have the correct fields in the view so the secondary is joining on what it's supposed to, and not too much or too little - you can tell by the orange chain link icons in the secondary.


              The Action Filter will then apply to the primary datasource, while the secondary datasource remains untouched.



              • 4. Re: Static Reference Line
                Alan Kwan

                HI Jonathan,


                I dont' quite understand what you are saying after the duplicate your connection part. Say I have 4 (40 to be exact)KPIs to measure as in the picture attached, each KPI has a target(Constant) to meet, how do you setup  the reference line for each?

                Thanks, Alan


                • 5. Re: Static Reference Line
                  Jonathan Drummey

                  I've attached an example of what I was describing, although I'm not sure whether it'll work in your case. In the graphic that you posted, it looks like your KPIs are set up as different dimensions of the same measure. So, I used the Coffee Chain DB sample data with the Sales measure and Market as the dimension to have something somewhat similar. (This is a case where posting a packaged workbook with some sample data for a few KPIs would be useful.)


                  I put SUM(Sales) on the Rows shelf, Date on the Columns Shelf, and Market on the Color shelf. Then I duplicated the data source (that's the Sample - CC for Global datasource), and dragged Sales from that datasource onto the Level of Detail shelf, which defaults to an aggregation of SUM(Sales). Now create a reference line using SUM(Sales) from the secondary datasource. This average will be roughly $25,000. Then I used the Data->Edit Relationships... menu item to delete Market as one of the relationships. Now the Average Sales reference line is roughly $100,000. In the dashboard that I created, this $100,000 number is independent of what Market you select.


                  The part that I'm not clear on is that do you want that larger number on your dashboard, or would you want something more like the $25,000 number that is a per-Market average? In the latter case, I'm not sure how to set that up given how your data seems to be formatted.



                  • 6. Re: Static Reference Line
                    Alan Kwan

                    I am looking for the per-Market Average. In your example, then I would be looking for a reference line for each market, for example, South has a reference line (target) of $8000, East has a reference line(target) of $10000...etc.

                    • 7. Re: Static Reference Line
                      Jonathan Drummey

                      Even easier. Using the same steps I outlined above, only not bothering to remove Market from the relationship, gets you what I think you're looking for. See the v2 Dashboard in the attached.

                      • 8. Re: Static Reference Line
                        Alan Kwan

                        Getting close to what I need now!!!! But I want to have more than one reference lines, is that possible (see attached picture)


                        • 9. Re: Static Reference Line
                          Jonathan Drummey

                          Since the goal is to have a "reference line" for each Market, there are a couple ways of doing this. The really messy way would be to create a calculation for each Market that would hide itself if that Market had been filtered out of the view, then add each calculation as a separate reference line.


                          The easier way is to use the same duplicated secondary datasource I used in v2, where the blend is on Date and Market. I then:

                          1. Created a calculated field in the secondary datasource called Window Average Sales with the following formula: WINDOW_AVG(SUM([Sales]))

                          2. Dragged that field to the Rows shelf.

                          3. Click on the green Window Average of Sales pill and choose Dual Axis.

                          4. Right-click on the Window Average of Sales axis and choose Synchronize Axis.

                          5. Click on the green Window Average of Sales pill and choose Mark Type->Line. This will change the Marks Card.

                          6. On the Marks card, click on the Color drop-down to change the transparency of the line and the thickness.


                          See the attached for an example.

                          • 10. Re: Static Reference Line
                            Alan Kwan

                            Thank you Jonathan this is great help, last question, is it possible to have a label for each line? Say, I want each line to have "Target = x%"


                            Many Thanks!

                            • 11. Re: Static Reference Line
                              Jonathan Drummey

                              Based on the packaged workbook above, you can create another calculated field with something like:


                              "Target="+STR(ROUND([Window Average Sales],0))


                              And put that field on the Label shelf for the Window Average Sales marks card. Tableau defaults the Compute using... settings to what's already used for Window Average Sales in the view, so you're all set. You can then click on the Label drop-down to set the Mark Labels to only appear at one end of the line.