Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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


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

MV

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

16 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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 ?

Anonymous
Not applicable
Author

Can you upload the file?

maxgro
MVP
MVP

in sort tab

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

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

holmlund
Contributor III
Contributor III

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

Not applicable
Author

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 ?

Not applicable
Author

Hi,

What would the MATCH expression look like in the sort ?

Not applicable
Author

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

Anonymous
Not applicable
Author

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.