topic sort dimension in bar chart in QlikView

sort dimension in bar chart

chriscools — Sun, 17 Oct 2010 08:09:17 GMT

Hello,

i have a bar chart with 8 different bars ( thanx to KuroKarl and Jonathan Dienst! [:)] ) ,

and i would like to sort them in a way i choose myself.

i can't use the methods from QV, it's not alfabethical or numerical that i wan't it.

is there a possibility to sort them in a manner i can decide myself? for example by typing the values somewhere by in an order

i want them? or by some kind of formula where i can specify the order?

thanx!

grtz,

chris

sort dimension in bar chart

jonathandienst — Sun, 17 Oct 2010 08:24:58 GMT

Chris

On the Chart Properties | Sort tab, you have several sort methods other than alpha/numerical. The three that may interest you are:

Sort by Y value - ranks the bars from largest to smallest or smallest to largest
Expression - allows you to enter any expression and the bars will be sorted by the results of the expression. For this to work, the expression value must be sensitive the field(s) used in the chart dimension
Load order - this is the original order in which the dimension field was loaded (grayed out for a calculated dimension)

Another option is to use a dual field for the dimension. Here is a script example of a dual field from an inline:

LOAD Dual(BandName, DistMin) AS DistanceBand,
DistMin,
DistMax
INLINE
[
BandName, DistMin, DistMax
'No move', 0, 0
'<1000km', 1, 1000
'1001-5000km', 1000, 5000
'5001-10000km', 5000, 10000
'10000-20000km', 10000, 20000
'>20000km', 20000,
];

This field, when used as a dimension, can be sorted by the numerical part. You can specify the sort order in any way that you like.

Choose the method that best suits your application.

Jonathan

sort dimension in bar chart

chriscools — Sun, 17 Oct 2010 13:01:12 GMT

Hey Jonathan,

thanx for your reply!

i thing the inline solution with the dual field provides the most flexibility, so i want to go for that option.

sadly i must be doing something wrong because it doesn't work...

i have this statement in the script:

load dual ('status bestelbon', volgorde) as [status bestelbon] inline
[ 'status bestelbon' , volgorde
'in picking gezet', 1
'in picking', 2
'picking ok', 3
inpak, 4
'inpak ok', 5
aanvul, 6
'aanvul ok', 7
];

when i rename the dual field as 'status bestelbons' it makes a key with the same field in the data table.

when i try to sort the dimension 'status bestelbons' by numerical value, it puts 'aanvul' up front...

any idea what i might be doing wrong?

grtz,

chris

sort dimension in bar chart

jonathandienst — Sun, 17 Oct 2010 13:49:49 GMT

Chris

I am not sure what is wrong, but try this:

load dual (status, volgorde) as [status bestelbon] inline
[
status, volgorde
'in picking gezet', 1
'in picking', 2
'picking ok', 3
'inpak', 4
'inpak ok', 5
'aanvul', 6
'aanvul ok', 7
];

Jonathan

sort dimension in bar chart

chriscools — Sun, 17 Oct 2010 19:26:35 GMT

Hello,

i can't seem to get it working...

this is the script (reduced to keep it simple!):

load dual (status , volgorde) as [status bestelbon] inline
[
status, volgorde
'in picking gezet', 1
'in picking', 2
'picking ok', 3
inpak, 4
'inpak ok', 5
aanvul, 6
'aanvul ok', 7
'in controle gezet', 8
'in controle', 9
'controle ok' , 10
gewikkeld, 11
'gefactureerd', 12
wachtend, 13
geannuleerd, 14
geleverd, 15
verzonden, 16
];

LOAD bestelbonnummer,
[status bestelbon],
FROM

(biff, embedded labels, table is [status pickbons$]);

The dimension i use in the bar chart is [status bestelbon],

the expression is:

count ( {$ < [status bestelbon] ={ 'in picking gezet' , 'in picking' , 'picking ok' , 'inpak', 'inpak ok', 'aanvul' , 'aanvul ok'} > } bestelbonnummer)

the dimension is then sorted by number.

but the result is this:

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7711.qv-table.docx:550:100]

i don't know if you will be able to see the picture of the bar chart, but the order that it shows is this:

picking ok, aanvul ok, inpak, inpak ok, in picking, aanvul, in picking gezet.

i hope it can be solved in any way, because this makes the bar chart useless...

thanx,

chris

sort dimension in bar chart

jonathandienst — Sun, 17 Oct 2010 19:51:49 GMT

Chris

I see the bar chart but I don't see anything that looks wrong (other than the output !)

Can you post your QVW so that I can have a look and hopefully see what is going on. If the data set is too large, or sensitive, create a subset and post that. It might also be useful to see a sample of the data from the spreadsheet.

Jonathan

sort dimension in bar chart

chriscools — Sun, 17 Oct 2010 20:06:57 GMT

Jonathan,

it seems i can only include one file at a time.

i will send the spreadsheet in the next post!

grtz

sort dimension in bar chart

chriscools — Sun, 17 Oct 2010 20:09:48 GMT

this is the spreadsheet!

grtz

sort dimension in bar chart

jonathandienst — Sun, 17 Oct 2010 21:10:43 GMT

Chris

The problem was that the field was not properly joining to the dual values - the load from the spreadsheet simply created non-dual plain text values that looked the same (but were not) and these were added to the [status bestelbon] field. For this type of problem, I add two listboxes to the model - one containing the field ([status bestelbon]) and one containing the expression num([status bestelbon]). Then it becomes easier to see that the [status bestelbon] values from the spreadsheet were not asscoiated with the dual values loaded inline.

The attached model solves this by:

inline loading a mapping table that maps the status bestelbons to the volgorde values
loading the main table (the mapping will be applied in sbnumber. sbtext contains the unconverted status bestelbons values)
joining the table to itself, building the dual value from the sbtext and sbnumber fields
dropping the now unneeded sbtext and sbnumber temp fields

This forces the load to use the correct values.

Jonathan

Re: sort dimension in bar chart

pgalvezt — Wed, 13 Jun 2012 16:21:00 GMT

Hello! You have to use the Match() Function.

Hope that help you.