5 Replies Latest reply on Jul 19, 2016 3:50 AM by Stephen Lavery

    Creating Multiple Reference Line from seperate data table

    Jessica Lee

      Hi all,

       

      I need to include reference lines as in the sample situation below:

       

      - I have a graph of sales (from table a) filtered by category and need two reference lines (one for breakeven sales, one for target sales)

      - These reference lines vary based on category according to a seperate table (Table b)

      - The final graph needed is the one shown (Final 1)

      - Would be even better if I could create this as well (Final 2)

       

      I cant seem to figure out how to do it with tableau.

       

      Table a

         

      YearCategorySales
      2010Furniture23
      2010Office Supplies30
      2010Technology25
      2011Furniture23
      2011Office Supplies27
      2011Technology31
      2012Furniture20
      2012Office Supplies21
      2012Technology29
      2013Furniture33
      2013Office Supplies34
      2013Technology33
      2014Furniture22
      2014Office Supplies22
      2014Technology34
      2015Furniture35
      2015Office Supplies23
      2015Technology22

       

      Table b

       

         

      CategoryBreakeven SalesTarget Sales
      Furniture2330
      Office Supplies2528
      Technology2529

       

      Final 1

       

       

      Final 2

       

        • 1. Re: Creating Multiple Reference Line from seperate data table
          Stephen Lavery

          Hi Jessica,

           

          I can help you get to Final 1. Just go to the analytics pain and drop out 2 constant lines. In here drop in your measure for breakeven sales and target sales:

           

           

          Unfortunetly I can't help you replicate final 2. Maybe someone else on here can help you. See the attached workbook for my solution. Note, I manually typed in my breakeven and target sales but you can drop in your measures here. That way when you change your category filter your constant lines will move accordingly

          • 2. Re: Creating Multiple Reference Line from seperate data table
            Jessica Lee

            Hi Stephen,

             

            Thanks for your help. I managed to create the constant lines but cant seem to link them to the measures so that they change by class.

            Do you mind also showing me how to 'drop in the measures' by class?

             

            Appreciate it! Thanks.

            • 3. Re: Creating Multiple Reference Line from seperate data table
              Stephen Lavery

              Hi Jessica,

               

              Sorry, it's actually a reference line you want to use and not a constant line. I took the numbers you originally posted and used them. You will need to put ATTR([Breakeven Sales]) and ATTR([Target Sales]) into the detail of your view. Then when you drag out a reference line you can use them as your value for the line:

               

               

              See the attached workbook and let me know if this helps.

               

              Thanks,

              Stephen

              • 4. Re: Creating Multiple Reference Line from seperate data table
                Jessica Lee

                Hi Stephen,

                 

                Thank you so much. That was very helpful and has helped me solve my problem.

                 

                However, I am still facing a slight issue. I have a seperate breakeven sales and target sales for the total (unfiltered) categories as below.

                 

                CategoryBreakeven SalesTarget Sales
                Furniture2330
                Office Supplies2528
                Technology2529
                Total2530

                 

                When I choose "all" for the category filter, there is no breakeven sales and target sales reference line.

                 

                Would you know how to show the 'total' breakeven and target sales when no filter is applied (ie. sales for all categories are graphed) ?

                 

                Thanks again. Your help is much appreciated.

                 

                Best Regards,

                Jessica

                • 5. Re: Creating Multiple Reference Line from seperate data table
                  Stephen Lavery

                  Hi Jessica,

                   

                  Sorry for the delayed response. I was having trouble logging in last week so I couldn't reply!

                   

                  I was able to add '(All)' by using a parameter. Is this what you meant?:

                   

                   

                  I did this by creating a parameter using category and manually typing in "(All)" as a parameter value:

                   

                   

                  Just type this in and move it to the top. Then create two new calculated fields for target sales and breakeven sales that look like this:

                   

                   

                   

                  Put this into the your review replacing the original ones and make them an ATTR.

                   

                  Next step is to create a filter for your category using the new parameter:

                   

                   

                  Drop this into the filter replacing the original category filter and only check the box for 'True'.

                   

                  Once you've done this you can show the parameter control. Now when you select '(All)' as the parameter value your sales and breakeven lines will show for all. See the second tab 'Final 1 (using parameter)' in the attached workbook.

                   

                  Kind Regards,

                  Stephen