Charts in tables. How do I set different gauge settings for each row of data

[#1]

Hi all

Let's say I have a table of KPIs that I have imported. The table has the following fields.

1. Indicator Name
2. Value
3. Lower Bound 1 Value
4. Lower Bound 2 Value
5. Lower Bound 3 Value

I want to display fields 1 and 2 in a table chart. In a third column I want to add a 3 segmented Traffic Lights representation of Value, using field [Lower Bound 1 Value], [Lower Bound 2 Value], [Lower Bound 3 Value] to set the Lower Bounds of each segment.

The reason I have fields 3, 4 and 5 in my dataset is becuase each indicator is unrelated to each other indicator and different values would determine a green, amber or red.

I want the 46 indicators to be presented in a simple list with 3 columns: Indicator Name, Indicator Value, Traffic Lights

When setting the Lower Bound expressions, they apply to all rows of data.I 'm sure this makes sense as normally a field in a table would be representing the same thing. I'm doing this because it makes it quick to put all the indicators on the front page.

Question 1: How do I acheive the above

Question 2: Is there a better way. Bear in mind I only started using Qlikview about 2 weeks ago.Also, I need to build this quick and dirty dashboard by Tuesday, so, given my lack of experience, it would be quicker for me to do a long repetitive way (can i have a table per indicator for example?)

Any ideas?

thanks

fil

[#2]

Actually further to that. Can I set gauge settings (such as max, lower bound) according to the values in the other fields in the record?

[#3]

It woudl be useful if I could just have an expression that summed the Value column if the Indicator Code column = 1. The next traffic lights down i would sum the value column, if the indicator code =2 and so on.

[rwunderlich · Accepted solution]

You can use expressions to set gauge min/max and segment bounds, but there is only one value for gauges in the column -- as you've discovered. You'll need to use different approach. Here's a couple of suggestions.

1. Skip the 3rd column and instead color the Value column. Use either a Text Color or Background Color expression like:

if(Value > bound3, red(), if(Value > bound2, yellow(), green())

2. If you require the traffic light metaphor, use an image in the third cell. Same expression as above but "red_image.jpg" instead of red() etc. Create three images that represent the states you want. For example r_image.jpg would have the "green" and "amber" light positions grey and the red light red. You can make the images you want by creating some dummy gauge charts the way you want them to look and then right click, export screen image.

-Rob

[#5]

Hi Rob

These ideas are excellent. However i hit Accepted too early as I've tried to implement idea 1 and am having trouble. Wheredo put the expression? I have put it in the Visual Cues page. But it is acting as though I have put exact values.

cheers

fil

[stephencredmond]

Hi Fil,

You have one problem with your bounds figures. They are expressed as a % - the underlying value is a decimal. For example, 30% has an underlying value of 0.39. The values that you are calculating are all > 1. Do you need to divide that by 100 to get at %? Or multiply the bounds by 100.

Visual Cues should work. Alternately, you can calculate the Background Color for any expression by clicking the "+" beside the expression and entering an expression under the definition for "Background Color".

Regards,

Stephen

[rwunderlich]

I don't think Visual Clues will work for setting different bounds per row. However, as Stephen indicated, you can do this by clicking the "+" next to the expression and entering a "Background Color" or "Text Color" expression.

-Rob

[wizardo]

hmm

might be another way:

make a straight table with "Indicator Name" as dimension

add expression and set its representation to a traffic Lights

put this in the expression

=if(Sum(Value) >= [Lower Bound 3],3,if(Sum(Value) >= [Lower Bound 2],2,1))

and then use 1,2 and 3 as your gauge segment's lower bounds (not the min/max)

Mansyno