Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikView Chart Color Logic

Hello,

I am trying to change colors in a line chart based on dynamic criteria.

I have a chart with a field called Business. In that field some of the values are: Shoes, Socks, Hats

Other values in that field include: Shoes (O), Socks (O), Hats (O)

I want to make the values without an '(O)' at the end be a solid color in the chart, and the one's with an '(O)' be a more opaque version of the original color.  It is important that this is done through automated logic.  It is also important to note that because there are other dimensions in this chart I cannot utilize a new field distinguishing between the values with and without '(O)'.

My current idea:

Inline load the color values or load them from Excel as R, G, and B variables and somehow map them to each Business field value.  This would result in an expression that says "if the Business is 'Shoes' then display it as rgb(Rshoes, Gshoes, Bshoes) for 'Shoes' and if the Business is 'Shoes (O)' then display it as argb(100, Rshoes, Gshoes, Bshoes)."

Written in the background color for the expression as:

If(Business = 'Shoes' , rgb(R,G,B), If(Business = Business & ' (O)', argb(100, R,G,B))

I am unsure as to what method to use to load in the color values and how to make sure that the portion of the expression containing 'If(Business = Business & ' (O)''  functions properly. 


Thank you for any help!!



Update:

I am pulling in the color values from Excel columns.

My formula for background color in the expression now reads as "ARGB(ColorA,ColorR,ColorG,ColorB)"

This expression colors everything correctly if I manually put in an argb value for all business values.


I am still trying to figure out a way to auto generate the colors for businesses with the 'O' on the end.

Thanks!


Correct Answer Reply:


Thanks everyone for the great replies!

Marco, I used your iteration logic as a launching point to dynamically assign the colors.  I ended up inline loading a list of colors as such:

ColorPalette.PNG

and then looping through my data to dynamically assign each of those colors based on certain criteria.

Thanks again for the help!

Message was edited by: Reed Perry

1 Solution

Accepted Solutions
MarcoWedel

Hi,

maybe like this?

QlikCommunity_Thread_204912_Pic7.JPG

QlikCommunity_Thread_204912_Pic8.JPG

QlikCommunity_Thread_204912_Pic9.JPG

tabBusCol:

LOAD Business&If(IterNo()=2,' (O)') as Business,

    ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol

FROM QlikCommunity_Thread_204912.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

While IterNo()<=2;

tabSales:

LOAD *,

    Money(Ceil(Rand()*1000)) as Sales

INLINE [

    Business, Product

    Shoes, Boots

    Shoes, Sneakers  

    Socks, low-cut

    Socks, ankle-dress    

    Hats, Top hats

    Hats, Baseball hats

    Hats, Hard hats      

    Shoes (O), Boots (O)

    Shoes (O), Sneakers (O)  

    Socks (O), low-cut (O)

    Socks (O), ankle-dress (O)

    Hats (O), Top hats (O)

    Hats (O), Baseball hats (O)

    Hats (O), Hard hats (O)    

];

tabProdCol:

LOAD Product&If(IterNo()=2,' (O)') as Product,

    ARGB(Pick(IterNo(),255,100),ProdCol>>16 bitand 255,ProdCol>>8 bitand 255,ProdCol bitand 255) as ProdCol

While IterNo()<=2;

LOAD Distinct

    Product,

    Color(AutoNumber(Product)) as ProdCol

Resident tabSales

Where not Product like '* (O)';

hope this helps

regards

Marco

View solution in original post

11 Replies
maxgro
MVP
MVP

maybe

if(WildMatch(Business, '*(O)'), rgb(R,G,B), argb(100,R,G,B))

Not applicable
Author

Thanks for the reply however this method did not work for me.

maxgro
MVP
MVP

could you post a small example of your problem?

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_204912_Pic1.JPG

QlikCommunity_Thread_204912_Pic2.JPG

QlikCommunity_Thread_204912_Pic6.JPG

QlikCommunity_Thread_204912_Pic3.JPG

or

QlikCommunity_Thread_204912_Pic5.JPG

QlikCommunity_Thread_204912_Pic4.JPG

tabBusCol:

LOAD Business&If(IterNo()=2,' (O)') as Business,

    ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol

INLINE [

    Business, R, G, B

    Shoes, 50, 100, 150

    Socks, 100, 150, 50

    Hats, 150, 50, 100

] While IterNo()<=2;

tabSales:

LOAD *,

    Money(Ceil(Rand()*1000)) as Sales

INLINE [

    Business

    Shoes

    Socks

    Hats

    Shoes (O)

    Socks (O)

    Hats (O)

];

hope this helps

regards

Marco

Not applicable
Author

Marco,

Thanks for your thorough answer!  That portion of the script in lines 2-3 is exactly what I needed!

The only other thing is that I would like to avoid inline loading the colors... I am hoping to store the solid colors in an excel spreadsheet so they can be maintained easier.

My spreadsheet looks likes:

Column A: Business

Column B: Value for R in rgb

Column C: Value for G in rgb

Column 😧 Value for B in rgb

Is there a way I could use the same logic but instead of lines 4-8 I'd load from Excel?

Thanks again for the help!

Not applicable
Author

The next step for this color problem is that after I click into a Business such as "Hats" I get a list of products displayed.

My chart is designed with a conditional dimension so that when I drill into a Business the chart changes the dimension to Products.

For example:  Once I choose "Hats" as a Business the chart displays values including: Top hats, Baseball hats, Hard hats, Top hats (O), Baseball hats (O), Hard hats (O).

For these values I do not want to have to set a specific color for every single one so I am hoping to make the solid color randomly generated and then the corresponding (O) value to have a lighter color.  So "Top Hats" may randomly appear as Red and if so then "Top Hats (O)" would appear as Light Red.

One more thing worth noting is that just because a field like "Top hats" exists does not mean "Top hats (O)" exists and vice versa.

MarcoWedel

Hi,

you can replace the inline load with a load from a file:

tabBusCol:

LOAD Business&If(IterNo()=2,' (O)') as Business,

    ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol

FROM QlikCommunity_Thread_204912.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

While IterNo()<=2;

tabSales:

LOAD *,

    Money(Ceil(Rand()*1000)) as Sales

INLINE [

    Business

    Shoes

    Socks

    Hats

    Shoes (O)

    Socks (O)

    Hats (O)

];

hope this helps

regards

Marco

MarcoWedel

Hi,

maybe like this?

QlikCommunity_Thread_204912_Pic7.JPG

QlikCommunity_Thread_204912_Pic8.JPG

QlikCommunity_Thread_204912_Pic9.JPG

tabBusCol:

LOAD Business&If(IterNo()=2,' (O)') as Business,

    ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol

FROM QlikCommunity_Thread_204912.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

While IterNo()<=2;

tabSales:

LOAD *,

    Money(Ceil(Rand()*1000)) as Sales

INLINE [

    Business, Product

    Shoes, Boots

    Shoes, Sneakers  

    Socks, low-cut

    Socks, ankle-dress    

    Hats, Top hats

    Hats, Baseball hats

    Hats, Hard hats      

    Shoes (O), Boots (O)

    Shoes (O), Sneakers (O)  

    Socks (O), low-cut (O)

    Socks (O), ankle-dress (O)

    Hats (O), Top hats (O)

    Hats (O), Baseball hats (O)

    Hats (O), Hard hats (O)    

];

tabProdCol:

LOAD Product&If(IterNo()=2,' (O)') as Product,

    ARGB(Pick(IterNo(),255,100),ProdCol>>16 bitand 255,ProdCol>>8 bitand 255,ProdCol bitand 255) as ProdCol

While IterNo()<=2;

LOAD Distinct

    Product,

    Color(AutoNumber(Product)) as ProdCol

Resident tabSales

Where not Product like '* (O)';

hope this helps

regards

Marco

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you want to avoid pre-loading the values in the script, you can compute the color at runtime using a background expression of:

if(Business LIKE '*(O)'

,color(FieldIndex('$(=GetCurrentField(BizProduct))'

  ,left($(=GetCurrentField(BizProduct)),len($(=GetCurrentField(BizProduct)))-4)))

bitand argb(100,255,255,255)

,color(FieldIndex('$(=GetCurrentField(BizProduct))',$(=GetCurrentField(BizProduct))))

)

Note if you have more than 18 values for a field, you'll have to use mod(fieldindex,18)+1 to get one of the 18 color palette values.

More info here

A Color Trick | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com