CODEWITHSUNDEEP
qlikview
LorcanT
LorcanT

Reputation: 63

Qlikview: Showing 0 Bars but maintaining order

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.

Upvotes: 2

Views: 76

Answers (2)

The Budac
The Budac

Reputation: 1633

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

Upvotes: 1

EldadT
EldadT

Reputation: 932

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

max({1} COL_SORT)

Upvotes: 4

Related Questions