topic Re: QlikView Chart Color Logic in QlikView

QlikView Chart Color Logic

Tue, 09 Feb 2016 18:30:59 GMT

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

Re: QlikView Chart Color Logic

maxgro — Tue, 09 Feb 2016 20:01:43 GMT

maybe

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

Re: QlikView Chart Color Logic

Tue, 09 Feb 2016 20:17:32 GMT

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

Re: QlikView Chart Color Logic

maxgro — Tue, 09 Feb 2016 21:21:22 GMT

could you post a small example of your problem?

Re: QlikView Chart Color Logic

MarcoWedel — Tue, 09 Feb 2016 22:38:16 GMT

Hi,

one solution could be:

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

Re: QlikView Chart Color Logic

Wed, 10 Feb 2016 14:36:59 GMT

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!

Re: QlikView Chart Color Logic

Wed, 10 Feb 2016 15:14:43 GMT

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.

Re: QlikView Chart Color Logic

MarcoWedel — Wed, 10 Feb 2016 23:54:33 GMT

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

Re: QlikView Chart Color Logic

MarcoWedel — Thu, 11 Feb 2016 00:53:42 GMT

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

Re: QlikView Chart Color Logic

rwunderlich — Thu, 11 Feb 2016 02:02:04 GMT

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

Re: QlikView Chart Color Logic

Mon, 22 Feb 2016 20:38:08 GMT

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!

Re: QlikView Chart Color Logic

Anonymous — Fri, 01 Apr 2016 11:06:45 GMT

I faced a similar situation. But I have 3 dimensions in my case: Company, Metric and Year. The sum(value) of the metric is my expression. I have chosen year as my primary and Company as secondary dimension and the metric is stacked. I get only 2 colors when i use an expression like this:

if(Company=SubField($(vCompany),’,’,1),argb(75,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)) , If(Company=SubField($(vCompany),’,’,2),argb(100,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,3),argb(125,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),

If(Company=SubField($(vCompany),’,’,4),argb(150,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,5),argb(175,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,6),argb(200,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,7),argb(255,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),color(FieldIndex(‘Metric’,Metric)))))))))

Please help me..