Hi
I think I understand what you are trying to do, but the Excel file is difficult to interpret into a dynamic Qlikview expression (for example, no between operator). It looks like you are trying to colour products according to low/medium/high bands with different limits for each product.
The way I handle something like this is to use Class or Interval match to 'band' the values, and then colour the results based on the class or interval band. You could use the extended interval match syntax to get product specific banding
The spreadsheet would contain the fields: Product, Low, High
You could load this into like this:
Intervals:
LOAD Product,
'Low' As Band,
0 As [From],
Low As [To]
From ProductBands.xlsx
(.....)
Concatenate
LOAD Product,
'Medium' As Band,
Low+1 As [From],
High-1 As [To]
From ProductBands.xlsx
(.....)
Concatenate
LOAD Product
'High' As Band
High As [From],
Null() As [To]
From ProductBands.xlsx
(.....)
Then use intervals something like
Left Join (Sales)
IntervalMatch(Sales, Product)
LOAD [From], [To] Resident Intervals;
Left Join (Sales)
LOAD * Resident Intervals;
Drop Table Intervals;
In the chart, use the colour expression:
=Pick(Match(Band, 'Low', 'Medium', 'High'), RGB(225,0,0), RGB(0,0,225), RGB(0,127,0))
Hope that helps to get you started
Jonathan
Changed to Concatenate
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
.png "Luminary Alumni"){kind=icon}
