Creating a Heatmap / Matrix

[#1]

Hi,

I am looking to create a risk-type heatmap based on a data column with values like these:

1A

3B

4C

2A etc

I would like to plot them on a chart that looks like below. The cells should contain a count of the various values (e.g. '1A') and should be plotted by number and letter.

It would also be desirable to colour the cells dynamically based on the count value. For example to highlight a cell in red that was above 15% of the total.

(Impact) 4 3 2 1		1	1	4
		2	3	1
		9	5	4
		3	2	10
		A, B, C (Possibility)

I am a newbie, so any help that you could provide would be much appreciated!

Thanks

Al

[jagan]

HI,

Can you attach the sample data file.

Regards,

Jagan.

[#3]

Hi Jagan,

Absolutely. Apologies I should have included this with my original post.

Sample data file attached.

Regards,

Alan

[jagan]

Hi,

I got your file, but I need some clarification.

Let me know what is the field name in your file for Impact, Possibility and what are values you are showing the report for example 1, 1, 4,  2, 3, 4 etc.

(Impact) 4 3 2 1		1	1	4
		2	3	1
		9	5	4
		3	2	10
		A, B, C (Possibility)

Regards,

Jagan.

[#5]

Hi Jagan,

The field name for Impact and Possibility is 'GrossRisk'. This field contains two characters, a number followed by a letter. The number dictates the Impact and the letter dictates the Possibility on the example grid.

So I would imagine I need to split the two characters into two separate fields so that they can be used. If that is the case, should this be completed in the script or within an expression?

The values that I am showing in the example (i.e. 1, 1, 4,  2, 3, 4 etc.) are fictitious representations of the counts that I would like to see. So, for example the 9 represents a count of the number of '2A' values. (Please note that the values in the file do not relate to this example).

Hope this helps.

Al

[jagan · Accepted solution]

Hi,

Please check the attachment, hope this helps you.

Regards,

Jagan.

[#7]

Hi Jagan,

Many thanks - that works a treat!

Cheers

Al