Rio Olympics – Medals Treemaps

Well, the Rio 2016 games have finished and we now all need to find something else to watch on TV. As always at the Olympics there was plenty to entertain and inspire. After the London games in 2012 we blogged showing the medal distribution using Treemaps. We’ve updated that for 2016 below with the corresponding 2012 equivalent:

CBS16

CBS12

The charts are split by country, and then sport where the size of the tile represents total number of medals, and the colour saturation represents the number of Gold medals. We can see immediately that the US retains a significant lead over the other nations, and also that roughly half its medals overall were won in Swimming and Athletics. Great Britain and France have seen their relative medal positions strengthen in the four years. It’s difficult to see the breakdown for countries with smaller numbers of medals, but the interactive version can of course be drilled to additional detail and we’ll make that available in the coming weeks.

Looking at things split by Sport then by country it’s as below:

SBC16

SBC12

Athletics and swimming have the most events and hence the most medals and largest presence on the Treemap. USA dominates both categories across both London and Rio, with an even stronger grip on athletics in Rio. Elsewhere China rule the diving boards, winning 7 of  the 8 events in Rio.

Team GB again did spectacularly well in Rio, and as a British company we can allow ourselves a slight bias in our coverage (a roundabout way of saying the remaining charts are just about the British team). Firstly we’ve brought the 2012 and 2016 data for GB together into one treemap as shown below.

GBNI_1612

While the mix of sports is slightly different, and in both games the team won medals across 19 sports, the core strengths remain fairly consistent. Despite that, there are some interesting movements. Gymnastics and swimming have shown the biggest improvements between 2012 and 2016. Cycling (all cycling disciplines grouped) had the same number of medals in total, but 2 fewer gold. Having said that when you start from such a high base even being close is success – when other teams are videoing your warm up / stay warm routines it’s safe to assume you’re doing something right!

Last but not least, a column chart showing overall GB medals by discipline across the two games – if you need a binary sport by sport comparison rather than contribution to total the classics still tell it best.

GB_c_1612

 

 

 

Dynamic Tooltips / Mouseover

Tooltips can be a useful addition to reporting, often used to display additional context or information which you don’t want to have permanently visible in the main body of the report. When the user sees a value of interest they simply hover over the cell and a popup displays the additional detail. The additional information can be anything required, as long as it can be retrieved from an Excel cell (or direct from the cube). Examples could include some textual information or sales for the same period last year.

Implementing this in XLCubed reports is straightforward, albeit not immediately obvious. Tooltips are implemented using the XL3Link() formula, most commonly used for in-context navigation within a report, where the focus is moved from one sheet to another while passing a parameter. A special parameter, introduced in version 8.1, extends XL3Link() for tooltips as explained below.

The syntax for XL3Link is:

XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] )

LinkLocation: where the focus would normally jump to on clicking the cell. This can be left blank if you just want a tooltip.

Friendlyname: what is displayed in the cell. This could be static text (“i”) or an Excel formula referencing other cells in the workbook as needed.

LinkType: can be left blank for Tooltips.

To specify a tooltip, set Range1 to the value “XL3Tooltip”, and Value1 to be the content you want displayed in the tooltip. Value1 can be static text or can reference other cells as required, which means you can display other values which are also dynamically retrieved from the cube based on slicer selections etc. If the report is variable in length, the formula can be added into an XLCubed grid calculation and the Value1 parameter could use a vlookup to ensure a match on the appropriate data element.

The simple example below shows the basic approach, and the result also works when published to XLCubed Web.

 

s1

The extended example below shows combining several additional data fields. The line breaks are achieved using Alt + Enter.

s2

Thanks to our colleague Norbert Engelhardt at pmOne who blogged on this point recently in German and prompted this piece, basically an English version of the original:

XLCubed Insights #4 – Mouseover Funktion in XLCubed Berichten