Conditional colouring of bar chart

[SteveDagg]

I have a problem with conditional colourings of bars in a chart being ignored under certain circumstances.

Here’s how it’s supposed to look. We’ve asked members of the public if they feel safe travelling on the bus during the day and in the dark. (see attachment “Required”)

The dashboard covers 3 modes of transport (Bus, Metro and Ferry) but this set of questions is asked only to the bus passengers. If the user has selected Metro or Ferry instead, this selection is to be ignored and the dashboard must show the bus scores instead.

I’ve used 3 dimensions (Year, Question and Answer) and one expression (shown below)

=sum({$<Mode=, Category={'Security'}, [Sub-category]={'Safety'}, [Answer]-={'N/A', 'Not Answered'}>}Count)
/
sum({<Mode=, [Answer]-={'N/A', 'Not Answered'}>}total<Category, [Sub-category], [Nexus Year], [Question]> Count)

For this expression, I’ve entered the following to control the background colour of the bars:

=IF( [Answer] = 'No, won’t use public transport due to concerns.' ,RGB(255,0,0),
     IF([Answer] = 'Not entirely but no different to other areas.' ,RGB(255,255,0),
     RGB(0,255,0) ))

The problem I have is that if the user selects Metro or Ferry, the conditional colouring is disregarded. It acts as if “Answer” is neither of the two answers it’s looking for and colours all the bars green. Apart from this it does seem to be behaving as it’s supposed to, as the percentages remain as they should. (see attachment “Incorrect”)

Can anybody advise on this? I’m hoping there’s a setting on the chart I can simply enable to sort this. Setting the colours in the “Colors” tab of the chart isn’t ideal, as there won’t always be 3 answers to every question (e.g., if we set the first 3 colours to red, yellow and green and then everybody says they feel completely safe for a question, that bar will appear red).

[m_woolf]

Without actually seeing your data, I can just make a guess.

When user selects Metro or Ferry, is there more than one possible Answer?

[SteveDagg]

I struggled to add attachments to show how it looks. For the two attachments, if you click on the small black download icon to the right of the filename and then select OPEN, you'll be able to view the 2 screen clippings in Paint.

There are 2 major tables - Interviews and Response. Each interview can have around 40 responses, a response being a question and answer. Mode (Bus, Metro or Ferry) is an attribute in the Interview table. The questions and answers that this chart reports on are only asked for Bus interviews, so if you select Metro or Ferry all of these questions will be excluded and the chart will appear blank. However, this isn't what we want. We want it to behave as though "Bus" is selected, so we've added "Mode= " in the set analysis so that if the user has excluded Bus in the list box, this exclusion will be disregarded. It's all working very well apart from the little glitch with the conditional colouring when Bus gets excluded!

Hope this helps to clarify

[SteveDagg · Accepted solution]

Thanks to everyone who looked at this and/or made suggestions. I have found a workaround.

Previously, I had one expression and 3 dimensions. The third dimension was "Answer" and had only 3 possible values.

I have now removed this third dimension and created 3 expressions, one for each possible answer. I can assign a colour (red, yellow or green) to each expression, eliminating the need to test attribute values in order to determine what colour to use. This method is more robust and is working correctly.

Hope this is of some use to anyone encountering a similar problem.

Steve