Unforunately, I am running a personal edition, so couldn't open your QVW file but based on what I understood from your description I think that you are looking for a conditional color formatting of the text which in your case is the "target".
Assuming that you are displaying the sales target in a table, you can set different conditional colors by going to the Expressions tab in the table properties. Simply expand the expression and you will see the option "Text Color". Click "Text Color" and enter the conditional expression in the "Definition" area. The expression should be something like this:
If(sum(SalesTarget) < 1000000, Red(),
If(sum(SalesTarget) > 1000000, If(sum(SalesTarget) > 5000000, Green(),
In the above expression, I am setting the SalesTarget to:
- Red - if it is less than 1 million
- Blue - if it is between 1 and 5 million and
- Green - if it is anything above 5 million
Hope this helps
Thank you very much for your reply but this is not what I am looking for. I want to take the operator ('<' or '>' or '>=' or '<=') directly from excel sheet and create the if clause in expression similar as you have created. This can be achieved using variables, but I can't create variables for each product as product may be 100 or 200 and user can specify different operator for each product.
Hope I am able to explain my problem.
Thanks & Regards,
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
Changed to Concatenate