Color by Expression for Over 100 Values

[Jennerz]

I have a dashboard that has a line graph that shows about 60 Models of Cars (Mustang, Challenger, etc.) and the Count of Active Issues (Y-Axis) over a period of 180 days (X-Axis). 

Currently, the line graph is divided by the Car Model; however, there is a need to split a Car Model's line by the 3 Types of Car Issues (Issue Type 1, Issue Type 2, Issue Type 3) to compare each Car Model's Count of Active Issues by Issue Type over time.

Originally, I planned on having each Car Model have their own color and the Issue Types lines would be split by solid, dashed, or dotted, BUT Qlik does not have this capability.

INSTEAD, I would like to assign each Car Model their own color and have each Issue Type Line the same color but split by varying opacities using the ARGB code.

I imported a table with each Car Model Name, Issue Type, and columns for the ARGB numbers to use in the color by expression option under presentation, but since that requires over 100 if loop nests, Qlik throws an error even though the code works. 

The code I wrote looks like this:

=IF([Car_Model_Issue]='Mustang: Issue Type 1', {<[Car_Model_Issue]={'Mustang: Issue Type 1'}>}argb([A_ARGB], [R_ARGB], [G_ARGB], [B_ARGB]),
IF([Car_Model_Issue]='Mustang: Issue Type 2', {<[Car_Model_Issue]={'Mustang: Issue Type 2'}>}argb([A_ARGB], [R_ARGB], [G_ARGB], [B_ARGB]),
IF([Car_Model_Issue]='Mustang: Issue Type 3', {<[Car_Model_Issue]={'Mustang: Issue Type 3'}>}argb([A_ARGB], [R_ARGB], [G_ARGB], [B_ARGB]))))

How can I go about achieving my goal? Any ideas for easier implementation?

[RafaelBarrios]

hi @Jennerz 

try this

MyTable:
Load * Inline [
MODEL_ID,MODEL,R_ARGB,G_ARGB,B_ARGB
1,Model 1,100,100,100
2,Model 2,100,100,250
3,Model 3,100,250,100
4,Model 4,250,100,100
];

//ALL POSIBLE COMBINATIONS
left join(MyTable)
Load * Inline [
ISSUE_TYPE_ID,ISSUE_TYPE, A_ARGB
1,Issue Type 1,100
2,Issue Type 2,150
3,Issue Type 3,200
4,Issue Type 4,250
];

and for values another i prepared an inline table with values only for model 2 (blue) and 4 (red)

sales:
Load * Inline [
DATES,MODEL_ID,ISSUE_TYPE_ID,Quantities
1/1/2023,2,1,100
2/1/2023,2,1,200
3/1/2023,2,1,300
4/1/2023,2,1,400

1/1/2023,2,2,300
2/1/2023,2,2,400
3/1/2023,2,2,100
4/1/2023,2,2,200

1/1/2023,2,3,400
2/1/2023,2,3,300
3/1/2023,2,3,200
4/1/2023,2,3,100

1/1/2023,4,1,10
2/1/2023,4,1,20
3/1/2023,4,1,30
4/1/2023,4,1,40

1/1/2023,4,2,30
2/1/2023,4,2,40
3/1/2023,4,2,10
4/1/2023,4,2,20

1/1/2023,4,3,40
2/1/2023,4,3,30
3/1/2023,4,3,20
4/1/2023,4,3,10
];

this will return a syn table, but will work for this example

on previous tables i only use

=argb([A_ARGB], [R_ARGB], [G_ARGB], [B_ARGB])

as background color expression (without the if) as ther is only one posible color for each combination.

for line chart its a bit tricky because you cant use only model or issue_type as dimension, you will need to combine both, otherwise you will have more than one color for each line.

first dimension: Date

second dimension: =MODEL& ': ' &ISSUE_TYPE

measure: Sum(Quantities)

color by expression: argb([A_ARGB], [R_ARGB], [G_ARGB], [B_ARGB]) without the if

hope it helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!