2 Replies Latest reply: Apr 30, 2014 1:35 AM by Frank Küster RSS

    How to sort x-axis dimension according to a string?

      Hi,

       

      I have created a bar chart with the product name as the primary dimension which gives the x-axis. Each product has an associated COMPOSITION string which indicates the four main components, and I would like the order of the products according to this string. For example, I have

       

      PROD     ITEM      VAL     COMP

      productA     foo       41     Brick.Concrete.Steel.Glass

      productA     bar       6       Brick.Concrete.Steel.Glass

      productB     foo       21     Concrete.Steel.Glass.Brick

      productB     bar       9       Concrete.Steel.Glass.Brick

      productC     foo       17     Brick.Concrete.Glass.Steel

      productC     bar       7       Brick.Concrete.Glass.Steel

       

      with the PROD as the x-axis dimension and ITEM as the second dimension for stacked bars. Now I want productA and productC to be displayed next to each other, since they have the most similar components (and productA first, since Glass is alphabetically before Steel).

       

      I tried to Sort the PROD dimension by the expression = COMP, but this didn't help. I wondered whether Qlikview is confused because each productA has two COMP values, and tried MaxString(COMP) for aggregation. But still the sort order seems to be arbitrary.

       

      In the simple example, I could use a match function (match(COMP,'Brick.Concrete.Steel.Glass',....)) - but in fact I have 17 differenct major components, not in 4^17 combinations but in a lot of them, and new products may have a new one. Therefore I'd rather avoid this, and it can't be so hard to sort by alphabet?

       

      Can someone help me, thanks!