I need to color bar charts, pie charts, point maps, table fills, etc. consistently by a custom color set by:
- a single dimension, or
- two different dimensions on different tables that repeat common values High, Medium, and Critical.
but when specifying colors the legend disappears.
For one dimension (#1), I built a color by expression for each of the three measures,
if([Security Category Rating]= 'Medium', rgb(191, 191, 191),
if([Security Category Rating]= 'High', rgb(0, 32, 96),
if([Security Category Rating]= 'Critical', rgb(0, 176, 240),
When I use the expression instead of Qlik's built-in color, the legend disappears from the chart.
For two similar dimensions in different contexts (#2), I have columns that have the same implicit meaning and I want to filter by the same unique 'overall' category High, Medium, and Critical across different tables of things rated separately. Normally in SSRS I define a bridge lookup table and a unique dimension table then connect the data as follows.
Location Summary Table
|Location||Overall Equipment Criticality|
|Overall Criticality||Criticality Variant|
Relationships in SSRS like data modeling software:
- Bridge Table.[Overall Equipment Criticality] (>>many to one>) Dim_Criticality.[Overall Criticality]
- Storage Table.[Storage Criticalities] (>>many to one>) Bridge Table.[Criticality Variant]
- Bridge Table.[Criticality Variant] (>>many to one>) Dim_Criticality.[Overall Criticality]
- Location Summary Table.[Overall Equipment Criticality] (>>many to one>) Dim_Criticality.[Overall Criticality]
Then within my charts I would like to use the dimension with a common meaning in the two different tables as Dim_Criticality.[Overall Criticality]. In Qlik Sense's data manager, when creating associations listed above it said there is a circular reference. I think this is because I cannot appear to tell Qlik the directionality of any association relationship as being many to one. Is that correct?
To get around this must I merge the Bridge, Storage, and Location Summary tables?
I found another approach unique to Qlik that involves setting variables within the load script then referencing the variables in the color by expression. This will not get around the first issue, granted, but is that method documented or useful to consider?
I need this to work in Qlik Sense Cloud, Qlik Sense Desktop, and Qlik Sense Enterprise server.