Training

How to Work with Time in Heat Map Explorer

In Heat Map Explorer, you can visualize change over time by comparing two periods or a series of periods. We’ll show you how to do both.

Comparing two periods

We’ll use NASDAQ 100 stocks to view the percentage change in share price between the previous close and last trade.

In the spreadsheet below, “Prev Close” and “Last Trade” represent the share price in the last two time periods; “% Change” represents the relative difference between these two time periods.

Spreadsheet with 2 time periods

Two time periods: "Prev Close" and "Last Trade"

In Heat Map Explorer, we map “% Change” to a “+/-: Red-Green” color scale which maps decreases in price to red, and increases in price to green.

Percentage change is represented by a color scale

Percentage change is represented by a color scale

Now we can see that all the stocks in Semiconductors dropped in price, while the stocks in Software & Programming were mixed.

Percentage change in share price between two periods

% change in share price between two periods

Comparing a series of periods

We want to visualize the performance of sales reps over a year. We’ve tracked monthly sales for each rep in a spreadsheet.

1.  Review the spreadsheet layout

First we need to see how the spreadsheet is laid out. The example below shows how this data often appears: each row is a sales rep, and each column, a time period (in this case, a month).

Before editing spreadsheet

Before editing spreadsheet

In Heat Map Explorer, each row in the spreadsheet must represent one time period (1 month). In the edited spreadsheet below, the month, rep, and sales amount for that month appear by row. Note, too, that numbers have been prefixed to each month (e.g., 01-Jan, 02-Feb, …, 12-Dec) to preserve calendar order in Heat Map Explorer.

After editing spreadsheet

After editing spreadsheet

2.  Import into Heat Map Explorer

Launch Heat Map Explorer, and then import the spreadsheet. (For steps, see How to Import Data from Microsoft Excel.)

3.  Display data

On the Data tab, check the Size, Color, and Label fields. For this spreadsheet, we select Performance for size (the better the sales performance, the bigger the size) and Variance for color (better than the target is green; worse than the target is red). For the label, we choose Actual to display the dollar value of the actual sales. Since we’ll be grouping by salesperson and month, this information will already be displayed in the group headers and doesn’t need to be used in the label field.

How to display data

How to display data

4.  Group data

We want to group data first by sales rep, then by month.

How to group data

How to group data

5.  Change the map type

We’ll choose Column Bar for the map type to display the data in rows and columns, enabling us to easily see changes over time.

The first level of grouping, sales rep in this case, gets displayed as rows. The second level of grouping, months in this case, gets displayed as column. The bar in each month represents the rep’s performance (sales amount), while the color of the bar depends on whether actual sales are greater than (green) or less than (red) targeted sales for the month.

As we can see, Jan’s performance over the 12-month period was consistent. Phil’s performance, on the other hand, decreased steadily, then sharply. The lack of data in November and December probably means he was fired. Sam’s performance is inconsistent, with peaks every few months.

Sales  rep performance by month

Sales rep performance by month

Next steps

Have you learned how to visualize change in data over time? Let me know if you’ve found this post useful.