7 Replies Latest reply on Apr 25, 2018 4:32 AM by Simon Runc

    Mulitple Partial Dynamic Reference Lines

    Nick Warchol

      Hi All - wondering if anyone has a solution for creating multiple reference lines in a date/time line chart which are calculated and displayed for a specific date range rather than for the entire table (the specific date ranges are determined using parameters).  My gut tells me the solution lies with the reference line scope selection, but I've been unable to get it working properly.


      I've included screen shots below of Excel mock ups as well as an attached workbook in v10.2.


      Any help would be greatly appreciated.






        • 1. Re: Mulitple Partial Dynamic Reference Lines
          Simon Runc

          hi Nick,


          Probably me just being slow on the uptake! (been a long week!)


          Are you after a reference lines between your 2 conversion dates (16/03 and 25/03)?

          • 2. Re: Mulitple Partial Dynamic Reference Lines
            Nick Warchol

            Hopefully you're not delaying the start of your weekend by trying to solution this one...


            Anyway, what I'm after are two reference lines and the values of those reference lines are dependent on the parameters (Start Date, Conversion, End Date).


            For example, if the following parameters were selected,


            Start Date: 3/1/2018

            Conversion: Conversion 1 (the date associated with this is 3/16/2018)

            End Date: 3/30/2018


            One reference line would extend from 3/1/2018 to 3/15/2018 and the value would be the average of the times for that date range.


            The other reference line would extend from 3/16/2018 to 3/30/2018 and the value would be the average of the times for that date range.


            I'm able to get two reference lines to appear, but the requirement is to have them display for the associated date ranges and not across the full chart .

            • 3. Re: Mulitple Partial Dynamic Reference Lines
              Simon Runc

              Hi Nick,


              Oddly I find these challenges quite fun...certainly compared cleaning the bathroom, which is what is planned for me once I "finish" work today (thus the delaying tactic..."Yeah my boss is a real slave driver!")


              After writing back the penny dropped on what you were after. I think we can do this with Reference Lines, but it tricky to get the labeling right), so I've had a go using a dual axis. To be fair it's still quite complicated.


              Take a look at the attached, and let me know if those are the correct answers, and (probably next week...my wife is getting wise to Community vs Work-work!) I can go through how it all works.

              • 4. Re: Mulitple Partial Dynamic Reference Lines
                Nick Warchol

                That's exactly it.  So in the Ref Line # - Chart calculation you're defining the date range then calculating the average of the average times in that date range, correct?  I really like it.


                Thanks Simon!


                Good luck with the bathroom.

                • 5. Re: Mulitple Partial Dynamic Reference Lines
                  Simon Runc

                  So lost the "procrastination" battle...but I do have  a clean bathroom!


                  ...over the weekend I did think of a way of doing it with reference lines, rather than the method I've used here, which is that the "reference lines" are actually a line chart, which is dual-axis'ed with your line chart...in fact we could use Gantt marks to get the "dotted line" feel. But I'll stick with the solution I've done (let me know if you need that, valuable, dual for something else and I can let you know how we can do it with reference lines)


                  So yes...I've created 2 fields which only contain the values for each side of the Red Reference Line Date.

                  [Ref Line 1]

                  IF [Date] < [Conversion Date] THEN [Time] END


                  As there is no ELSE part the FALSE part equates to NULL and Tableau doesn't plot NULLs (they also don't get included in any average calculations, unlike zero). Now as we now have 2 calculations (one for each side of the red line), I need to use Measure Names/Measure Value to plot them, and as I can't use Dates with the Average aggregation, I use the "Time" measure. I then created a second set (which are datetime types), which I've used to Label the marks.


                  To get the reference line version of this working, we need to have your [Date (Param Calc)] as discrete, and set the scope to Cell. The Table Calculations can then make the value the Average of all marks, so for each date (each side of the ref line) return the same value (the Window Average). The problem with this is that we don't get much control on the Labeling and when the scope is set to cell, we'll get a label for every date (I have through of a way round this, but if this is doing the job...no need to add extra confusion)


                  Hope the calculations make sense, but let me know if not.

                  • 6. Re: Mulitple Partial Dynamic Reference Lines
                    Nick Warchol

                    Thank you for the follow up and further explanation – the calcs make sense.  I would be interested in seeing your method that would allow the line to be dashed similar to the format option available for reference lines if you wouldn’t mind sharing.

                    • 7. Re: Mulitple Partial Dynamic Reference Lines
                      Simon Runc

                      hi Nick,


                      So it a bit "swings and roundabouts"! As I said we need to make the date discrete in order to have the reference line stop at the boundary, when we use the scope of cell. This creates the problem of labelling


                      It does this


                      So I actually create the reference line twice. First with no label and set scope to cell and then second one with scope to Table, but I don't show the line (line - none) but do show the label! Where I'm hovering in the below image shows there is a reference line there, but we don't see it, and just use it for the label.



                      Now here's the "swings and roundabouts"...as we now have discrete dates, we can't set up the red reference line...so here I've created this with a secondary axis and a really thin bar. Let me know if you can't reverse engineer how I created this thin bar, to the height of the max avg time.


                      Hope that helps