I created a calc field with the following calculation:
IF [Order Date]<=#4/1/14# THEN 18000
ELSE 23000 END
Then I added MIN(Reference) to the Details shelf on the Marks card.
After, I created a reference line that calculated per cell, and it worked for me.
2 of 2 people found this helpful
How's this for you?
I've created the following calculated field [Ref Line]
IF [Order Date] < #01/04/2014# THEN 18000
and brought it into the detail pane, so I have access to it as a Reference Line. I then set the Scope to per-cell, and we have the changing line!
btw thanks for including the Tableau version in your post (and a workbook)...very helpful
...spooky! lets go with 'great minds think a like'
I think everyone is correct in using a calculated field to change a value based on the date.
This is what I used as part of the workbook you attached.
Calculated Field Reference Line:
CASE DATEPART('month',[Order Date]) WHEN 1 THEN 23000 WHEN 2 THEN 23000 WHEN 3 THEN 23000 WHEN 4 THEN 25000 WHEN 5 THEN 25000 WHEN 6 THEN 25000 WHEN 7 THEN 28000 WHEN 8 THEN 28000 WHEN 9 THEN 28000 WHEN 10 THEN 21000 WHEN 11 THEN 21000 WHEN 12 THEN 21000 END
ReferenceLine.twbx 1.2 MB
Does this only work using Discrete date? Can it also work for Continuous?
1 of 1 people found this helpful
...Unfortunately I don't think so (I hadn't really thought about than, but had a good play with a few settings, to no avail!).
I do have though a continuous solution, but it does use up a valuable dual axis (hopefully in your real world situation you aren't already dualled!)
So I've created the reference lines using a line chart instead (here's a far more elaborate creation of a reference band, not using the reference line feature! Educational Brain Teaser: Change the transparency of a reference band )
So I created 2 calcs
[Ref Line - Post April]
IF [Order Date] > #01/04/2014# THEN 23000
[Ref Line - Pre April]
IF [Order Date] <= #01/04/2014# THEN 18000
so both equate to NULL when not in range...btw I had to change the pre to a <= else the lines have a slope join between March and April.
I then arrange them as a measure values line chart and dual the axis (and sync)
...I'll have a bit more of a play with the actual reference line feature (you've got me intrigued now!) but I think the way that reference lines are calculated might prevent this...I'll let you know if I find anything.
Hopefully either you can use Discrete, or my other solution will do the trick!