Search code examples
qliksense

Need help in re-order / sort Bars in Bar Chart in Qliksense

I am trying to reorder the Bars in bar chart in qliksense. Below is the order which I have to sort the bars.

NEW
IN_PROGRESS_PL
ASSIGNED
IN_PROGRESS_SPOC
RESOLVED/CLOSED
FBS E2S BACKLOG
CANCELLED
DUPLICATE

But by default the bars are ordered in alphabetical order or by the numbers, I have tried the below solution, but it still didn't rearranged the bars.

Default order :

enter image description here

Match('NEW','IN_PROGRESS_PL','ASSIGNED','IN_PROGRESS_SPOC','RESOLVED/CLOSED','FBS E2S BACKLOG' , 'CANCELLED','DUPLICATE' )

Can someone please help me to fix this issue.

Solution

  • The first parameter in Match is the targeted field.

    If we have data like:

    RawData:
Load * inline [
Stage       , IdeasCount
ASSIGNED    , 63
CANCELLED   , 11
INTERNAL_123, 2
IN_PROGRESS1, 20
IN_PROGRESS2, 47
];

    Then the sorting expression will be:

    =match(Stage, 'IN_PROGRESS1', 'ASSIGNED', 'IN_PROGRESS2', 'INTERNAL_123', 'CANCELLED')

    The sorting properties:

    chart properties

    And the result chart will have the correct sorting: result

    P.S. (1)

    Have to mention that if the value is not found in the Match function then the function will return null and thus the corresponding bar will be pushed in the front. In this case you can wrap the match in if statement and if no matching value is found then assign some large number:

    = if( match(Field, 'value1', 'value2' ...) > 0,
      match(Field, 'value1', 'value2' ...),
      1000
  )

    P.S. (2)

    In your case you can also use Dual function to create "default" sort order for a specific field.

    If we change the script to:

    RawData:
Load 
  Dual(Stage, OrderId) as Stage,
  IdeasCount
;
Load * inline [
Stage       , IdeasCount, OrderId
ASSIGNED    , 63        , 2
CANCELLED   , 11        , 5
INTERNAL_123, 2         , 4
IN_PROGRESS1, 20        , 1
IN_PROGRESS2, 47        , 3
]
;

    The result table will look the same - with two fields only Stage and IdeasCount. But in the background each value of Stage field will have and number representation (based on the initial OrderId field). And as a side effect of that the auto sorting option will sort the field data by its internal number representation and the chart will "sort itself" correctly

    default sorting