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

# 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!

• ###### Re: How to sort x-axis dimension according to a string?

Hi

On the sort tab, check Sort by Expression, and enter =COMP into the expression box. You may need to check Text as well

HTH

Jonathan

BTW, sorting alphabetically should sort to CAB

• ###### 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?

TIA, Frank