Qlikview: Showing 0 Bars but maintaining order

user_8607498 0
Viewed 60

I have a bar chart with 6 categories, e.g. Prohibited, Restricted, High, Very High, Moderate, Low. In my load script I created a order column for the sorting of the bars in the chart which goes something like: 

if(COL = 'prohibited', 1,
if(COL = 'restricted', 2,
if(COL = 'very high', 3,
if(COL = 'high', 4,
if(COL = 'moderate', 5,
if(COL = 'low', 6, 1)))))) AS COL_SORT

I also have the "Show All Values Option" checked which shows the categories with 0 counts. What I find is that sorting only partially works in that if I have volume in each category the order works but if there are 0 bars there are no guarantees the sort works.

My question is: Is there a way to guarantee the order of the bars no matter whether is the bar count is 0 or more? The company I work in won't let me upload sample data so I understand if that annoys people trying to help.

Kind Regards

Edit: I'd like to keep the order as in the if/else statement.

qlikview
2 Answers

in chart "properties -> sort" use "expression" as the sort order and use this expression:

max({1} COL_SORT)
user_23422920

The dual() function can do what you want. Dual assigns a numeric value to each element of a dimension. QlikView uses it internally to sort things like Months and day names. Important note, after assigning the order in the script you need to set the sort order in your chart to Numeric.

dual(COL,
 if(COL = 'prohibited', 1,
  if(COL = 'restricted', 2,
    if(COL = 'very high', 3,
      if(COL = 'high', 4,
        if(COL = 'moderate', 5,
          if(COL = 'low', 6, 1))))))) AS COL
user_47640010
Related