background color on dimension not working

[#1]

I have to indicate the severity of an incident that happened on a specificy day by color

So my Dimensions are: Day, Region and Severity

My Expression is: count(distinct Indicident)

On my Dimensions, I add the following Expression on "Background" for "Severity"

if(Inc_EstimatedSeverityCount=5,RGB(123,7,16),if(Inc_EstimatedSeverityCount=4,RGB(207,2,0),if(Inc_EstimatedSeverityCount=3,RGB(255,124,6),if(Inc_EstimatedSeverityCount=2,RGB(253,255,8),if(Inc_EstimatedSeverityCount=1,RGB(128,255,0))))))

As you can see in the attached image, only severity level 2 has a different background (the numbers on the same row is Severity, while those below it is count. So for 13 Aug 2012 there was 3 x Severity 1 incidents, 1 x Severity 2 and 1 x Severity 4's)

Why does ONLY the middle (ie, Severity 2) change color?

No other colors changed at all. And I have a wide spectrum from 1 - 5 that needs to be indicated.

[Anonymous]

I think you have to use calculated color on the expression level rather than dimension in this case.

Regards,

Michael

[#3]

Yea I noticed, but it does look really crap if I do. If the color was prominent at the top, then it would be fine, but now the entire table is litered with overpowering colors.

If you have any alternative suggestions on how to display this I will be very greatful

[Anonymous]

Pivot tables are hard to control...  There only hint that maybe helpful in your serach for solution - the "correct" column is the only one that has values in all rows.  All other columns in this screenshot have null values.

Not sure it is related, but worth digging, I think.

Regards,

Michael

[#5]

Well, SHEQ incidents doesn't happen every day, if they did then the health & safety board would shut the business down. We need to monitor incidents per region though so we do act immediately if there's been a major incident so we can investigate.

Having columns blank for 1 region and it shows, only means there was an incident in another region. I don't think that can be helped unless I manually create pivot tables for each region, but then this whole exercise becomes redundant when we add a new location in our data. Hardcoding is not the way.