topic Re: Change expression font color based on targets in QlikView

Change expression font color based on targets

Thu, 16 Aug 2012 08:29:34 GMT

Hi All,

I have to implement a functionality where I have to change the font color based on targets specified in excel sheet. For each product there are different targets and operators for which different font colors have been specified.

I needs to show different font color of sales according to provided excel sheet. I have attached the qvw file and configurable excel sheet.

Please check the text color of Sales expression. It is not working. After some investigation I found that the Operator1, Color1 etc are coming as a text from excel sheet that's why it is not working. Is there any other way to achieve this functionality.

Please provide your expert comments.

Thanks in Advance,

Anosh Nathaniel

Re: Change expression font color based on targets

richard_pearce6 — Thu, 16 Aug 2012 09:56:48 GMT

Hi Anosh,

I had a simalar question to you some time ago. Try looking at this thred http://community.qlik.com/message/175527#175527

Richard

Re: Change expression font color based on targets

Thu, 16 Aug 2012 13:51:29 GMT

Hi Richard,

I have gone through your post but this is not the similar question which I am asking. It is different problem.

Thanks & Regards,

Anosh Nathaniel

Re: Change expression font color based on targets

Thu, 16 Aug 2012 15:28:36 GMT

Hi Anosh,

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(),

Blue())))

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

Cheers,

Ravi

Re: Change expression font color based on targets

Mon, 20 Aug 2012 18:55:48 GMT

Hi Ravi,

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,

Anosh Nathaniel

Re: Change expression font color based on targets

Thu, 23 Aug 2012 07:17:21 GMT

Hi Experts,

Please suggest any solution for my problem.

Thanks & Regards,

Anosh Nathaniel

Re: Change expression font color based on targets

jonathandienst — Thu, 23 Aug 2012 09:35:30 GMT

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

Re: Change expression font color based on targets

Fri, 19 Jul 2013 13:04:50 GMT

Hi Anosh,

RE. No.4 reply, you say that this can be done using variables. Could you let me know how you do this.

I am having a similar issue, but as my dataset is smaller I might be able to use variables.

Thanks

Re: Change expression font color based on targets

Wed, 16 Oct 2013 13:12:39 GMT

Have you got the answer for the issue...???????