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)?
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 .
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.
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.
Good luck with the bathroom.
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.
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.
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