topic Re: How to sort the dimension in the bar chart? in QlikView

How to sort the dimension in the bar chart?

bhaveshp90 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a bar chart with GP_Bucket as dimension and Count, Sales as expressions as shown below;

I want to sort the dimension in the following order > '<10%','10-14%','14-20%','20-25%', '25-30%', '30-35%', '35-40%', '>40%'

I tried to use the sort -> expression as

match(GP_Bucket,'<10%','10-14%','14-20%','20-25%', '25-30%', '30-35%', '35-40%', '>40%')

which didn't sort as per the order as shown in the image above.

I even tried using in the back end script as (Sort -> expression -> GM_Bucket_Sort)

if(GP_Bucket = '<10%', 1,

if(GP_Bucket= '10-14%',2,

if(GP_Bucket = '14-20%',3,

if(GP_Bucket = '20-25%',4,

if(GP_Bucket = '25-30%',5,

if(GP_Bucket = '30-35%',6,

if(GP_Bucket = '35-40%',7,

if(GP_Bucket = '>40%',8)))))))) as GM_Bucket_Sort,

Is there any other way to sort the dimension, or am I doing wrong here? any help is appreciated.

thanks

Bhavesh

Re: How to sort the dimension in the bar chart?

kingsleyh — Tue, 23 Oct 2018 13:09:44 GMT

Hi Bhavesh.

Are you able to supply us with a qvw?

Re: How to sort the dimension in the bar chart?

Gysbert_Wassenaar — Tue, 23 Oct 2018 13:13:54 GMT

How is the field GP_Bucket created in the script? Or is it a calculated dimension?

Re: How to sort the dimension in the bar chart?

sudeepkm — Tue, 23 Oct 2018 13:16:05 GMT

create an inline load like below just before the Load script where you have the field GP_Bucket.

GP_BucketSort:

Load * inline [

GP_Bucket

<10%

10-14%

14-20%

20-25%

25-30%30-35%

35-40%

>40%];

Reload the app and then in your Bar Chart Properties > Sort > Select Dimension GP_Bucket and then select sort by

Load Order > Original

Re: How to sort the dimension in the bar chart?

niclaz79 — Tue, 23 Oct 2018 13:17:46 GMT

Hi,

You GM_Bucket_Sort should work. I would set the expression:

Max({1}GM_Bucket_Sort) myself.

Make sure that the sort expression is the first ranked one and there are no other check boxes clicked on.

Re: How to sort the dimension in the bar chart?

achettipalli — Tue, 23 Oct 2018 13:23:56 GMT

Post your workbook if you still didn't get the desired result.

Re: How to sort the dimension in the bar chart?

bhaveshp90 — Tue, 23 Oct 2018 14:35:58 GMT

This is how I created in the scipt ;

if(GM_As_Sold <= 10, '<10%',

if(GM_As_Sold > 10 and GM_As_Sold <= 14 ,'10-14%',

if(GM_As_Sold > 14 and GM_As_Sold <= 20, '14-20%',

if(GM_As_Sold > 20 and GM_As_Sold <= 25, '20-25%',

if(GM_As_Sold > 25 and GM_As_Sold <= 30, '25-30%',

if(GM_As_Sold > 30 and GM_As_Sold <= 35, '30-35%',

if(GM_As_Sold > 35 and GM_As_Sold <= 40, '35-40%',

if(GM_As_Sold > 40, '>40%')))))))) as GM_Bucket,

Re: How to sort the dimension in the bar chart?

bhaveshp90 — Tue, 23 Oct 2018 14:42:25 GMT

thank you Niclas, it worked like a charm