#WorkoutWednesday 2019 Week 36 was set by Curtis Harris, and for me was a nice gentle challenge to finish up my post holiday catch up.

The full challenge is posted here, with the main requirement being to create a ‘custom axis’, which on hover, showed intersecting reference lines on the line chart, as shown below.

This challenge involves the use of 2 views (one for the axis and one for the line chart) and then Set Actions to drive the interactivity.

To build this, I created a calculated field to essentially store the month/year of each record

Month Order Date

DATE(DATETRUNC(‘month’,[Order Date]))

This is then custom formatted to m-yyyy to get the required presentation

The line chart then simply plots Month Order Date (set to continuous, exact date) against SUM([Sales])

Using Month Order Date, I then created a set, Selected Date Set, and just selected one of the values listed. It is this set that will get changed via the use of Set Actions later.

Selected Date

IF [Selected Date Set] THEN [Month Order Date] END

This field stores the date that has been selected in the set, and can then be added to the Detail shelf on the line chart (as a continuous, attribute), so it can be used as a reference line on the date axis.

Sales Ref

IF [Month Order Date]=[Selected Date] THEN [Sales] END

This field stores the value of the sales for the date selected in the set, and can also be added to the Detail shelf on the line chart, so it can be used as a reference line on the Sales axis.

Custom Axis

The custom axis simply plots Month Order Date on the columns with mark type of circle.

However the requirement states that if the month is the last month or the start of a quarter, the text should display rather than a dot. To do this I needed…

Max Date Month

DATE(DATETRUNC(‘month’,{FIXED : MAX([Order Date])}))

The month associated to the latest Order Date

Order Date Display

IF ([Month Order Date] = DATETRUNC(‘quarter’,[Month Order Date]))
OR ([Month Order Date] = [Max Date Month])
THEN
[Month Order Date]
END

If the month is also the same as the quarter, or the month is the last month, return the month.

Putting this field onto the Label shelf, centre aligning and allowing labels to overlap marks, almost gives the required affect….

I don’t want the dots showing through when the labels exist, so I created

Colour : Circle

IF ISNULL([Order Date Display]) THEN ‘teal’ ELSE ‘white’ END

and added this to the Colour shelf,and adjusted the colours to suit.

Now it’s just a case of putting the sheets together onto a dashboard, so we can then invoke the Set Action, which is sourced from the Custom Axis sheet on Hover, affects the Selected Date Set, and empties the set when the mouse is moved off (so causing the reference lines to disappear).

That’s the core features this challenge is testing. There’s a few other bits and bobs listed, which I haven’t gone into, so do let me know if there’s something you’re struggling with that I have mentioned.

My published viz is here.

Happy vizzin!

Donna