Can you create a Monthly Driver Analysis dashboard?

Donna ColesLeave a comment

Global Recognition month continued this week for #WOW2023, and I was able to enlist Norbert Borbas to set the challenge this week, which was published in both Norbert’s native Hungarian, as well as English.

Norbert provided a challenge based on a solution he had implemented at his company, and involved the creation of 2 dashboards with interactivity between them both. There’s a fair amount going on with this one, so let’s get cracking.

Building the Sales KPI

For this viz, we need to get information about the latest year sales in conjunction with the previous year. Rather than hardcode any years relating to the data, I created

Latest Year

{MAX(YEAR([Order Date]))}

which for the data set I was using, returns 2022. Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line).

With this I then created

LY Sales

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

to the sales for 2022. Format this to $ with 0 dp.

To get the sale for the previous year (ie 2021) I created

Previous Year

[Latest Year]-1

Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line), and then create

PY Sales

IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END

Format this to $ with 0 dp.

We then needed the % difference between these values

% Diff Sales From PY

(SUM([LY Sales]) – SUM([PY Sales]))/SUM([PY Sales])

Apply custom formatting set to +0.0%;-0.0%;0.0% which explicitly adds the + sign in front of positive values.

To help with the interactivity that is required in the dashboard, I also created

Sales Label

‘Sales’

With all these, we can build the KPI ‘card’.

Add Sales Label to Detail, and LY Sales, PY Sales, Previous Year and % Diff Sales From PY to the Label/Text shelf.

Format the text so it is aligned middle centre, and the arrange the text as required

Set the view to Entire View and stop the Tooltip from displaying (uncheck show tooltip). Name the sheet Sales KPI.

Building the Profit KPI

Repeat similar steps as above, apply formatting to the fields as required. You’ll need

LY Profit

IF YEAR([Order Date]) = [Latest Year] THEN [Profit] END

PY Profit

IF YEAR([Order Date]) = [Previous Year] THEN [Profit] END

% Diff Profit From PY

(SUM([LY Profit]) – SUM([PY Profit]))/SUM([PY Profit])

and

Profit Label

‘Profit’

Name the sheet Profit KPI.

Building the Line Chart

The line chart needs to change based on whether the Sales KPI or the Profit KPI sheet has been selected. We need a parameter to capture this ‘decision’.

pMeasureToShow

string parameter defaulted to Sales

To the determine which actual value to display we need

Line – Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END

format this to $ with 0dp.

I also created

Line – Measure to Display Axis (k)

IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END

ie the same field, but this was formatted to $ with 0 dp but display units of Thousands (k).

Having the two fields means that the axis can display in one format while the tooltip can show the more detailed value.

On a new sheet add Order Date to Columns and change to the discrete ‘month’ level (blue pill – May). Add Line – Measure to Display Axis (k) to Rows. Add Order Date to Colour. This will default to the Year level, and show all years from the data set, but we only want the latest 2 years. So create

Filter Years

YEAR([Order Date]) >= [Previous Year]

and add to the Filter shelf and set to True. Adjust colours to suit.

Add Order Date to the Label shelf and change to the Year level. By default the lines should be labelled. Edit the label and set the font to match mark colour. I also set the font to be Tableau Medium and bold. Adjust the order of the years in the colour legend, so 2022 is listed first which makes the line for 2022 sit ‘on top’ of the 2021 line.

Remove all gridlines/row column dividers, and set the axis lines to be bolder. Hide the Order Date label (right click > hide field labels for columns). Adjust the formatting of the Order Date axis, to display the months in an abbreviated form. Adjust the title of the y-axis to reference the pMeasureToShow parameter (right click the axis > edit).

Add Line – Measure to Display to the Tooltip shelf.

Adjust the Tooltip to display as

Finally, to help with the interactivity later, we will need

Month Order Date

DATEPART(‘month’, [Order Date])

This returns the number of the month ie 1 for January, 2 for February etc. Move this to the ‘dimensions’ section of the left hand data pane (drag above the line), and then add this to the Detail shelf. Change the field to be a discrete attribute

Name the sheet Line Chart.

Building the Symbol Chart

On a new sheet add Filter Years to the Filter shelf and set to True. Add Order Date to Columns and change to be at the discrete month level. Double click into the Rows shelf and manually type in MIN(0). Add Month Order Date to the Detail shelf.

We need to display coloured arrows depending on whether the change is up or down. For this we need

Symbol – Difference to Display is +ve

IF [pMeasureToShow] = ‘Sales’ THEN IIF([% Diff Sales From PY]>=0,TRUE,FALSE)
ELSE IIF([% Diff Profit From PY]>=0,TRUE,FALSE)
END

If the measure to display is Sales, and the difference in Sales from previous year is +ve, then return true, otherwise false, Else if the measure to display is Profit and the difference in Profit from the previous year is +ve, then return true, else false.

Change the mark type to Shape and then add this field to both the Colour shelf and the Shape shelf. Adjust colours and shapes accordingly.

Edit the axis and delete the title and set the major and minor tick marks to None. We need the axis to remain as we will need to ‘line up’ this chart with the line chart, and having a left hand axis will help.

Hide the months from showing (uncheck show header against the pill on Columns. Hide all gridlines, axis lines, zero lines & row/column dividers.

Name the sheet Symbol Chart.

Building the Main dashboard

Using horizontal and vertical layout containers, position the sheets in the required locations along with the title and the instructional text. Use background colours and inner & outer padding to give space between the objects.

For the line chart and symbol chart, these were placed in a vertical container, and the width of the ‘blank’ y-axis on the symbol chart widened to be in line with the axis on the line chart. The hierarchy of objects I used is pictured.

To make the Sales display on the line chart when the Sales KPI sheet is clicked, create a dashboard parameter action

Show Sales Line

On select of the Sales KPI sheet, set the pMeasureToShow parameter, passing in the value from the Sales Label field. When the selection is cleared, keep the parameter set to the current value.

And create a similar action to show the profit

Show Profit Line

On select of the Profit KPI sheet, set the pMeasureToShow parameter, passing in the value from the Profit Label field. When the selection is cleared, keep the parameter set to the current value.

We will need to return to this later, to add more interactivity, but for now we’ll move onto the analysis/drill down sheet.

Building the drill down table

We’re going to need a few more fields to build this type of display. Firstly, for the first bar chart column we need

Bar – LY Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [LY Sales] ELSE [LY Profit] END

Bar – PY Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [PY Sales] ELSE [PY Profit] END

Format both these fields to $ with 0dp.

Add State/Province to Rows and Bar – LY Measure to Display to Columns. Sort the states by the measure descending. Adjust the colour of the bar to suit. Add Bar – PY Measure to Display to the Detail shelf and add a reference line per cell that displays the average of this measure.

Show mark labels and adjust the font of the labels to be size 8pt. Widen each row, and align the State labels to the left, and change the font to be bold & black. Reduce the Size of the bars. Remove gridlines, but add row dividers.

Double click into the columns shelf and manually type MIN(0) to create a ‘fake’ axis and generate a MIN(0) marks card.

Create a new field

Measure Rank LY

RANK(SUM([Bar – LY Measure to Display]))

and add this to the Label shelf of the MIN(0) marks card. Adjust the table calculation so it is explicitly set to Compute by State/Province. Remove the Bar – PY Measure to Display field from the Detail shelf. Change the mark type to shape.

To determine what type of shape and what colour to apply, we need

Measure Rank PY

RANK(SUM([Bar -PY Measure to Display]))

and then

Measure Rank Change

IF [Measure Rank LY] < [Measure Rank PY] THEN ‘Up’ ELSEIF [Measure Rank LY] > [Measure Rank PY] THEN ‘Down’
ELSEIF ISNULL([Measure Rank LY]) THEN NULL
ELSE ‘N/A’
END

Add this field to both the Shape and the Colour shelf, and adjust the table calculation so it is explicitly set to Compute by State/Province.

Adjust the shapes, and use a transparent shape against the Null option (see here for details). Adjust colours to suit. Increase the size of the shape, and align the label to the left.

For the next column, create

Bar – Measure Difference

SUM([Bar – LY Measure to Display]) – SUM([Bar -PY Measure to Display])

and custom format to +”$”#,##0;-“$”#,##0

Add to the Columns shelf, and labels should automatically get added.

Create a field

Bar – Measure Diff is +ve

[Bar – Measure Difference] >=0

and add to the Colour shelf of this marks card. Adjust colours to suit.

For the final column, we need to separately identify the values when the YoY measure difference is positive from those that are negative, and then apply ranking to each of these fields. So we need

+ve Measure Diff

IF [Bar – Measure Diff is +ve] THEN [Bar – Measure Difference] END

-ve Measure Diff

IF NOT([Bar – Measure Diff is +ve]) THEN [Bar – Measure Difference]*-1 END

Note, as the difference in this instance is negative, the values returned will also be negative, but when it comes to ranking, we want the record with the biggest negative difference to be ranked 1st ie if one value had a difference of -10 and another had a value of -100, in typical ranking, -10 is ‘higher’ than -100, so -10 would be ranked 1 and -100 2. But we want -100 to be ranked 1. So by multiplying the values by -1 in the calculation we actually return values 10 and 100. So when we rank them later, 100 is ranked 1 as it is bigger than 10.

Ranke +ve Measure Diff

RANK_UNIQUE([+ve Measure Diff])

Rank -ve Measure Diff

RANK_UNIQUE([-ve Measure Diff])

We will be displaying the information for the positive and negative ranks in separate ‘columns’ which we can do with

Rank YoY X-axis

IIF([Bar – Measure Diff is +ve], 1,2)

Add this field to columns and change the mark type to Circle. Add Bar – Measure Diff is +ve to Colour. Add Rank +ve Measure Diff and Rank -ve Measure Diff to Label. Ensure the table calculations for both fields are explicitly set to Compute by State/Province. Increase the Size of the circle, and align the label to be middle centre using a bold white font.

Now we have all the information displayed, we need to sort the tooltips.

This sheet, will be accessed through interaction and will be ‘filtered’ to just a specific month. For now, we’ll ‘hardcode’ the month by adding Month Order Date to the Filter shelf and selecting 3 (for March).

On the All marks card, add Latest Year, Previous Year, Month Order Date, Measure Rank PY to the Tooltip shelf.

We will also need

TOOLTIP – Rank statement decrease

IF [Measure Rank Change] <> ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank decreased vs. same month last year.’ END

TOOLTIP – Rank statement increase

IF [Measure Rank Change] = ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank increased vs. same month last year.’ END

TOOLTIP – Rank YoY Statement Negative

IF NOT([Bar – Measure Diff is +ve]) THEN MIN([State/Province]) + ‘ is a negative (Rank: ‘ + STR([Rank -ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END

TOOLTIP – Rank YoY Statement Positive

IF [Bar – Measure Diff is +ve] THEN MIN([State/Province]) + ‘ is a positive (Rank: ‘ + STR([Rank +ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END

Add all 4 of these fields to the Tooltip shelf of the All marks card. Ensure all the table calculation fields are set to explicitly Compute by State/Province.

Now adjust the tooltip with all the relevant fields, applying colouring as required

Hide the axis and hide the null indicator. Hide the State/Province column label heading. Finally, remove the Month Order Date field from the Filter shelf. The tooltip will look a bit funny at this point, but that will get sorted later.

Name the sheet Drill Down Table.

Building the drill down dashboard

Again using vertical and horizontal containers, arrange the sheet on a dashboard along with the title. Use text boxes arranged in a horizontal container directly above the Drill Down Table sheet to display the column headings.

As I didn’t want to hardcode any years, I created the following parameters

pLatestYear

integer parameter defaulted to 2022 and with a display format that did not include thousand separators.

and

pPreviousYear

integer parameter defaulted to 2021 and with a display format that did not include thousand separators.

and

pMonth

integer parameter defaulted to 3 and with a display format that did not include thousand separators.

When building the column headings, I referenced all these parameters instead.

To ‘set’ these parameters, I added Previous Year and Latest Year to the Detail shelf of both the Line Chart and Symbol Chart sheets.

I then added 3 dashboard parameter actions to the main dashboard which on select of the Line Chart or Symbol Chart sheet, set the relevant parameter with the value from the appropriate field.

To ensure the drill down gets ‘filtered’ to the month selected on the main dashboard, add a dashboard filter action

Drill down

On select of the Line Chart or the Symbol Chart, target the Drill Down Table sheet on the Drill Down dashboard, passing the selected field of MONTH(Order Date) only. Exclude all values when selection is cleared.

The final step is to add a Navigation Button to the drill down dashboard which displays the text ‘Go back to landing page’ and navigates back to the main dashboard.

And hopefully, with all that, you have a completed interactive navigational dashboard! My published version is here.

Happy vizzin’!

Donna

Can you make dynamic indexed bars & measures?

Donna ColesLeave a comment

For this week’s #WOW2023 challenge, guest poster Ervin Vinzon asked us to rebuild this visualisation based on data from his home country, The Philippines.

I have to admit, I did find this a bit tough this week – there was a lot going on and maps don’t come naturally to me. I actually wasn’t sure initially whether both the files were needed, as the requirements were a little bit sparse, and I managed to build pretty much the whole solution not using the zip file. I just couldn’t get the map label annotation to work, so ended up having to had to revisit and start again.

Modelling the data

You will need to download both the excel file and the zip file from the Ervin’s shared area.

In the data pane, connect to the Ph Pop 2020 excel file and add the Philippine Population sheet to the canvas.

Then add a connection to a spatial file and point to the zip file. Tableau will automatically identify the file it can use. Add the Provinces file to the canvas.

Create a relation that uses relationship calculations that maps from the Philippine Population sheet :

IIF([Province] = “Maguindanao del Norte” OR [Province] = “Maguindanao del Sur”, “Maguindanao”, [Province])

to the the Provinces sheet

IIF([ADM1_EN] = “National Capital Region”, “Metro Manila”, [ADM2_EN])

Thanks to Rosario Gauna for helping me with this logic, as I couldn’t figure out how the data needed to be related. I think this really needed to be included in the requirements… (Note the logic has been adjusted since I took the image below)

Building the Measure Selector

We can’t use a parameter directly for this, as the design of the ‘radio button’ is more fancy than just what you get with the basic parameter selection functionality.

So we need to ‘fake’ the selection and can use existing fields in our data set to help with this. The Island Group field contains 3 values, so we’re going to draw on these and build

Measure Selector

CASE [Island group]
WHEN ‘Luzon’ THEN ‘By Population’
WHEN ‘Mindarao’ THEN ‘By Population Density’
ELSE ‘By Area’
END

Add to Columns and manually reorder. In the Rows shelf, double click and manually type MIN(0)Change the mark type to circle and add Measure Selector to the Label shelf. Resize the circles, and adjust the label to be aligned middle right. Change the view to Fit Width to see all the labels.

Create a parameter to capture the selected measure when this view is interacted with

pMeasureSelected

string parameter defaulted to By Population

Show the parameter on the sheet. Then create a calculated field

Is Selected Measure

[pMeasureSelected] = [Measure Selector]

and add to the Colour shelf. Adjust the colours to suit and add a grey border on the circles (via the colour shelf).

Stop the Tooltip from showing, hide the MIN(0) axis and the Measure Selector header and remove all gridlines/zero lines and any dividers. Name the sheet Measure Selection.

Building the bar chart

Firstly, we need to determine which measure we’re going to be displaying, so need

Measure to Display

CASE [pMeasureSelected]
WHEN ‘By Population’ THEN SUM([Population])
WHEN ‘By Population Density’ THEN SUM([Density])
ELSE SUM([Area (sq km)])
END

Show the pMeasureSelected parameter on a new sheet, then add Island Group and Province to Rows and Measure to Display to Text. Sort the data descending.

Create a new calculated field

Measure Rank

RANK_UNIQUE([Measure to Display])

Change the Measure Rank field in the left hand data pane to be discrete. Add to the Rows and adjust the table calculation so it is computing by Province only. The Measure Rank should show sequential numbers from 1 upwards, but restart at the next Island Group.

Add another instance of Measure Rank to the Filter shelf. Select All intially to select all the numbers. Then adjust the table calculation to compute by Province only as above. Then re-edit the filter and just select numbers 1-10.

The bar visual displays the actual value in a coloured bar, along with the maximum value for the measure in a grey bar. So we need

Max Value

WINDOW_MAX([Measure to Display])

Add this to the table and adjust the table calculation to compute by Province.

Finally, we need some information to help with the labels

Label Strapline

CASE [pMeasureSelected]
WHEN ‘By Population’ THEN ”
WHEN ‘By Population Density’ THEN ‘persons per sq km’
ELSE ‘sq km’
END

Add this to Rows and then test the behaviour by adjusting the value of the pMeasureSelected parameter.

We now have the data needed to build the bars.

Move Island Group to Columns and manually reorder to be Luzon, Visayas, Mindanao. Move Province and Label strapline to Text. Move Measure to Display and Max Value to Columns. Set sheet to fit Entire View. Reduce the size of the bar to be relatively thin.

On the Measure to Display marks card, add Island Group to Colour and adjust to suit.

Set the colour of the bar on the Max Value marks card to be pale grey and remove the bar border. Remove the Label Strapline field and move the Province from label to Detail.

Make the chart dual axis and synchronise the axis. Adjust the axis (right click > edit axis) to be independent axis ranges for each row or column.

On the Measure to Display marks card, add Measure Rank and Measure To Display to the Label shelf. adjust the table calculation settings of the Measure Rank field to compute by Province only.

Adjust the label to be aligned top left, and then format the label text box, so the label is laid out as required (I used bold 8pt font). To make the label sit ‘above’ the bar, add carriage returns after the text in the label edit box (thanks to Sam Parsons for spotting this sneaky method – my original build was using a much more complex method to get the text sitting on top of the bars!).

Finally hide the axis and the Measure Rank and Island Group fields. Remove all gridlines/zero lines/axis & row and column dividers. Stop the tooltips from showing. Name the sheet Bars.

Building the Bar Header

On a new sheet, add Island Group to Columns and manually re-order. Then double click in Columns and manually type MIN(0.1). Set the mark type to Bar and set the view to fit Entire View. Add Island Group to Colour. Reduce the Size of the bar. Edit the axis and fix to end at 0.7. Add Island Group to the Label shelf, and align bottom left. Adjust the size of the font to be larger and then add multiple carriage returns above the label text to shift the label to sit under the bar.

Remove all headers/axis and row/column dividers and gridlines. Stop the tooltip from showing.

Adjust the title of the sheet to reference the pMeasureSelected parameter.

Name the sheet Bar Header.

Building the map

We will need another parameter to store the selected Province value.

pSelectedProvince

string parameter defaulted to nothing

On. a new sheet, double click on the Geometry field. This will automatically display a map of the Philippines. Remove all the unnecessary detail via Map > Background Layers and unchecking all the options.

Add Province to the Detail shelf and Region and Island Group to the Tooltip. Adjust the Tooltip.

Show the pSelectedProvince parameter and manually enter the province Leyte.

Create a new field

Is Selected Province

[pSelectedProvince] = [Province]

and then add to the Colour shelf. Adjust the colours to suit (set the NULL field to the same as False).

We need to capture the ‘geometry’ of the selected Province

Selected Province Location

MAKEPOINT((IIF([Is Selected Province],[Latitude],NULL)),(IIF([Is Selected Province],[Longitude],NULL)))

Drag this field onto the canvas and drop it on the Add a marks layer section that displays. This will create a second marks card. Change the mark type to Circle and adjust the colour as required. Add pSelectedProvince to the Detail shelf.

Select the circle mark, and add an annotation against the mark (right click > Annotate > Mark). Reference the parameter pSelectedProvince in the dialog window.

Providing the pSelectedProvince is on the Detail shelf and is referenced in the Annotation, then changing the value of the pSelectedProvince parameter to Samar or any other province, should retain the annotation. Once again, thanks to Sam for figuring this out as I could just not see it, even when I looked at the solution.

Remove row & column dividers. Stop the map options from displaying (Map > Map Options and uncheck all selections). Update the title of the sheet, and then name the sheet Map.

Adding the interactivity

Add the sheets to a dashboard using horizontal and vertical layout containers to arrange the objects.

Update the title of the Measure Selection sheet and the Bar Header sheet to match the text being displayed.

Create a dashboard parameter action to define the measure selection on click

Set Measure

On selection of the Measure Selection sheet, set the pMeasureSelected parameter, passing through the value from the Measure Selector field.

Create another action for the Province

Select Province

On selection of the Bars sheet, set the pSelectedProvince parameter, passing through the value from the Province field. When the selection is cleared, reset to nothing.

To stop the Bar Heading sheet from being clicked on, just float a blank object over the top.

To prevent the other bars and the measure selections from all fading when clicked on, create a new field

HL

‘HL’

and add to the Detail shelf of the Bars sheet and the Measure Selection sheet.

Then back on the dashboard add a dashboard highlight action

Unhighlight

On selection of the Bars sheet and the Measure Selection sheet, target the Bars and the Measure Selection sheets using the HL selected field only.

Now when a bar is clicked, it will look ‘selected’ (has a black bar around it), but the other bars won’t become faded/greyed out. Similarly when a measure is selected, the other circles won’t fade.

Phew! That should be it. There’s a fair amount going on here and lots of tricky ‘gotchas’. My published viz is here .

Happy vizzin’!

Donna

Help me choose my wine!

Donna ColesLeave a comment

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

Can you build an UpSet Plot with just one calculated field?

Donna ColesLeave a comment

For this week’s #WOW2023 challenge , guest poster, Venkatesh Iyer asked us to create an UpSet Plot, with the added requirement of using just 1 calculated field.

To start with, I had to read up on what an UpSet plot was and looked through the blog post by Chris Love that was referenced in the challenge introduction. While this post gave me more clarity, it introduced more calculations than I was hoping for, so I started looking a bit wider for a bit of help. This YouTube video set me on my way.

Setting up the data

The requirements stated to limit to the Category of Furniture only, so after connecting to the Superstore data source, I added a Data Source Filter (right click data source > Edit Data Source filters) to restrict the information throughout the workbook just to the Furniture Category.

Doing this means I don’t have to keep adding the Category to the Filter shelf, and any FIXED LoDs I create will only be based on the subset of data that has been ‘pre-filtered’.

There are multiple charts in this challenge, and I used 5 sheets in total. Let’s start with the easy ones.

Building the Customer List

On a new sheet add Customer Name to Rows and Sales to Text. Format Sales to $ with 0dp, and widen each row. Remove row/column dividers and remove the ‘Customer Name’ column heading (right click and hide field labels for rows). Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Customer List.

Building the Sub-Category Bar Chart

Add Sub-Category to Rows. Add Customer ID to Columns, then use the context menu to change the field to use the Count (Distinct) measure.

Note – I would typically create a specific calculated field containing the function COUNTD([Customer ID]), but as we only want a single calculated field in the solution, then this is the method to adopt.

Sort the resulting bar chart descending, and add Sub-Category to the Colour shelf and adjust to suit.

Widen each row and then click on the Label shelf and check the Show mark labels tick box. Align the labels middle left and format the font. Hide the axis and the Sub-Category column heading (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Sub Cat Bar.

Identifying the groupings

Ok, now we’re at the point we need to identify the different ‘cohorts’ of customers based on what Sub-Categories they have purchased. Let’s build out a tabular ‘check’ sheet so we can see what we’re up to…

On a new sheet add Customer Name and Sub-Category to Rows. This simple table shows us that Aaron Bergman has at some point only ever bought Bookcases & Chairs, while Aaron Hawkins has purchased Chairs & Furnishings. These 2 customers are in different cohorts as they haven’t bought exactly the same combination of Sub-Categories. There are 15 different combinations in total.

Based on what I observed in the video, I can create a FIXED LoD calculated field to identify if a customer has bought Bookcases.

BC

{FIXED [Customer ID]: MAX(IIF([Sub-Category] = ‘Bookcases’,1,0))}

Pop this into the view, and we can see that there is a 1 reported against all the rows associated to each customer who bought Bookcases. So Aaron Bergman has a 1 against both rows, and Aaron Hawkins has 0 against both rows.

Creating similar calculated fields, specific for each of the 4 different Sub-Category values, and putting them into the table, we can see we have various combinations of 1s and 0s for each customer. Adam Shillingburg has bought all 4 types, so has 1’s across the board, while Adrian Sharni has only bought Furnishings, so has 3 0’s and a single 1.

Based on our understanding of what these fields are doing, we can combine what each one is doing into a single calculated string field.

Combo

STR({FIXED [Customer ID]: MAX(IIF([Sub-Category] = ‘Furnishings’,1,0))}) + ‘-‘ +
STR({FIXED [Customer ID]: MAX(IIF([Sub-Category] = ‘Chairs’,1,0))}) + ‘-‘ +
STR({FIXED [Customer ID]: MAX(IIF([Sub-Category] = ‘Tables’,1,0))}) + ‘-‘ +
STR({FIXED [Customer ID]: MAX(IIF([Sub-Category] = ‘Bookcases’,1,0))})

Note the order is based on the order of Sub-Category bar chart. Add this into the view on Rows (rejig the order of the measure values to match).

So with this one calculated field Combo, we now have a dimension we can use to count the customers against. The calculated fields I used to demonstrate the concept are now superfluous and can be deleted if you wish, if you remove the check sheet too. I chose to keep mine in for reference.

Building the Combo bar chart

ON a new sheet, add Combo to Columns and then add Customer ID to Rows, but as before, set it to use the COUNTD aggregation.

Sort the bar to be ascending. Check the Show mark labels option on the Label shelf and adjust the alignment to be bottom middle, and the font to be bold. Change the colour of the bar to suit. Hide the axis and the Combo heading (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Combo Bar.

Building the dot plot

Add Sub-Category to Rows and Combo to Columns. Manually sort the Sub-Category rows so they are listed with Furnishings at the top then Chairs > Tables and Bookcases at the bottom. Sort the Combo field by COUNTD of Customer ID ascending

Double click into Columns and manually type MIN(0) to generate an axis. Change the mark type to circle. Add Sub-Category to Colour. Widen each row.

Add Sub-Category to Label. Then double click into the pill on the label shelf and manually change to add the LEFT function around the pill, so the pill becomes LEFT([Sub-Category],1) to get the initial. Again, this is typically something I would explicitly store in its own calculated field. Manually re-sort the rows again, as this seems to break the sorting.

Align the label middle centre and bold the font. Then add another instance of MIN(0) on Columns to create a 2nd marks card. Change this mark type to Line. Remove the field from the Text shelf, and move the Sub-Category pill from Colour to Path.

Make the chart dual axis and synchronise axis. Right click on the MIN(0) axis title at the top of the chart and move marks to back.

Then hide the axis and the Combo column and Sub-category row (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Adjust the size of the marks as required. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Dot Plot. .

Building the Legend

The quickest way to do this is to duplicate the dot plot sheet. Then remove Combo from the Columns. mark type of the line from line to circle and decrease the size to the smallest possible. Move Sub-Category from Detail to Label and align middle right.

Edit the label and add some spaces in front of the text to push the labels further to the right. Format the font to suit.

Adjust the MIN(0) fields to both be MIN(0.2) instead (just double click into the fields to edit). Then edit the axis to be fixed from 0 to 1. This forces the display to the left.

Hide the axis, and name the sheet Legend.

Adding the interactivity

Arrange the items on a dashboard, using layout containers and padding to organise the 4 main charts and ensure the dot plot aligns both vertically and horizontally with the other two bar charts.. The legend chart is a floating object.

Add a filter dashboard action to filter the customer list

Filter Customers

On select of the Combo Bar or Sub Cat Bar sheets, target the Customer List sheet, showing all values when the selections are cleared.

And that should be it! My published viz is here.

Happy vizzin’!

Donna

Can you create a dynamic small multiple chart?

Donna ColesLeave a comment

For this week’s #WOW2023 challenge, Lorna asked us to recreate this small multiple (or trellis) chart which organises the time series charts per Sub-Category into a grid format, where the number of columns is determined by the user.

Whenever I need to build these types of charts, I often end up referencing this blog post by Chris Love from 2014, as this has the basis for the calculations required.

To get started, we need to capture the number of columns based on a parameter

pCols

integer parameter ranging from 1 to 5 with a step size of 1, that is defaulted to 5

On a new sheet, display the parameter, and add Sub-Category to Rows. Apply a sort to Sub-Category based on the field Sum of Sales descending.

Based on the pCols parameter, we need to determine which column and subsequently which row each Sub-Category should be positioned in. We will make use of the index of each entry in the list. Double click into the Rows shelf and manually type in INDEX(). Change the field to be discrete (blue). This will number every Sub-Category row from 1 upwards. To be explicit, edit the table calculation, to explicitly set it to compute using the Sub-Category dimension.

To determine the column for each sub-category

Column

(INDEX()-1)%[pCols]

the % symbol, is the modulo and returns the remainder when the INDEX()-1 is divided by pCols – ie if INDEX() = 12, then 12-1 = 11 and 11 divided by 5 is 2 with 1 left over, so the result is 1.

Add this to the sheet, set it to be discrete (blue) and also edit the table calculation to compute using Sub-Category. You can see that Chairs and Machines are in the same column. If you adjust pCols, the values will adjust too.

To determine which row each Sub-Category will be positioned in we need

Row

INT((INDEX()-1) / [pCols])

This divides INDEX()-1 by pCols and just returns the whole number. ie if INDEX() = 8, then 8-1 = 7, and 7 divided by 5 = 1.4. The integer part of 1.4 is 1.

Add this to Rows and set to be discrete, and adjust the table calculation as before. You can see Chairs and Phones are in the same row (but different columns), which Chairs and Machines are in the same column, but different rows.

Let’s rearrange – Move Column to Columns, Sub-Category to Text and remove INDEX() altogether, and you’ll get the basic grid layout we need.

Create a new field to store the date part we’re going to present

Month Order Date

DATE(DATETRUNC(‘month’,[Order Date]))

Add this to Columns, and set as exact date and add Sales to Rows and move Sub-Category to Detail. At first gland this may look ok, but if you look closely, you’ll notice that there are multiple lines on some of the charts.

This is because there are some states that didn’t sell some of the sub-categories on the month, and this affects the index() calculation when the Month Order Date is set to be a continuous (green) pill (the viz below highlights this better – Accessories is now indexed with 6 and 7…

So to resolve this, add Month Order Date as a discrete (blue) exact date to the Detail shelf underneath the Sub-Category field. Then change the Month Order Date field in the Columns shelf to be a Continuous (green) attribute. Then adjust the table calculation on both the Column and the Row fields, so they are computing over both Sub-Category and Month Order Date, but at the level of Sub-Category.

Format the ATTR(Month Order Date) field on Columns to be the custom format of yyyy, so the axis just display years

and then format the Month Order Date field on the Detail shelf, to be the custom format of mmmm yyyy, so the information in the Tooltip will display the date as March 2001 etc. Adjust the Tooltip to match.

The label for each Sub-Category needs to be positioned based on the y-axis at the maximum sales across the whole display, and on the x-axis at the last point in the date scale ie December 2023. For this we need

Max Sales in Table

WINDOW_MAX(MAX([Sales]))

Label Position

IF LAST()=0 THEN [Max Sales in Table] END

Add Label Position to Rows and adjust the table calculation so the Max Sales in Table nested calculation is computing by both Sub-Category and Month Order Date, and the Label Position nested calculation is computing by Month Order Date only. This should result in a single mark per Sub-Category displaying.

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. On the Label Position marks card, change the Mark type to shape and select a transparent shape (see this post for details on how to get this set up). Move Sub-Category to Label and align top right.

Finalise the display by hiding the Column and Row fields (uncheck show header), hiding the right hand axis (uncheck show header). Format to remove all gridlines & zero lines and hide the null indicator. Remove the axis title.

You should then be able to just add this to a dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you proportionally brush from multiple selections?

Donna ColesLeave a comment

For this week’s #WOW2023 challenge, Kyle wanted us to build a viz that used selections on the viz rather than a set of filter controls to show how the sales for those selections were distributed.

This concept is referred to as proportional brushing and makes use of set actions to achieve the results. The complexity added here was the multiple selections being made.

6 sheets make up this dashboard – 1 for each bar chart, 1 for the KPI and 1 for the breadcrumb trail.

Building the basic bar charts

Create 4 sheets, one for each of the Region, Segment, Ship Mode and Sub-Category dimensions. The simplest way is to build one sheet, get all the formatting applied etc, then duplicate and replace the dimension on the duplicated sheet with the new one.

When building the first sheet, place the dimension (eg Region) on Rows and Sales on Columns, sorted descending. Adjust the Sales to be formatted to $ with 0dp. Hide the Sales axis, and format to remove all gridlines/axis lines/ zero lines and row/column dividers. Show mark labels and align centrally. Adjust the font label to 8pt. Widen each column if need be. Hide the dimension label from displaying (hide field label for columns). Adjust the tooltip to suit. Name the sheet based on the dimension.

Then duplicate this sheet, and drag the next dimension, eg Segment, and drop it directly on Region. If done properly, everything should seamlessly update. Re-name this sheet accordingly, then repeat the process until you have a sheet for each of the four dimensions.

Applying the proportional brushing

Create a set for each of the relevant dimensions.

Region Set – right click on the Region field in the data pane and select Create > Set. Select all the options to be in the set.

Repeat and do the same for each dimension, so you end up with Segment Set, Ship Mode Set and Sub-Category Set.

We need to determine the combination of all the values selected in each set. So we need

Is Selected Options

[Segment Set] AND [Ship Mode Set] AND [Region Set] AND [Sub-Category Set]

This returns true for all the records in the data which match the combined selections of the individual sets.

On the Region sheet, add Is Selected Options to the Colour shelf. The right click on each set in the data and and select Show Set, so the set of selections are listed on the canvas.

Change the options so only the Segment Consumer and rthe Ship Mode Standard Class are selected, along with all Region and Sub-Category values. Adjust the colours associated to the True and False values that are now presented

If need be, adjust the tooltip so the Is Selected Options is not displaying, then add the Is Selected Options field to the Colour shelf of the Segment, Ship Mode and Sub-Category sheets. Play with the set selections to see how the bars change. Once you’re familiar with the behaviour, reset all the sets so they all contain all the values.

Building the KPI sheet

On a new sheet add Sales to Text. Change the mark type to shape and select a transparent shape (see this blog to get this set up). Adjust the Label to include the text ‘Sales’ and format accordingly. Align middle centre. Add Is Selected Options to the Filter shelf and set to True.

Again, if you adjust the set selections, the value will adjust accordingly.

Building the Dashboard interactivity

Add the sheets onto a dashboard. I used both vertical and horizontal layout containers to get the objects positioned where I wanted. I also used blank objects set to height/width of 1px and with a black background colour to create the horizontal and vertical divider lines. You can see from the item hierarchy in the image below, how I laid out my dashboard (I like to rename my containers to help understanding)

Now add a dashboard change set values action for each of the 4 bar chart sheets.

Select Region

On select of the Region sheet only, target the Region Set. On running the action (ie clicking the bar), assign values to set, and when clearing the selection (clicking the bar again), add all values to the set.

Note – While not specified in the requirements, I noticed that the breadcrumbing functionality in Kyle’s solution didn’t behave if multiple selections of the same dimension were made – eg 2 regions were selected. I decided to add the requirement of only allowing a single dimension to be clicked (ie the single-select only box is checked).

Create a Select Segment, Select Ship Mode and Select Sub-Category set action using the same principals described above.

Creating the breadcrumb

I’ve added this last, so you understand how we can ensure each set only has either all the values in it, or just 1 value.

To create the breadcrumb, we’re going to build up some strings based on what the state of each set looks like. This involved several calculated fields…. I’m not sure if I’ve over complicated this though..

Anyway firstly, we want to capture the values that have been added to each set, so we need

Regions in Set

IF [Region Set] THEN [Region] END

Segments in Set

IF [Segment Set] THEN [Segment] END

Ship Modes in Set

IF [Ship Mode Set] THEN [Ship Mode] END

SubCats in Set

IF [Sub-Category Set] THEN [Sub-Category] END

The image below shows how each of these fields are behaving based on the set selections – if the value is not selected in the set, the Regions in Set field is Null.

Next we have fields to count how many different values exist in each of these fields.

Count Selected Regions

{FIXED: COUNTD([Regions in Set])}

Count Selected Segments

{FIXED: COUNTD([Segments in Set])}

Count Selected Ship Modes

{FIXED: COUNTD([Ship Modes in Set])}

Count Selected SubCats

{FIXED: COUNTD([SubCats in Set])}

Again you can see from the sheet below, this is counting the number of selections, which is ‘fixed’ (ie the same) for every row.

Now, while this is showing 2, as we’ve manually clicked on the set options, in practice when driven from the dashboard, we’re either going to have all values in the set, or just 1. So based on this assumption, we now just want to get the name of the single selection

Selected Region

IF SUM([Count Selected Regions]) = 1 THEN MAX([Regions in Set]) ELSE ” END

If there’s only 1 item in the set, then get it’s value, otherwise return ‘blank’.

Just testing this behaviour, we can see below that with all the Regions selected, the Selected Region field is empty, but with 1 value selected, we show that value.

Create equivalent fields for each dimension

Selected Segment

IF SUM([Count Selected Segments]) = 1 THEN MAX([Segments in Set]) ELSE ” END

Selected Ship Mode

IF SUM([Count Selected Ship Modes]) = 1 THEN MAX([Ship Modes in Set]) ELSE ” END

Selected SubCat

IF SUM([Count Selected SubCats]) = 1 THEN MAX([SubCats in Set]) ELSE ” END

The order of the dimensions displayed in the breadcrumb is fixed, regardless of the order in which you click the options. That is, if you click a Segment then a Region, the breadcrumb will display the <segment> followed by the <region>. But if you click the Region first and then the Segment, the breadcrumb will still display the<segment> followed by the <region>. Based on this, we can create string values for each dimension that differ depending on whether we know there is a selection made against a subsequent dimension (ie should we include the ‘>’ character or not).

Let’s go through in order. Firstly, no selections made

All Segmentations BC

IF [Selected Segment]=” AND [Selected Ship Mode]=” AND [Selected Region]=” AND [Selected SubCat]=” THEN ‘All Segmentations’ END

If all the ‘selected’ values are empty, then all the sets contain all the values, so display ‘All Segmentations’.

If there are selections made, then the dimensions are ordered as Segment > Ship Mode > Region > Sub-Category

Segment BC

IF [Selected Segment]<>” AND ([Selected Ship Mode]<>” OR [Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Segment] + ‘ > ‘
ELSE [Selected Segment]
END

If there is only 1 Segment selected and at least 1 of the other dimensions has been selected too, then add the ‘>’ character after the Segment name, otherwise just show the Segment.

Ship Mode BC

IF [Selected Ship Mode]<>” AND ([Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Ship Mode] + ‘ > ‘
ELSE [Selected Ship Mode]
END

Similar to above, but this time, we only need to compare with the dimensions that are below Ship Mode in the display hierarchy.

Region BC

IF [Selected Region]<>” AND [Selected SubCat]<>” THEN [Selected Region] + ‘ > ‘
ELSE [Selected Region]
END

There is only one dimension below Region. As Sub-Category is at the bottom of the ordering, we don’t need anything special – the value of the Selected SubCat field will do.

On a new sheet, add All Segmentations BC, Segment BC, Ship Mode BC, Region BC and Selected SubCat to the Text shelf. Change the mark type to shape and change to use a transparent shape.

Adjust the label, so all the fields are ordered correctly and positioned exactly next to each otherwith no spacing/carriage returns between. Align the label middle left.

Show the set controls, and then test the functionality by altering the selections, ensuring either only 1 value or all values are selected

Once you’ve finished testing, ensure all values are selected in all sets.

The add this sheet to the dashboard – I had the title and the breadcrumb in a vertical container, which was the left hand side of a horizontal container

And hopefully that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you build a flexible tabular display?

Donna Coles1 Comment

The challenge this week was to provide a balance between the user requesting ‘all the numbers’ and the visualisation designer trying to encourage ‘best practice’. It was inspired by Sam Parsons‘ viz here, which he discusses in this video.

We’re going to use 2 sheets to build this, one to display the text and one to display the bars. But first we need to define some parameters for the user to drive the display

pDisplayType

string parameter containing a list of two options, and defaulted to ‘Bars’

pShowExpanded

boolean parameter, defaulted to False

The measures we’ll be displaying are the Sales in 1000s and the Profit Ratio percentage, so we’ll need

Sales (k)

[Sales]/1000

format to 1 dp

and

Profit Ratio %

(SUM([Profit])/SUM([Sales]))*100

custom format this to ▲ #,##0.0;▼ #,##0.0 (you can use this site to copy & paste the symbols required).

Let’s start to build out the tabular view.

Add Category and Sub-Category to Rows. Add Region and Order Date (as a blue, discrete pill, at the YEAR level) to Columns. Add Measure Names to Filter and filter to Sales (k) and Profit Ratio %. Add Measure Values to Text. Add Measure Names to Columns in front of the Order Date field. Swap the order of the pills in the Measure Values box.

Add subtotals (Analysis > Add all Subtotals) and set Row Totals to Left and Column Totals to Top. Add Column Grand Totals (Analysis > Show Column Grand Totals).

Set the totals, so they are displaying the average values (Analysis > Totals > Total All Using > Average)

Right click on the label ‘Grand Total’ in the table, and format. In the left hand pane, change the label from ‘Grand Total’ to ‘All Categories’

Right click on the ‘Total’ label in the columns and format and change the Totals label to ‘Avg per Yr’

Now we have the content of the table, we need to add the functionality to expand/collapse (hide or show) the Year columns based on the pShowExpanded parameter. For this we need

Show / Hide Columns

IF [pShowExpanded] THEN ‘Show’ ELSE ‘Hide’ END

Add pShowExpanded to the view, and add Show/Hide Columns to Rows.

You should see that the value for Show/Hide Columns is only visible against the Year columns and not against the sub totals. With pShowExpanded set to False the value displayed is ‘Hide’

Now right click on the text ‘Hide’ and select the Hide option from the context menu

The Year columns will disappear. Change pShowExpanded to True, and voila! the columns are displayed. Uncheck Show Header against the Show/Hide Columns so the text ‘Show’ is not visible when expanded.

The ‘hide’ option is a really sneaky trick, but so very effective at not displaying specific rows/columns without filtering them out. It isn’t obvious it’s been applied though (there’s no clues in the canvas), so as a top tip, I recommend always adding a note to the worksheet caption to remind your future self (or a colleague who needs to maintain the workbook), that this has been applied.

Formatting the table

Now the functionality has been nailed, we need to apply the formatting & styling to the table.

I set the fonts to Tableau Medium, colour #7d4e47 throughout. The ‘All Categories’ Grand Total label was 11pt, the Category labels 9pt and the Sub-Category labels 8pt. The Region labels were 11pt, the Measure Names labels were 9pt and the ‘Avg per Yr’ and YEAR(Order Date) labels all 8pt. The Text of the numbers in the main body of the table were all set to 8pt too.

The background of the workbook was set to cream: #fefaf1

and then the total and grand total rows were coloured #faf4e7 and #f5eddc accordingly

To colour the font, add Measure Values to Colour, and right click and select Use Separate Legends

This will then give you two colour legends.

Adjust the Sales (k) colour legend – select any diverging colour palette, set Stepped Colour to 2 steps, and check Include Totals, and then click on the colours at each end and set both to the same brown (#7d4e47).

Repeat similar steps for the Profit Ratio % colour legend, but this time also set the range to be centred at 0, and apply different colours to the ends (#ff6f6a and #7d4e47).

Remove the Category & Sub-Category and Region/Order Date labels from displaying (hide field labels for columns/rows), and adjust the width and height of the cells to give a bit more spacing. Hide the tooltips from showing.

Create the bar table

Duplicate the sheet. Add Measure Values to Columns, and the click on the Label shelf and uncheck Show Mark Labels. Click on the Colour shelf and change the opacity to 35%. Reduce the Size of the bar to suit, then hide the Measure Values axis (uncheck Show Header).

Click on the Tooltip shelf and enable tooltips, then adjust accordingly.

Re-apply any of the formatting to the text that may have been lost – for some reason I lost all the Tableau Medium style fonts…???

Swapping the sheets on the dashboard

Create a dashboard, add a vertical layout container and add both sheets.

We will make use of dynamic zone visibility to only display the sheet we want based on the value of pDisplayType. To do this, we need some more calculated fields

Show Bar

[pDisplayType] = ‘Bars’

Show Numbers

[pDisplayType] = ‘Numbers’

Go back to the dashboard. Add the pDisplayType parameter. Click on the Bar sheet so it is selected (grey box) and on the Layout tab, check the Control visibility using.. checkbox and choose the Show Bar field.

Repeat the same steps for the Text table, but select the Show Numbers field instead. Depending what value is displayed in the pDisplayType parameter, one of the sheets should disappear, and changing the pDisplayType value should swap between them.

Now, style the dashboard as required.

My published viz is here.

Happy vizzin’!

Donna

Let’s Analyse New Product Sales

Donna ColesLeave a comment

For the challenge this week, Sean wanted us to identify the sales of ‘new’ products compared to previous years. This challenge was born out of a requirement he had at work, where he discovered the definition of a ‘new product’ was different to what he’d assumed.

In this instance, the ‘client’ only cared about comparing sales in the same month across the years. Any sales related to the products outside of the specific month were irrelevant and were to be ignored. A product was then counted as ‘new’ in the first year (of the specified month) that there was a sale.

In this case we are always looking at the month of September (the previous month to ‘today), so if Product X was sold in Sept 2021 and Sept 2023 only, then Product X would count as new in Sept 2021, and only the sales for Product X in Sept 2021 would be included in the subsequent values displayed.

Now I thought I’d nailed my understanding of the requirement, and went ahead building out a tabular view of the data using table calculations, but my final output did not match Sean’s result – in fact it was quite way off. I stepped through my logic, even sharing some samples with Sean to sense check, and we seemed to concur on the products identified as new, so I was incredibly puzzled as to what was causing the value discrepancy.

I messaged my friend, Rosario Gauna, to share my numbers and see what she came up with…. she matched Sean! Doh! I then had to spend time dissecting my solution and Rosario’s to see which products I was/wasn’t counting. Eventually I found the problem….

In using table calculations, I had included all the fields the viz required which included the Segment (Home Office, Corporate, Consumer). This meant that in my logic to identify a ‘new product’, I was counting the product as new in the first year that the product was sold in each Segment – ie if Product X sold in Sept 2022 against Segment Home Office and then sold in Sept 2023 against Segment Corporate, I counted both sales as being ‘new’ as the segment differed. However I only wanted the Sept 2022 sale to count as ‘new’.

With this understood, I tried to revise my initial table calc build, but to no avail. So I opened up a new workbook and started again, this time taking a different track. This is what I’ll blog, but I felt it important to explain how even a requirement I thought I’d understood, could still get mis-interpreted.

Building out the calculations

The first requirement is that we’re only looking at comparing the data for the previous month to ‘today’. If this was being developed for a live business application we’d reference the TODAY() function, but since I want this viz to continue to display on Tableau Public after we hit 2024, then I’m going to use a parameter to hardcode ‘today’.

pToday

date parameter defaulted to 4th October 2023

From this, I can then determine the month we need to compare

Month to Compare

DATEPART(‘month’, DATEADD(‘month’, -1,DATETRUNC(‘month’, [pToday])))

working from right to left (inside to out), this takes ‘today’ and finds the 1st of the month (ie 1st October), then goes back 1 month (ie 1st September), then gets the month number ie 9.

As this returns a number, it automatically is listed in the ‘measures’ section of the data pane (under the line), but I dragged it into the top half as I want the number to be discrete.

I then created

Filter Month

DATEPART(‘month’, [Order Date]) = [Month to Compare]

which returns true for all the orders where the month of the Order Date is in September.

Let’s build up a table so we can start to see what we’re working with

Add Product Name and Filter Month to Rows, Order Date (at the Year level) to Columns and Sales to Text.

In the example above, 3-ring staple pack has sales in September 2022 and in other months in 2020 and 2021, but we don’t care about those months, so this product is counted as new in 2022, with a value of $11.

3M Hangers With… have no sales in September in any year, so we won’t be counting that product at all.

6″ Cubicle Wall Clock has sales in both September 2022 and 2023, so this product will be counted as new in 2022 with a value of $83.

So we can filter the date just to Filter Month = True (move Filter Month from Rows to the Filter shelf).

Now we want to identify the earliest year for each Product Name.

Year First Purchased in Month

{FIXED [Product Name],[Filter Month]: MIN(YEAR([Order Date]))}

Add this onto Rows, and you can see below we’re picking up the right year.

Now we can identify the value of the sales we want to count.

New Product Sales

IF [Year First Purchased in Month] = YEAR([Order Date]) THEN [Sales] END

Only get Sales for the year that matches the minimum year. Format this field to $ with 0 dp.

Adding this in to the table, we can see we only have a value against the first year.

So we’ve identified the sales values of the products we care about. We can now aggregate this at a higher level, and incorporate the other dimensions.

Remove Sales from Text, add Category to Rows and Segment to Columns. Remove Product Name and Year First Purchased in Month.

The viz displays the % of total new product sales by Category per Segment. Right click on the New Product Sales pill and add Quick Table Calculation, selecting Percent of Total. Right click on the pill again and select Compute Using -> Category. Right click on the pill and Format and format to % with 0dp. Add another instance of New Product Sales back into the table.

Now we want to be able to compare the sales for the current year/category/segment against that of the previous year.

Previous Year Sales

LOOKUP(SUM([New Product Sales]),-1)

This looks up the New Product Sales value of the previous (-1) field. Add this field into the table, and right click and Edit Table Calculation. Adjust so the calculation is computing by Year of Order Date only.

You should be able to see that the values from the previous year for each Segment & Category are now displayed against each cell. As there is no data for 2019, there are no values listed for this field against the 2020 fields.

Now we have the values we want to compare in the same ‘row’ of data, we can compute

% Diff From Previous Year

(SUM([New Product Sales]) – [Previous Year Sales]) / [Previous Year Sales]

custom format this to ▲0%;▼0% (use this site to get the images to copy & paste), and add this into the table ensuring the table calculation is set to compute using Year of Order Date only, as you did above.

And now we have all the components needed to build the viz.

Building the bar chart

Duplicate the table sheet, then apply the following steps

  • Move % of Total New Product Sales to Rows
  • Move Category to Colour and adjust accordingly
  • Move New Product Sales and % Diff from Previous Year to Label
  • Remove Previous Year Sales
  • Add another instance of % of Total New Product Sales to Label ( I tend to do this by pressing ctrl, and then clicking on the pill in the Rows and dragging onto Text – this creates a duplicate of the pill and preserves the table calculation settings. Otherwise add an instance of New Product Sales to Label, then add the Percent of Total Quick Table Calculation and ensure the pill is set to compute by Category.)
  • Move Segment to be in front of the Year(Order Date) field on Columns – if this changes the viz display, ensure the mark type is set to bar.

Tidy up the display by

  • removing all gridlines/zero lines etc
  • remove row dividers
  • Adjust title of the y-axis
  • Hide the Segment/Order Date column heading
  • Hide the tooltips

The text and formatting of the labels also needs to be adjusted. We don’t want the ‘vs PY’ text displaying for the 2020 years when there is no difference from previous year.

Label PY Text

IF NOT ISNULL([Previous Year Sales]) THEN ‘vs PY’ END

Add this to the Label shelf and ensure the table calculation is set to compute using YEAR of Order Date only. Then adjust the layout of the label text and the format the size of the text.

Now you can just add to a dashboard!

Happy vizzin’!

Donna

Target Achievement Report

Donna ColesLeave a comment

The #WOW2023 challenge this week was set by guest challenger Valerija Kirjackaja, who asked us to recreate a tabular view of data.

Model the data

The requirement involved both the use of the Superstore data set and a custom Target data set, so these need to be combined. In the data pane, relate the two data sets together applying the fields below in the screen shots as the relationship fields (not you’ll need to create a relationship calculation to build the 3rd relationship.

Building the table

The table displays Category & Sub-Category along with a 3rd dimension which will differ depending on user selection. So we need to enable this choice.

pBreakdown

string parameter containing a list of options, defaulted to Ship Mode

Then create a calculated field to determine the actual field to show based on the parameter selection

Breakdown Dimension

CASE [pBreakdown]
WHEN ‘Segment’ THEN [Segment]
WHEN ‘Region’ THEN [Region]
WHEN ‘Ship Mode’ THEN [Ship Mode]
END

The user will also need to select a month. I chose to use a calculated field and parameter to drive this.

Month

DATE(DATETRUNC(‘month’, [Order Date]))

and this then feeds the parameter

pMonth

date parameter, formatted to custom date format of mmmm yyyy (to display September 2023). The list is populated when the workbook opens from the Month calculated field created above. Default date is 01 Sept 2023.

The report will need to be filtered based on the date selected in the parameter, so create

Filter Date

[Month] = [pMonth]

On a new sheet, add Category & Sub-Category (from the Orders data set) and Breakdown Dimension to Rows. Show the parameters. Add Filter Date to the Filter shelf and set to True.

Now, the table shows some conditional formatting, which is a clue that we’re not going to building this table in the basic way. Instead we’ll be using what I refer to as ‘fake axis’ to define placeholder columns, which we can then use to have more flexibility on how the data is displayed.

Double click into the Columns shelf and type MIN(0.0).

Change the mark type to Shape and use a transparent shape (see this blog for more information on doing this). Add Sales to the Label shelf and align left. Add all subtotals (Analysis > Totals > Add All Subtotals) and then set the all to display at the top (Analysis > Totals > Column Totals to Top).

Adjust the formatting, so the row dividers are at the highest level

and the row banding is also at the total pane/header level

Type in another instance of MIN(0.0). This will create a second marks card. Adjust the Sales pill and apply a quick table calculation of percent of total. Set to compute using the Breakdown Dimension field. Format the field to % with 0dp.

To stop the % displaying on the Total rows, click the table calc field and select Total using > Hide

Add another instance of MIN(0.0) and this time add Sales Target value from the Sheet1 data set to the Label shelf.

You can see the target values fill down against the Breakdown Display rows, but we don’t want this.

So to work out whether we’re on a Total row or not, we’re going to make use of the SIZE() table calculation.

We want to count the number of Breakdown Dimension rows being shown.

Count Breakdown Dimension Rows

SIZE()

change this to be discrete, add this to Rows and adjust the table calculation so it is compute using the Breakdown Dimension field. You can see the value against each row represents the number of (in this case) different Ship Mode values.

What isn’t visible is the fact that the SIZE() values against the Total rows are actually 1. Move the Count Breakdown Dimension Rows to the Tooltip shelf on the All marks card. If you now hover over the rows of data, the tooltip should display 1 against this field when it’s a total row and the count otherwise. We’ll use this to identify the Total rows.

This is a common technique that is used. Note though, that if the ‘variable’ you’re counting over only has 1 value, then any logic based on this will apply to that row too (see the Copiers row for Ship Mode & Sept 2023).

So to display the Target Sales as we want, we need

Sales Target to Display

IF [Count Breakdown Dimension Rows] = 1 THEN SUM([Sales Target]) END

ie only show the value of the target on the total rows (or when there’s only 1 value in the dimension being counted).

Replace the Sales Target pill on the 3rd MIN(0.0) marks card with this Sales Target to Display field. Make sure the table calculation is set to compute using Breakdown Dimension

Now we need the variance

Variance

(SUM(Sales)-[Sales Target to Display])/SUM([Sales])

Format this to ▲0.0%;▼0.0%

and we need to identify if it’s +ve or -ve

Variance is +ve

[Variance] >=0

Create another instance of MIN(0.0). Add the Variance field to the Label shelf and verify it is computing by Breakdown Dimension

Add Variance is +ve to the Colour shelf. Adjust the colours, then on the Label shelf, set the font to match mark colour.

To change the ‘Total’ labels to ‘All’, right click on one of the labels, and change the label in the left hand pane.

Finally, adjust the formatting – I set the font throughout to be a darker colour, set the All labels to be bold, removed all gridlines, zero lines and column dividers, hid the MIN(0) axes and also hide field labels for rows. Turn off tooltips for the whole table.

Building the dashboard

I then added the sheet to a dashboard, and placed it within a vertical container. Above the vertical container, I used a horizontal container in which I added text boxes for the column headers. The text box for the variable dimension, referenced the pBreakdown parameter value. I also then used a floating blank set to a height of 2px with a background colour of grey to give the appearance of a line above the column headings.

In the dashboard heading, refer to the pMonth parameter to display the date.

Once published, I did have to tweak the width of the heading text boxes and the position of the floating line within the web edit view.

My published viz is here.

I essentially ‘faked’ the table header row but I still only used 1 sheet to build the table and I was able to dynamically change the title of the dimension column, so hey I hit the brief 🙂 If you want to understand how to build the header row into the actual table itself, then check out Rosario Gauna’s blog.

Happy vizzin’!

Donna

Can you build a drunken tree chart?

Donna ColesLeave a comment

This week, Luke set a challenge he’s had in his back pocket since he first joined as a WOW coach. He did provide several good clues within the requirements to help build the chart.

The challenge involves some data modelling (unioning 2 instances of the Superstore data set together) and what I refer to as ‘normalising’ of dates – to get data spread across multiple years to set to the same year.

I have to admit, I’ve had a busy week so far, attending a conferences and dealing with some personal matters, that I feel I’m going to struggle to get a thorough solution guide documented in a timely manner – it won’t be long before we’re on to week 39….

So for this week, I’m going to direct you to get help from my fellow #WOW participator and Visionary, the most excellent Rosario Gauna, who has already published her solution guide : English | Spanish

Our approaches were very similar – completed in a single sheet, though, as if often the case, Rosario’s solution is far more elegant than mine!

My published viz is here.

Happy vizzin’!

Donna