Excel filterable Heatmaps for Asset Condition over time online

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

A method for showing condition heatmaps is using Excel to display the condition of the elements over time. Another post Heatmaps for visualising exterior and interior space conditions uses D3.js heatmaps.

The good part of this is that you can use filters to explore the information and drill down into the data. Video (4min)

I used Knime to show the condition of the elements working on the set Base Life of the elements and their remaining life. This data is based on an existing survey of the properties and with a large dataset.

After calculating the element conditions over a 26 year period, the conditions (1 to 5) are shown as columns in the spreadsheet.  I then used the conditional formatting tool in Excel to colour the cells based on the condition code.

Uploading the Excel file to OneDrive one can embed the excel file into a web page, freezing the Headers of the columns and adding filters, so that the excel sheet can be displayed online.

 

The data can also be displayed as a pivot table with slicers, see below.

 

As you can interact with the data, the next level is to build a Dashboard so that key metrics can be displayed. So key metrics need to be defined and possibly some charting methods used to display an overview would be good. For another day.

Exporting issues

One issue that I have been challenged with at this time is, after filtering the data on the web page , exporting it to add to a report. I haven’t found an elegant solution to this yet apart from a screenshot (Although I did experiment with screenshot, save to PDF and save PDF to Excel but data was poorly structured after the translations).

When I was testing Tableau Public this was an issue that disappointed me in that you could only save the original dataset, not the  filtered information that you’d developed for the charts/maps, which was annoying as I would have liked the tabulated backup to the charts/maps with the settings I had used.

The other method is to allow people to download the Excel file, in which case they can do reporting on their desktop.

Their is the ability to password protect the data, such that only approved users can access the data, in that case giving them access to download the data is ok.

It is still not as elegant as exploring the data on the web and being able to save scenarios there to report on.

Speed

The initial dataset was only 1162 rows of data, and I did not notice much more of a lag with this larger dataset when using the filters to explore the data.

I should try and extend the dataset to a larger number of rows and see how that performs.

Add a Comment