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

[#2]

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!

[Anonymous]

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..