topic Re: How do I display a stacked bar chart in a specific order ? in QlikView

How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 17:21:00 GMT

Hi,

As a novice QV designer I am experimenting with some Olympics Games data to create a dashboard. Each table record is an individual medal winner and includes a Medal field (GOLD, SILVER or BRONZE) and a Ranking field (1, 2 or 3) which qualifies the medal type.

I want to display my data as a stacked bar chart showing the number of medals won by a selected country at each Olympic Games. The Y axis will show the number of medals won by that country. And the X axis will show the Olympic years. Each vertical bar will be split into Gold, Silver and Bronze portions (from top to bottom) and coloured appropriately.

I can create the chart ok but the portions of each bar are in the wrong order. If I sort alphabetically I get BRONZE, GOLD, SILVER. I've tried a few options but none seem to work. How can I use the Ranking field to determine the correct stacking sequence of GOLD, SILVER, BRONZE ?

Regards

Hi Michael, I've uploaded the QV file as requested. MV

Re: How do I display a stacked bar chart in a specific order ?

Anonymous — Thu, 10 Apr 2014 17:53:55 GMT

In the script, create medal type as dual, for example:

MedalTypeMap:

MAPPING LOAD * INLINE [

A, B

GOLD, 1

SILVER, 2

BRONZE, 3];

LOAD

...

dual("medal type", applymap('MedalTypeMap',"medal type")) as "medal type",

...

After this each medal type has dual value - text and numeric. Sort in chart by number.

Regards,

Michael

Re: How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 18:11:50 GMT

Hi Michael,

Each bar now displays (from top to bottom) BRONZE, SILVER, GOLD. I tried Sort Numeric Ascending and Descending but it doesn't make any difference.

What do the ellipsis ... refer to in the script ? Other code ? Doesn't it matter where that last line of code is entered ?

Re: How do I display a stacked bar chart in a specific order ?

Anonymous — Thu, 10 Apr 2014 18:18:09 GMT

Can you upload the file?

Re: How do I display a stacked bar chart in a specific order ?

maxgro — Thu, 10 Apr 2014 18:34:05 GMT

in sort tab

sort the second dimension (I suppose medal type) only by expression with expression

=wildmatch(YourMedalTypeField, '*GOLD*', '*SILVER*', '*BRONZE*', '*')

Re: How do I display a stacked bar chart in a specific order ?

holmlund — Thu, 10 Apr 2014 18:35:13 GMT

You may also sort by expression and use the match function...

Re: How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 19:18:26 GMT

Hi,

Do you mean the .qvw document file ? I'm loading 37000 records from my Excel table so it's quite a big file.

How do I upload it ?

Re: How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 19:19:28 GMT

Hi,

What would the MATCH expression look like in the sort ?

Re: How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 19:19:54 GMT

MedalSort:

LOAD * INLINE [

Medal, Medal_Sort_Order

GOLD, 1

SILVER, 2

BRONZE, 3];

Make sure this new table is associated to your existing table on Medal field.

and in chart properties under Sort tab: Select expression and type =Medal_Sort_Order

Qlikview Bitz and Pieces: Custom Sort in Qlikview

Re: How do I display a stacked bar chart in a specific order ?

Anonymous — Thu, 10 Apr 2014 19:24:43 GMT

You can reduce the document before uploading, make sure it is less than 1MB please.

To upload, click "use advanced editor" on the top right, and there will be attache option.

Re: How do I display a stacked bar chart in a specific order ?

Thu, 10 Apr 2014 19:35:38 GMT

Hi Massimo,

I tried this but the colours don't match those in the Colors 1-6 of the Colors tab.

I've attached 3 screenshots as follows:

1. The chart object

2. The Sort tab entries for the GSB field (Medal Type)

3. The Colors tab entries for the chart

Hope this helps

Re: How do I display a stacked bar chart in a specific order ?

holmlund — Thu, 10 Apr 2014 19:41:03 GMT

=match(YourMedalTypeField, 'GOLD', 'SILVER', 'BRONZE')

Re: How do I display a stacked bar chart in a specific order ?

maxgro — Thu, 10 Apr 2014 19:55:53 GMT

you can use if in expression background color or here

http://community.qlik.com/blogs/qlikviewdesignblog/2012/12/04/colors-in-charts#comment-13693

Re: How do I display a stacked bar chart in a specific order ?

Fri, 11 Apr 2014 06:22:04 GMT

File has been uploaded for your attention.

Thanks

Re: How do I display a stacked bar chart in a specific order ?

kiranmanoharrode — Fri, 11 Apr 2014 07:05:15 GMT

Use below expression for sorting GSB dimension

=if(wildmatch(GSB,'*GOLD*'),1,

if(Wildmatch(GSB,'*SILVER*'),2,

if(WildMatch(GSB,'*BRONZE*'),3)))

and make sorting order 'Descending' in sort tab of chart properties

Regads

Kiran

Re: How do I display a stacked bar chart in a specific order ?

Anonymous — Fri, 11 Apr 2014 12:06:42 GMT

Fixing the script:

MedalTypeMap:
MAPPING LOAD * INLINE [
     A,        B
     GOLD,     1
     SILVER,   2
     BRONZE,   3];

Medallists:
LOAD Year,
     Season,
     Sport,
     Gender,
     Event,
     Athlete,
     Sex,
     Team,
   Dual("GSB", applymap('MedalTypeMap',"GSB")) AS "GSB",
     Rank,
     Type,
     Result,
     Medal,
     Record
FROM
Data\Medallists11.xlsm
(ooxml, embedded labels, table is Medallists);

Games:
LOAD Year,
     Season,
     City,
     Country
FROM
Data\Medallists11.xlsm
(ooxml, embedded labels, table is Games);

Flags:
INFO LOAD Team,
Flag
FROM
Data\FlagsOECD.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Now it will be dual, that is sortable by number.

Re: Re: How do I display a stacked bar chart in a specific order ?

maxgro — Sun, 13 Apr 2014 18:33:42 GMT

see image

or attachment