This week’s #WOW2022 challenge required us to build an horizon chart. While I’m familiar with them, I haven’t had to build one before.

Luke provided references to a workbook and blog post as part of the challenge. I was also aware that Marc Reid had recently published this blog post on the subject and that in turn referenced this Tableau guest blog post by Yves Fornes. Reading through them both I hoped I’d have the clues I’d need to build the chart.

Note – the blogs referenced all give a good explanation on the process we’re trying to achieve with the horizon chart (divide into equal sized layers and ‘merge’), so I’m not going to spend time repeating that in this post.

Unfortunately, despite a good start, I couldn’t just couldn’t quite manage it – I tried both Marc’s and Yves techniques and also downloaded Joe Mako’s workbook and tried to follow his method, but I just couldn’t get the display to match up, and there were minimal clues in Luke’s published solution.

My fellow #WOW participant Rosaria Guana also pinged me, as she had got further than I had, but wasn’t matching Luke’s solution either. With some assistance from Rosario I managed to build the solution that matched hers and seemed the closest fit to Luke’s. As yet we can’t figure out where the discrepancy may lie… maybe we’ve overthought the issue… who knows.

For now though, I’ll step through the solution as per my published workbook.

Sorting out the dates

The data needs to be segregated into decades, so first I created

Decade

In the requirements, it mentions using data from the 1980s onwards, but the solution only presents 1990s onwards. I interpreted this as a typo in the requirements, so I added a Data Source Filter (right click data source > Edit Data Source Filters), and added Decade excludes Null to eliminate all the unnecessary rows.

The chart shows 10 years worth of data at the day level across the x-axis. We can’t just use the existing Date field to plot as this will extend for 30+ years. Instead we need to ‘baseline’ the dates to a fixed set of 10 years.

Firstly we need to know what year in the decade it is (ie the 1st, 2nd, 3rd year etc).

Year Index

RIGHT(STR([Year]),1)

This returns values of 0, 1, 2, 3… or 9

I then create the years I’ll baseline my dates against – this can start at any year. I chose 1980.

Baseline Year

INT(STR(198) + [Year Index])

So this will result in years 1980, 1981, 1982… up to 1989.

I can then create a new date which I’ll use to plot against

Baseline Date

MAKEDATE([Baseline Year], MONTH([Date]), DAY([Date]))

If we pop some of this data into a table, you can see how the Date field relates to this revised date

Normalising the Arctic ice (NH) value

The NH values need to be adjusted so that they all sit on a scale of 0-1. This means the date with the lowest NH value across all the years will have a value of 0, and the date with the highest NH value across all the years will have a value of 1, and all the values in between will sit proportionally between. To compute this we need

Max NH

{FIXED : MAX([NH])}

Min NH

{FIXED : MIN([NH])}

Normalise NH

(AVG(NH) – SUM([Min NH])) / (SUM([Max NH]) – SUM([Min NH]))

take the difference between the NH value and the minimum as a proportion of the difference between the max and min values. This gives us the scale we need – the image below is sorted based on the Normalise NH value descending (so the dates aren’t in order)

Creating the Levels & the chart

All of the above I managed with no issue. It was this section that I struggled with 😦 As mentioned I tried all sorts of calculations, and after discussions with Rosario, the calcs I was using based on Yves blog post came closest…

We’re aiming to get bands which are 20% (0.2) in width.

Range 0.8-1

IF ([Normalise NH]) > 1 THEN 0.2
ELSE
IF ([Normalise NH])<0.8 THEN NULL
ELSE ([Normalise NH]) – 0.8
END
END

If the normalised value is over 1 then draw a mark at 0.2, our band size (there won’t be any values, but I did this for completeness). Otherwise if we’re below our lower threshold of 0.8, don’t draw anything, else as we’re within our specific range, draw a mark at the point which is the difference between our normalised value and our lower threshold of 0.8 ( ie a record with a normalised value of 0.91 will plot at 0.11).

On a new sheet add Baseline Date as a continuous exact date to Columns and Decade to Rows. Then add Range 0.8-1 to Rows too. Change to an Area chart.

Now let’s build the next level based on the same principals

Range 0.6-0.8

IF ([Normalise NH]) > 0.8 THEN 0.2
ELSE
IF ([Normalise NH])<0.6 THEN NULL
ELSE ([Normalise NH]) – 0.6
END
END

As you can see this is very similar to the above calculation, but this time, we will have values greater than the upper threshold of 0.8, which will all be plotted at 0.2.

Drag this field on the Range 0.8-1 axis and drop when you see the ‘two column’ symbol appear. This will make the chart a Combined Axis chart and Measure Names and Measure Values will automatically get added to the view. Colour the Measure Names accordingly, and up the transparency on the Colour shelf to 100%.

This looks very close to what we need, but you can see the axis is plotting values at >0.2. This is because the measures are stacked on top of each other. We need to turn this off via the Analysis > Stack Marks > Off menu.

When you do this, it’ll look like some marks have disappeared, but they are just hidden behind the other marks. Use the Measure Names colour legend on the right to drag and re-order the options listed.

Let’s now add the next level

Range 0.4-0.6

IF ([Normalise NH]) > 0.6 THEN 0.2
ELSE
IF ([Normalise NH])<0.4 THEN NULL
ELSE ([Normalise NH]) – 0.4
END
END

And drag the field into the Measure Values box. For the purposes of display at this point, I have coloured this measure a light grey, when it should be white (but that won’t show up at this point).

Now if we continue with the pattern and create similar calculated field for the next 2 ranges, we end up with something that isn’t right… too much red, and white in places we shouldn’t have white… 😦

This is where I started having discussions with Rosario. I could see by observing Luke’s solution that the dark reds were ‘inverted’ ie looked like humps coming ‘down’ from the top line, rather than humps ‘going up’ from the bottom line, but I was stumped where I needed to go next. Rosario confirmed this was her thinking too, and provided alternative computations for the light and dark red levels.

Range 0.2-0.4

IF [Normalise NH] < 0.2 THEN -0.2
ELSE
IF [Normalise NH]<0.4 THEN [Normalise NH] – 0.4
ELSE NULL
END
END

If we’re below the lower threshold, plot at -0.2 (minus 0.2) instead. If we’re within the lower and higher threshold, plot the difference between the Normalise NH value and 0.4 (which will be a negative number), otherwise plot nothing.

and finally for the last band

Range 0-0.2

IF ([Normalise NH]) < 0.2 THEN [Normalise NH]-0.2
ELSE NULL
END

Add this to the view, but you’ll need to change the order, so this measure is sitting above the Range 0.2-0.4 one.

Ok, so the shapes look like they might be correct, although we are missing some dark red in the 1990s compared to Luke’s solution, and as mentioned at the start, we don’t know where the discrepancy comes from.

So how do we make this all be ‘above the line’.

Drag another instance of Measure Values onto Rows so it’s sitting next to the existing one.

The make the chart dual axis BUT DON’T synchronise axis.

Instead, right click on the left axis to edit it, and fix the axis from 0-0.2

Then right click on the right axis to edit, and fix this from -0,2 to 0.

Set the colour of the Range 0.4-0.6 back to white, and you should have

Now you just need to

• add tooltips
• Edit the date axis and fix from 01/01/1980 to 31/12/1989
• hide all 3 axis
• remove row gridlines
• remove column dividers
• hide the null indicator
• hide field labels for rows
• adjust the font side and alignment of the Decade header

My published viz is here.

A few days later…

Since authoring this blog, Luke has made his workbook available to download. I have looked at it, and the calculations he uses are much much simpler. However I’m still struggling with the concept and not fully convinced with it all. I have played around with the workbook, and try to step through based on how the horizon chart is described in the various blogs, but I’m not entirely sure I know what the right answer might be in order to know whether what I’m seeing is actually correct or not. Feels like this is one I could do with a face to face discussion over, and ultimately is probably one for another day. ..

Happy vizzin’!

Donna