Treemaps

Treemaps are a new visualization available in Version 7.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 its 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 its children, but you may want to use another method, e.g. totalling 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.

Cube

When sourcing a treemap from a cube you get the usual member selector found throughout XLCubed.

You must put a hierarchy in the Plot area, and this defines the areas to be plotted.

The Header area performs the usual action of slicing the results, e.g. showing only data for a given time period.

The Behaviour tab allow you to control the data displayed.

The Values section allows you to pick the measures used for the size and colour of the treemap. Although measures is the default hierarchy, you can drop in a different one if you need to. From v7.5, you can also select the number formats for the values, although these are overridden by the Apply Cube formatting option, if that is selected.

The Split by section allows you to define a two level treemap. The second level can be a lower level of the hierarchy you chose to plot, or could be a different hierarchy entirely.

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. N.B. If you have nested areas then you can pick a range of several cells to contain the names at each level.
• 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.

Formatting

New in Version 8.1 you can specify the layout and formatting of the treemap text by using the "Text" setting when inserting a Treemap. These can be formatted using Custom number formats.

New in Version 9.1 you can specify %sizemeasure% and %colormeasure%.

From Version 9 you can specify the font style and colour in the format sheet.

To do this you set the "Item Name" for either the name of the Treemap (specified in the options form) or Treemaps to apply to all treemaps.

The Hierarchy and Member columns must contain *, and the format will be taken from the Data Format cell in the row.

Additional Data

Extra data fields can be added from a SQL or Excel source by referencing the column in the Text.

The reference shoud be %fieldName% or %valueN%.

• fieldName is the field returned in SQL, or the column letter in Excel.
• valueN should use a number as N, where 1 is size, 2 is colour, and 3 upwards are additional data fields in the data.