Traffic Light Gauge within Straight Table

[NickBentley]

Hi there,

I am trying to develop traffic light gauges within a straight table that bases colours off 'Category' rather than hard coded values.

Anyone had any luck achieving this?

[SRA]

Hi,

Let's try one answer. I loaded your data with :

Color:
LOAD * INLINE [
Category, Color, ValueMin
Fruit_Veg, 128994, 7
Fruit_Veg, 128993, 4
Fruit_Veg, 128992, 0
Poultry, 128994, 5
Poultry, 128993, 2
Poultry, 128992, 0
Meat, 128994, 6
Meat, 128993, 5
Meat, 128992, 0
];

// 128992 = Red bulb, 128993 = Yellow bulb, 128994 = Green bulb

data:
LOAD * INLINE [
ID, Product, Category, Qty
1, Apple, Fruit_Veg, 3
2, Apple, Fruit_Veg, 5
3, Chicken, Poultry, 6
4, Pear, Fruit_Veg, 7
5, Steak, Meat, 6
6, Steak, Meat, 2
7, Steak, Meat, 3
8, Duck, Poultry, 9
9, Chicken, Poultry, 3
10, Chicken, Poultry, 2
];

Is this what you are trying to achieve ?

Quantity measure is :

=Sum(Qty)

Traffic light measure is :

=If(Sum(Qty)<FirstSortedValue(ValueMin,ValueMin,2), Chr(FirstSortedValue(Color,ValueMin,1)),
If(Sum(Qty)<FirstSortedValue(ValueMin,ValueMin,3), Chr(FirstSortedValue(Color,ValueMin,2)), Chr(FirstSortedValue(Color,ValueMin,3))))

I hope this answer will help you. If yes you can tick the answer as a solution.

Thanks