Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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 ?
Can you upload the file?
in sort tab
sort the second dimension (I suppose medal type) only by expression with expression
=wildmatch(YourMedalTypeField, '*GOLD*', '*SILVER*', '*BRONZE*', '*')
You may also sort by expression and use the match function...
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 ?
Hi,
What would the MATCH expression look like in the sort ?
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
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.