As we continue global recognition month for #WOW2023, Flavio Matos introduced this challenge which displays a unit chart of wines by type.

An added twist was to provide the ability for the user to switch between English (UK) and Portuguese (Brazil) languages, and the excel data set provided a sheet with the data per language.

After completing my solution, I checked out Flavio, and found he built the chart for each language (and so data source) in 2 separate sheets, added to 2 different dashboards and used navigation button with a flag image to switch between 2 dashboards. I chose to go a different route, one that didn’t mean duplicating the viz. This means that if I had to alter the viz in future for some reason, I’m only doing it once.

Modelling the data

To build in a single sheet, I needed to have the data for two languages combined into one table. Connect to the excel workbook and add the Wine sheet to the canvas, then add a relation to the Vinho sheet and set the relationship to match on the Wine field from each sheet.

Building the Viz

We need a parameter to manage the language selection

pCountry

string parameter defaulted to ‘UK’

This parameter is then used to determine which of the fields we need to use on the viz, and these are determined through calculated fields.

Country to Display

IIF([pCountry]=’UK’, [Category],[Family])

Add this to Rows then add Abreviation to the Detail shelf. Sort the Country To Display field by the count of the Abbreviation field descending.

Change the Mark type to Square then add Abreviation to the Label shelf. Adjust the sheet to Entire View and then align the label to be middle centre.

Create a parameter to store the list of English options that can be selected

pOptions-UK

string parameter containing the list of options (taken from the requirements page), with All listed first. Default to All.

Then create a version to store the Portuguese options

pOptions-Portuguese

string parameter containing the list of options (taken from the requirements page), with Todos listed first. Default to Todos.

To flag the wines that are related to the options selected, we need

Wine has Tag

IF [pCountry] = ‘UK’ THEN
IF [pOptions-UK] = ‘All’ THEN TRUE
ELSEIF CONTAINS([Tags],[pOptions-UK]) THEN TRUE
ELSE FALSE
END
ELSE
IF [pOptions-Portuguese]=’Todos’ THEN TRUE
ELSEIF CONTAINS([Tags (Vinho)],[pOptions-Portuguese]) THEN TRUE
ELSE FALSE
END
END

This returns true or false based on what country has been selected, and in turn what country specific option has been selected. If the wine is tagged with that option, or all wines have been selected then true is returned, otherwise false.

And then using this field we can determine how to colour the squares.

Colour

IF [Wine has Tag] THEN
IF [pCountry]=’UK’ THEN [Type]
ELSE
IF [Type] = ‘Red’ THEN ‘Tinto’
ELSEIF [Type] = ‘Sparkling’ THEN ‘Espumante’
ELSEIF [Type] = ‘White’ THEN ‘Branco’
ELSE [Type]
END
END
ELSE
IIF([pCountry]=’UK’, ‘No Highlight’,’Nao Harmoniza’)
END

This is slightly more detailed as the Type field in each of the excel sheets are both stored in English, but the legend in the viz shows the types in Portuguese when that language is selected. Ideally the Type should have just come straight from the data source.

Add the 3 parameters to the view, and then add Colour to the Colour shelf. Set the pCountry parameter to UK, and choose Appetizers from the pOptions-UK parameter. Adjust the colours to suit. Manually sort the colour legend options, so the colours are listed Red > Rose > Sparkling > White > No Highlight.

Then clear the pCountry parameter (or set it to any value other than UK) and set the pOptions-Portuguese parameter to Aperitivos. Once again adjust colours as required and re-order.

The tooltips need to be language specific, and only display for the wines that match the options chosen. For these we need the following calculated fields

Tooltip – Wine

IF [Wine has Tag] THEN [Wine (Wine)] ELSE ” END

Tooltip – Food Pairing

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,[Food pairing],[Food pairing (Vinho)])
ELSE ”
END

Tooltip – Food Pairing Label

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,’Food pairing:’, ‘Harmoniza com:’)
ELSE ”
END

Tooltip – Notes

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,[Notes],[Notes (Vinho)])
ELSE ”
END

Add all four of these fields to the Tooltip shelf and adjust accordingly

Finally tidy up the formatting by removing row dividers and hiding the Country to Display column heading (hide field labels for rows).

Building the Country Selector

For this I needed to add the UK and Brazilian flags a custom shapes to my Tableau repository. I just sourced some images via my favourite search engine and added them to my repository as per the instructions here.

On a new sheet I then added Abreviation to Columns and also to Filter and just filtered to the first 2 options Ab and Ag. I changed the mark type to shape. I then created the field

Country Selector

IIF([Abreviation]=’Ab’,’UK’,’Brazil’)

and added this to the Shape shelf, and selected the two flags.

Remove the row dividers and hide the header (uncheck show header on the Abreviation pill).

Building the dashboard

Add the sheets to your dashboard and display both the options parameter and the colour legend. I used layout containers for most of the arrangement, but floated the colour selector viz. I then added the title in English and Portuguese in two separate text boxes. I also hid the title of the colour legend, and add 2 different text boxes with the colour legend title in English and Portuguese. My layout looked like

To only show the sections based on the language selected, we need the following fields

Country is UK

[pCountry]=’UK’

Country is Brazil

[pCountry]<>’UK’

On the dashboard, select the text box which contains the English title, then on the Layout tab, select the Control visibility using value checkbox, and choose the Country is UK field.

Repeat this for all the other objects – the other title, the food selector parameters and the colour legend titles, choosing the Country is UK or the Country is Brazil option as appropriate.

To switch the language, we need to add a dashboard parameter action

Select Country

On selection of the Country Selector viz, set the pCountry parameter by passing in the value from the Country Selectior field.

The final step is to stop the country flags from being ”greyed out’ ‘highlighted’ when one is selected (ie the other flag ‘greying out’).

Create a fields

True

TRUE

False

FALSE

and add these 2 fields to the Detail shelf of the Country Selector worksheet.

The back on the dashboard, add a dashboard filter action

Country Selector – Unhighlight

on selection of the Country Selector sheet on the dashboard, target the Country selector sheet directly, passing the fields True = False. Show all values if the selection is cleared.

And with that, the viz should be complete. My published version is here.

Happy vizzin’!

Donna