I have a simple table chart. I am trying to set up Visual Cues so that the background will be Green for favorable conditions, and Red otherwise.
What determines favorable conditions is indicated by a field called IndicatorStatus, which can contain either 'Lower is Better' or 'Higher is Better'. These are the only two values, though obviously there can also be an indeterminate state, which I think is in play here.
Based on selections, the table will have a mixture of rows, some with the IndicatorStatus of 'Lower is Better' and others with 'Higher is Better'. Also, two other expressions, one for Benchmark and another for Value. It is the Value field that we are using Visual Cues for.
Visual Cues on this expression are defined:
Upper >= =avg({$<Sq={$(=Period2)}>} Benchmark)
Background: = if(IndicatorStatus = 'Lower IsBetter',lightred(),blue())
Lower <= =avg({$<Sq={$(=Period2)}>} Benchmark)
Background: = if(IndicatorStatus = 'Lower IsBetter',green(), black())
(In this example, I've used blue and black to represent the 'higher is better' condition because it makes the problem easier to see.)
When I have not made a selection on IndicatorStatus, and the dataset contains a mixture of 'Higher is Better' and 'Lower is Better' conditions, the Visual Cues does not work as expected. I expected that since each aggregate row must have a single value for Indicator Status, (it is a dimension of the table) then it would KNOW this value in the Visual Cues expression, but this IS NOT the case.
If IndicatorStatus is not selected, then the value in the Visual Cue is unknown, so even though the dimension clearly shows 'Lower is Better' it is taking the 'false' path of the if function (that is, it doesn't see the value).
This is resulting in the values changing background colors back and forth as the selection is made.
What I want is to have the Visual Cues KNOW the value for the current row, the same way it know the Value and Benchmark fields.
Help me, QlikView Community. You're my only hope. [:^)]
