Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

It is not uncommon that users want specific products or customers to be displayed in specific colors. The most obvious way to do this is to change the colors in the chart properties. This is in fact quite easy if you use the copy and paste functions found when you right-click a color button. Just copy one button and paste on another, and you have moved the color.

 

Copy and Paste.png

 

This way you can assign which color is to be used for the different values of the field. However, a prerequisite for this to work is that the order of the field values doesn’t change.

 

A more robust way is to use color functions. Usually, you want to set the color of a bar, line or bubble and this is done by using the “Background Color” on the Expression tab:

 

Color expression.png

 

By the way – don’t use Visual cues. This feature is old and not very versatile. Use color functions as described here instead.

 

In the picture above, both the product ID and the color are hard-coded in the expression. However, if you want to define colors for many products, the if-function will not be manageable. Then it is better to store this information in a table – either in the database or in an Excel sheet or as an inline statement in a scriptlet that is included in the script. Hence,

 

  1. Create your color definition table and store it in an appropriate place. The Red, Green and Blue columns hold the different color components and define the color uniquely.

    Color table.png
  2. Load the color definitions into a mapping table:
       ProductColors:
       Mapping Load ProductID, Rgb(Red,Green,Blue) as ProductColor From ProductColors

  3. Use this mapping table when loading the products table, creating a new field for the product color:
       Applymap('ProductColors', ProductID , LightGray()) as ProductColor
    The third parameter, here lightgray(), defines which color the unlisted products should get. If you instead use null(), the unlisted products will be multicolored according to the color settings in the chart properties.

  4. Finally, use this field as product color in the charts:

    Color expression2.png

 

This way it is easy to define which color specific products, customers, or other dimensions should get.

 

Which colors to use? Oh, that is a completely different topic:

 

HIC


Bar chart.png

34 Comments
Not applicable

Dear Henric

thanks for your reply. I still do not get it. You mean it should be like this?

Mapping LOAD Industry RGB(Red,Green,Blue)as ProductColor from [path...data.xlsx]
(
ooxml, embedded labels, table is color);
Applymap('ProductColors', Industry , lightgray()) as ProductColor

I receive an unknown statement error....

Jay

0 Likes
4,430 Views
hic
Former Employee
Former Employee

There are two ways to do this:

1) If you use your Load statement, without the "Mapping", and without the "ApplyMap", it should work. Then you will get an extra table with Industry and ProductColor.

2) Use the Mapping Load, and then the ApplyMap inside a load statement where you load the Industy field.

HIC

0 Likes
4,430 Views
Not applicable

Could you please support me with option 2? How would it look like inside the load statement?

thank you

0 Likes
4,444 Views
hic
Former Employee
Former Employee

Assuming that you already have a Load statement loading a table containing the field Industry, e.g.

     Industries:

     Load * From Industries.txt;

then you should change this into

     IndustryColorMap:

     Mapping LOAD Industry RGB(Red,Green,Blue) as IndustryColor from [....xlsx] (...);

     Industries:

     Load *,

       Applymap('IndustryColorMap', Industry , lightgray()) as IndustryColor

       From Industries.... (...) ;

Read more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

HIC

0 Likes
4,444 Views
danialier
Creator III
Creator III

Thanks Henric. But what I want is to change the text format, so instead of showing all products in black, I want to have the products colored based on the company they belong to.

I attached a QV sample on where I stand.

thanks,dani

0 Likes
4,444 Views
Not applicable

I do you visual cues but only to bold the title of the expression columns. It helps the pivot table look a little more uniform.

4,444 Views
danielgargiulo
Partner - Creator
Partner - Creator

Hi All, Quick question. Has anyone used Henrics method for line charts? I have it working for bar charts however it seems when applied to a line chart it does not like it.

Any suggestions welcomed.

Dan

0 Likes
4,444 Views
hic
Former Employee
Former Employee

It works fine. In the chart below, I have used "Quarter" as first dimension (since line charts should have an enumerable as x-axis) and Product as 2nd dimension. The data is the same as in the bar chart in the article.

HIC

Line Chart.png

0 Likes
4,444 Views
thanstad
Creator
Creator

Thanks, very nice!

Tormod

0 Likes
4,397 Views
Not applicable

Hi Henric Cronström,

Thank, good one. In which type of charts we can use this Method?

0 Likes
4,397 Views