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

[#1]

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

[#2]

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

is

[holmlund]

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

[maxgro]

you can use if in expression background color or here

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

[#5]

File has been uploaded for your attention.

Thanks

[kiranmanoharrode]

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

[Anonymous · Accepted solution]

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.

[maxgro]

see image

or attachment