8 Replies Latest reply on Apr 24, 2018 11:53 AM by mike.czyzewski

    Reference line starting point on worksheet

    mike.czyzewski

      Hi!

       

      I have attached a screenshot.

       

      I have built a view with a reference line which represents a monthly sales goal.  However, I don't want the reference line to start at Annual Goal or YTD.  I want it to start in January.  Is there any way to do this?  I played around with all of the options in the Reference Line area, but didn't see anything promising.

       

      Thanks!reference line.JPG

        • 1. Re: Reference line starting point on worksheet
          Joe Oppelt

          Can you upload a sample workbook?  I want to see how you created the x-axis the way your screen shot shows.  Depending on how you got it that way, you might be able to make the calc that feeds the reference line to be conditional.  When it's "Annual Goal" or "YTD" then set the value to null.

          • 2. Re: Reference line starting point on worksheet
            mike.czyzewski

            Hi Joe!

             

            See attached for twbx.

             

            Thanks for the help!

            • 3. Re: Reference line starting point on worksheet
              Joe Oppelt

              OK.  Bummer.  I was hoping you were running along a dimension, in which case we could null out the value of the reference line for specific dimension values.

               

              In fact you ALMOST have a data setup where we can do that.  If your MONTHS structure had one more value for "Goal Annual", we could make that into a dimension and you would be off and running.

               

              But tableau is going to treat all values in the MeasureNames/MeasureValues construct uniformly.  You won't get past that.


              So here's what I did.  I split the sheet into two sheets.  One part has the reference line, and the other does not.  I positioned them side-by side on the dashboard.  See Dashboard 2.  Note also that I added an extra reference line to BOTH sheets.  This forces a value on the sheet greater than the largest value.  (I made an assumption that the largest value will be either YTD or Annual Goal.)  Forcing that larger value makes the axis on both sheets align.  (For kicks, remove it from both to see what you would get.)

               

              Now, on Dashboard 2 I left the color legend for Sheet (2).  You  don't want that.  Look at Dashboard 2(2).  Here I added your original sheet and took its color legend for the dashboard and removed the little one.  Then I sized the original sheet to be just 1x1 pixels and floated it behind everything else.  It's on the dashboard, but nobody is going to see it.  But that lets the full color legend to be on the dashboard.

               

              You can do additional formatting, and you'll want to make sure that filters apply to all the sheets so that one select will govern all sheets.

              • 4. Re: Reference line starting point on worksheet
                mike.czyzewski

                Thanks, Joe!  That's a pretty clever approach.  I tried some stuff with lining up two different sheets yesterday and the issue I kept running into was that I could get the gridlines to match on MOST of the names, but there were instances where it would break.  On your Dashboard 2 (2), there are still moments when the lines become disjointed.  Is there any way around this?  I've included a screenshot:

                 

                 

                You mentioned above that, if I had a Goal Annual in my months structure, we could rig it differently.  Could you explain a little further what you meant by that?

                 

                 

                Thanks again for the help!

                • 5. Re: Reference line starting point on worksheet
                  Joe Oppelt

                  Yup.  You'll need to incorporate the monthly bars into the MAX calc to get the phantom reference line.  In your screen shot the max between YTD and Annual is 28K and the max in Mar is 40K.  Do you need help with that?  It'll be a multi-nested MAX(MAX(MAX(.... ))))))) to get the MAX among ALL the bars.

                  • 6. Re: Reference line starting point on worksheet
                    mike.czyzewski

                    I edited my post while you were replying, I think.  I'd like to explore the idea you mentioned about a Goal Annual in the months structure.  What did you mean by that?

                     

                    Thanks again for the assistance!

                    • 7. Re: Reference line starting point on worksheet
                      Joe Oppelt

                      From what I see, each row has all those individual columns.  If you added one more column and duplicated Goal Annual] in it, you would have 14 slots that you could pivot into a dimension.  (I assume you still need [Goal Annual] built under the [Goals] folder, thus I recommended duplicating it.)

                       

                      In the attached I duplicated your data source.  Some data source types allow Tableau to do the PIVOT operation, and others require you to do the PIVOT in the creation of the data source.  (The latter would be SQL scripts.)  Here I could do it in Tableau.


                      Sheet 4 shows how to use a pivoted structure.  (We can do things to get it to sort the way you want it to.)  For now I just wanted to show what it could look like.  And with this structure I can turn off the reference line for "YTD".

                      • 8. Re: Reference line starting point on worksheet
                        mike.czyzewski

                        Cool, thanks Joe!  I think I see what you did here, will recreate your pivot in my workbook, rebuild the view and validate results.  Thanks again!