topic Re: How to sort x-axis dimension according to a string? in QlikView

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

Tue, 29 Apr 2014 14:25:05 GMT

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?

jonathandienst — Tue, 29 Apr 2014 14:33:19 GMT

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?

Wed, 30 Apr 2014 05:35:26 GMT

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