Treemaps

Treemaps are a new visualization available in V7.1 of XLCubed. They can show the relationship between two measures, and their variance across a dimension.

Treemap of Sales vs Profit

The above treemap plots Sales against Profit across the top ten states in the USA. In this example the size of the area represents the total sales, and the colour shows how profitable the sales in that area were.

In XLCubed treemaps can be sourced from a Table, and Excel range or from a cube.

Data sources

Table or Excel range

The data sources for tables and Excel ranges are quite similar.

In both cases you select a number of columns. XLCubed will use the last two columns as the size and colour values to plot, and other columns provide the member values.

You can have several member columns, in which XLCubed will allow drill-down into the data.

Example

If we start with the following data in Excel:

We can create the following treemap, which starts at the top level:

We can drill into the areas by double clicking to get more detail:

Data options

If you edit the treemap, you get some options to customise it's behaviour.

The Show second level option allows you to instantly see the make up of each outer area from its children. For example, in the above example we see the product category, and how each sub-category contributes to it. Drill and we can see the products in a sub-category:

The Total Size using and Total Colour using options allow you to chose how the data is aggregated. By default a parent's number is made up of the sum of it's children, but you may want to use another method, e.g. totaling a percentage using the average of the children.

Finally you can use the number format text boxes to set the number format used on the treemap. By default data sourced from an Excel range will use the number format set in Excel.

Common Options

• Gradient: Choose the colour scheme for the treemap. Allow you to reverse the colour order in cases there low numbers are good, e.g. losses.
• Layout: Allows you to change the method by which the areas are laid out.
  • Order by Size - Larger areas come first.
  • Natural Order - Attempts to keep the areas in the order they started in. This is the order they are found in tables and ranges, cube order when the data source is a cube.
  • Squares - Attempts to make the areas as square as possible, makes comparing sizes easier.
• Parent name position: Choose the title position when showing two levels in the treemap.
• Output range: Area to output the clicked area name. Allows you to drive another section of the report from the treemap.
• Link to: Use in conjunction with Output range, allows you to jump to the section of the report updated when the treemap was clicked.
• Show back/forward controls: Show the arrows used to navigate when drilling.