Chart with persistent colors changing colors

[ttmaroney]

Greetings QlikView gurus, 

I have a Qlikview application which pulls data from an SQL based ticketing system. One field I want to chart and sort by is priority (which is 4,3,2,1, or Major Incident) for all active tickets, by group assigned, and by team member assigned. I want the colors to be 4(green), 3(blue), 2(yellow), 1(red), and major incident (grey). I used persistent colors, but when I reload the data, the colors often change- I understand based on the order these priority appear in the data, which changes as tickets are opened or edited. To fix that, rather than use priority as a dimension, I made custom expressions with the following labels, definitions and colors:

Label                    Definition                                                                                   Color                 

Priority 4                =Count({<[Priority]={'4'}>}[Priority])                                   Green: rgb(0,255,0)

Priority 3                =Count({<[Priority]={'3'}>}[Priority])                                   Blue: rgb (51,153,255)

Priority 2                =Count({<[Priority]={'2'}>}[Priority])                                   Yellow: yellow()

Priority 1                =Count({<[Priority]={'1'}>}[Priority])                                   Red: rgb(255,0,0)

Major Incident'      =Count({<[Priority]={'Major Incident'}>}[Priority])         Grey: rgb(150,150,150)

Now the colors stay the same, but the problem is the chart is not clickable like it was before, and I want it to be. When I click on the pie piece for p4, I want it to filter to show me only p4 tickets, like it did when it was done by dimension.  What is the best way to accomplish both objectives (static colors on a chart that remains clickable)?

[marcus_sommer · Accepted solution]

See here: Colors in charts

- Marcus

[Gysbert_Wassenaar]

You can use Priority as dimension and assign colors to the priority values in several ways. You can load a table in the script that associates the priorities with the colors:

PriorityColors:

LOAD Priority, RGB(R,G,B) as Color INLINE [

Priority, R,B,B

Priority 4, 0, 255,0

Priority 3, 51, 153, 255

..etc

];

You can then use as color expression =only(Color)

Or you can use a pick-match combination as color expression:

pick(match([Priority],'Priority 1','Priority 2', ...etc ), RGB(0,255,0), RGB(51,153,255), ...etc )

[Chanty4u]

or else u can create a  excel sheet with ur priority colur coding and do inline  to get the exct colurs for ur  req

[ttmaroney]

Thank you all for your suggestions. I found the article Marcus posted very helpful. The tip from Gysbert would have worked, but I want others, especially executives, to be able to control the colors of this and other variables without editing (and breaking) a load statement, so I believe keeping an Excel workbook, as Chanty suggested) with a tab for each variable and the associated RGB numbers is the way to go. I did have trouble Mapload and Applymap, so I went with the following for priority (as no ticket does not have a priority):

Directory;
LOAD Priority,
Red,
Green,
Blue,
RGB(Red,Green,Blue) as PriorityColor
FROM
Data\Colors.xlsx
(ooxml, embedded labels, table is Priority);

Then when I tried to set colors for another variable, ticket type (incident, request, or problem), I got a circular reference error, so I made the RGB variables for ticket type red1, green1, and blue1 and that solved that issue.  I might change them to RedPriority, RedType, etc, but for now it works. Thank you all again.

Directory;
LOAD [Ticket Type],
Red1,
Green1,
Blue1,
RGB(Red1,Green1,Blue1) as TypeColor
FROM
Data\Colors.xlsx
(ooxml, embedded labels, table is TicketType);