QlikView Chart Color Logic

[#1]

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:

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

[maxgro]

maybe

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

[#3]

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

[maxgro]

could you post a small example of your problem?

[MarcoWedel]

Hi,

one solution could be:

or

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

[#6]

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!

[#7]

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 · Accepted solution]

Hi,

maybe like this?

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]

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