0 Replies Latest reply: Apr 19, 2018 10:27 AM by Isaac Warren RSS

    Why do legends disappear when I color a chart by expression (two scenarios)?

    Isaac Warren

      I need to color bar charts, pie charts, point maps, table fills, etc. consistently by a custom color set by:

      1. a single dimension, or
      2. 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),

      'purple')))


      When I use the expression instead of Qlik's built-in color, the legend disappears from the chart.

      Vulnerabilities Bar Chart Qlik colors.pngVulnerabilities Bar Chart Expression colors.png


      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

      LocationOverall Equipment Criticality
      AHigh
      BMedium
      CCritical
      DCritical
      EMedium
      FCritical


      Storage Table

      LocationStorage Criticalities
      AHigh; Medium
      BMedium; Low
      CCritical; High
      DHigh; Critical
      EMedium; Low
      FCritical; Medium


      Bridge Table

       

      Overall CriticalityCriticality Variant
      HighHigh; Medium
      MediumMedium; Low
      CriticalCritical; High
      CriticalHigh; Critical
      MediumMedium; Low
      CriticalCritical; Medium
      High

      High

      MediumMedium
      CriticalCritical


      Dim_Criticality

       

      Overall Criticality
      High
      Medium
      Critical


      Relationships in SSRS like data modeling software:

      1. Bridge Table.[Overall Equipment Criticality] (>>many to one>) Dim_Criticality.[Overall Criticality]
      2. Storage Table.[Storage Criticalities] (>>many to one>) Bridge Table.[Criticality Variant]
      3. Bridge Table.[Criticality Variant] (>>many to one>) Dim_Criticality.[Overall Criticality]
      4. 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.