topic Re: Sort order first on ... then on ... in QlikView

Sort order first on ... then on ...

Wed, 09 Sep 2015 16:25:30 GMT

Hi,

I have a bar chart which shows the development of skills per employee. Some explanation: Employees get a skill rating between 1 and 4 for a current, short term and long term level. The bar chart shows the growth of the employee over time. It's a stacked bar chart with three expressions:

1. current level

2. short term level - current level

3. long term level - short term level

See picture below (ST = short term LT= Long term)

I want to sort the bar chart in this manner:

first on current level

then on growth short term

then on growth long term

The first bars should only show people who don't have to learn.

the next bars should show people who only have to learn on a short term

then the bars should show people who have to learn on the short term and long term

then the bars should show people who only have to learn on the long term.

To make more complicated (and more readable) the current level is leading, first the growth of all the current 1, then current 2, etc..

I've some sort expressions but i can't get it to work. It should look like this:

This one happens to be correct, no idea why.

My current sort expression is:

[Current level]&IF(([Target level long term]-[Target level short term])=0,([Target level short term]-[Current level]),

IF(([Target level long term]-[Target level short term])=([Target level short term]-[Current level]), [Target level short term],

IF(([Target level long term]-[Target level short term])>([Target level short term]-[Current level]), [Target level long term])))

Thanks for your help!

Re: Sort order first on ... then on ...

Oleg_Troyansky — Wed, 09 Sep 2015 16:51:50 GMT

Hi,

I'm not totally sure what was your question here... I assume you want to improve your sorting expression or find a mroe elegant one.

What I'd suggest is calculating a "composite" sorting value based on the three conditions that you listed. Since all of your values range from 1 to 4, it's quite easy to build a 3-digit number, where each digit is responsible for a certain condition. Something like this:

[Current level] * 100 +

([Target level short term]-[Current level]) * 10 +

([Target level long term]-[Target level short term]) * 1

if I got your logic correctly...

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Sort order first on ... then on ...

Wed, 09 Sep 2015 21:33:42 GMT

Tried something like this before but somehow it doesn't quite do the job, sometimes it orders perfect, sometimes a weird high value comes in between.. i'll keep on searching but thanks for the reply!

example of weird value:

one that does order correctly (could be luck 😞