How to sort x-axis dimension according to a string?
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?
Re: How to sort x-axis dimension according to a string?
Hm, well - that is just what I originally did. Items with the same COMP string are often next to each other after that, but not always, and there is no sorting of items with different COMP strings - if I do a manual numbering of the strings, I get e.g. 10, 25, 1, 26, 8, 8, 34, 8, 42, 41, and so on.
I made a screenshot to illustrate this:
The sort window looks like this:
Am I doing something wrong? Is there some special setting I may have wrong?