Can you dynamically zoom into a clustered area?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, Lorna revisited an old #WOW2021 challenge based on Hans Rosling’s chart used in his famous TED talk.

Modelling the data

The first part of the challenge involves modelling the data. Since I’d blogged a solution guide to the original 2021 challenge here, I thought I’d refer myself to my own blog. Re-reading it though, I found I originally had some issues getting all the data sources pivoted in the way I needed, and ended up having to create the csv files as extracted hyper files separately before putting them together. I encountered the same issues again (I was hoping that ‘maybe’ it had been a version problem).

However, I then watched the solution guide that was posted on the old blog page that Lorna had provided, and I found where the problem was.

When I was trying to add connections to the additional data sources into Tableau, I was using the Add option, browsing for the file, and then dragging it into the pane, where I was then unable to pivot it

However, as the csv files I needed are all located in the same directory, the files were already listed on the left hand pane, and dragging from there allowed me to do what I needed, and I’ll talk through that now. Why, adding via the Add button does not work, I don’t know….

The data provided consists of 4 files

  1. Life Expectancy csv
  2. Population csv
  3. Income csv
  4. Geographies (Region Mapping) excel file

The first 3 files are stored as a matrix of Country (rows) by Year (columns) with the appropriate measure in the intersection. This isn’t the ideal format for Tableau so the data needs to pivoted to show 3 columns – Country, Year and the relevant measure (life expectancy, population or income depending on which file is referenced).

In Tableau Desktop, connect to the Life Expectancy file and check the Use Data Interpreter checkbox, so the top row of the file is understood to be the column headings.

Now we need to pivot this data; click on the 1800 column, then scroll across to the end, press shift and click on the final column to select all the year columns. While highlighted, right-click and select Pivot. Your data will be reshaped into 3 columns.

I then renamed each column as

  • Country
  • Year
  • Life Expectancy

Change the datatype of the Year column to be a number (whole), as we’ll need to relate the data on this field later, and working with numeric data is more efficient than strings.

Now from the left hand Files pane, drag in the Population file. By default it will pick up a relationship based on the Country field in each file

Once again, multi-select the columns from 1800 across to 2100, and pivot. Rename the fields to Year – Population and Population and change the datatype of the Year – Population field to a whole number. Add an additional relationship on Year = Year – Population

Next, drag in the Income file from the left hand pane and link to the Life Expectancy file. By default it should pick up a relationship based on the Country field in each file (if not add it). Once again pivot the date fields, and rename the fields Year – Income and Income. Change the data type of the Year – Income field to a whole number, and add an additional relationship on the Year fields.

Finally, using the Add option, add a connection to the Geographies/Region excel file and drag the list-of-countries-etc sheet onto the canvas and link to the Life Expectancy file. Add a relationship from County to Name.

Now the data is modelled, we can build out the viz.

Building the Scatter Plot

We only need to show information for the years up to the ‘current’ year. I created a parameter to represent ‘Today’, essentially hard coding a date.

pToday

date parameter defaulted to 16 Jan 2024

I then created a field

Year <= Current Year

[Year] <= YEAR([pToday])

and added this to the Filter shelf of a new worksheet and set the value to True.

Change the Year field to be discrete (right click > convert to discrete),then add to the Filter shelf, select All Values and then select 2024 from the list. Show the filter on the canvas, and change to a single value (dropdown) that displays only relevant values. Also Customise so the ‘All’ value does not show. Only options from 1800 – 2024 should be listed.

Create a new field to get the regions in the correct format

Region

UPPER(REPLACE([Eight Regions],’_’, ‘ ‘))

Now add Income to Columns and Life Expectancy to Rows and add Country to Detail and Region to Colour and adjust accordingly. Change the mark type to circle. Add Population to Size and adjust. Set the opacity of the colour to around 70%.

If you examine the Income axis on the solution, you’ll see the scale isn’t uniform. This is because it’s using a logarithmic scale instead, which you can set by right-clicking on the Income axis -> Edit Axis and selecting the relevant checkbox. Also, untick the Include zero checkbox, and the display should now start looking more like what’s expected.

Add Year to the Tooltip shelf and update the tooltip. Format the Year field so it’s a number with 0dp that does not include the thousand separators.

Also format the Population field to be a number with 1dp displayed in millions, and format the Life Expectancy field to be a number to 1dp.

Adjust the display of the Life Expectancy axis so that it is displayed without the decimal place. Right click on the axis > format, and on the axis tab on the left hand side, format the numbers to be Number standard

We need to be able to adjust the axis based on selection, so need to set the axis to be able to adjust. For this we will need parameters.

pIncomeMin

integer parameter defaulted to 500

pIncomeMax

integer parameter defaulted to 100,000

pLifeExpectancyMin

integer parameter defaulted to 0

pLifeExpectancyMax

integer parameter defaulted to 100

Right click on the Income axis and edit and set the range to be custom, selecting the pIncomeMin and pIncomeMax parameters

Do the same for the Life Expectancy axis, selecting the relevant parameters.

Hide the null indicator and name the sheet Scatter or similar.

Building the Viz in Tooltip

On a new sheet, add the Year <= Current Year field to Filter and set to True.

Then add Year as a continuous dimension field (green pill) to Columns and Country, Income, Life Expectancy and Population to rows. Add Region to Colour.

Edit each of the Income, Life Expectancy and Population axis in turn and select the Independent axis range for each row or column option.

Hide the Country column (uncheck show header) and remove all gridlines, zero lines, axis lines. Set the display to Entire View and name the sheet VIT or similar.

Back on the Scatter worksheet, edit the Tooltip and add a reference to VIT sheet, adjusting the height and width of the sheet to suit (after a bit of trial and error I used 700 x 450) and setting the filter to Country.

When you hover over a mark, the VIT chart should also be displayed, filtered to the country related to the mark hovered on.

Building the Legend

Create a simple ‘table’ with Region on Columns, Colour and Text. Hide the column heading (uncheck show header) and remove all row & column dividers. Align the text centrally and adjust the font to suit.

Name the sheet Regions or similar.

Adding the interactivity

Arrange the sheets on a dashboard and ensure the Year filter is displayed as a single value drop down that only shows relevant values and doesn’t show the All option.

To filter the chart by the Region, add a filter dasboard action

Filter Region

On select of the Region sheet on the dashboard, target the Scatter sheet on the dashboard, passing the selected fields of Region only. Show all values when the region is unselected.

To allow the chart to zoom in, we need to set the parameters referenced in the axis by using parameter actions.

Income-MinSelected

On selection of marks on the Scatter sheet, update the pIncomeMin parameter using the Minimum value of the Income field. When the selection is cleared, reset the field to 500.

Income-Max Selected

On selection of marks on the Scatter sheet, update the pIncomeMax parameter using the Maximum value of the Income field. When the selection is cleared, reset the field to 100,000.

Create 2 further parameter actions similar to above but referencing the pLifeExpectancyMin and Max parameters and resetting to their defaults of 0 and 100 accordingly.

Once done, the viz should be complete. My published version is here.

Note – I found that after publishing from Desktop to Tableau Public, the ‘zoom’ interactivity was lost, and when I edited my viz on Tableau Public the axis had lost their references to the parameters. I updated and republished the viz from Tableau Public. I don’t know why this happened, and whether it’s a known issue, but thought worth noting in case you encountered the same issue.

Happy vizzin’!

Donna

Can you create a time-focused Gantt chart?

Donna ColesLeave a comment

It was Kyle’s turn to set the challenge this week, and took inspiration from a work based scenario he had encountered. As a result 3 fictitious data sets were provided, and a viz was required to be built against each of them.

Building the Marketing Campaigns Gantt chart

All 3 vizzes are tied together by 2 parameters – the highlight date and number of days, so lets’ start with defining those

pHighlightDate

date parameter defaulted to 01 Dec 2023

and

pDays

integer parameter defaulted to 120

On a new sheet, show both of these parameters.

Using the Campaign data source, add Campaign to Rows and Start Date to Columns as a continuous exact date (green pill).

To define the width of each mark, we need

Duration

DATEDIFF(‘day’, [Start Date], [End Date])

Add this to the Size shelf, then add Category to the Colour shelf and adjust accordingly.

Add Start Date as a discrete exact date (blue pill) to Rows and position in front of the Campaign pill. This will order each row based on the Start Date ascending.

Hide the Start Date pill on Rows (uncheck Show Header). Add End Date as a continuous attribute to Tooltip, and amend the tooltip as required.

Add pHighlightDate to Detail, then right click on the Start Date axis and Add Reference Line. Add a dotted line for the entire table based on the pHighlightDate field.

Remove all row and column dividers; delete the axis title (right click > edit axis) and hide the Campaign column heading (right click > hide field labels for rows). Format the Campaign numbers so they are aligned right.

Update the title of the sheet, and include the details for the legend title within

Finally, we need to filter the rows shown based on whether the campaign was running within the window based on the number of days before/after the highlight date. We need

Window Start

DATE(DATEADD(‘day’, [pDays]*-1, [pHighlightDate]))

Window End

DATE(DATEADD(‘day’, [pDays], [pHighlightDate]))

and then

Campaigns to Include

[End Date] > [Window Start] AND [Start Date] < [Window End]

Add Campaigns to Include to the Filter shelf and set to True. Test the viz changes as the parameters are changed.

Building the Experiments Gantt Chart

This is built in very much the same way as the above using the Experiments data source instead. In this case, Start Time and Experiment ID will be on Rows, Start Time on Columns and Status on Colour,

A Duration field should be on Size, but the calculation needs to be slightly different to handle those records where there is a start but not an end. In this case, we assume the experiment is still ongoing, so set the end to ‘today’.

Duration

DATEDIFF(‘day’, [Start Time], IFNULL([End Time], TODAY()))

A Window Start and Window End field will need to added to this data source too, and then

Experiments to Include

IFNULL([End Time], TODAY()) > [Window Start] AND [Start Time] < [Window End]

should be added to the Filter shelf, and set to True – at this point all the experiments that didn’t have a start date either will disappear. Add a title and the reference line and format as before

Building the Emails bar chart

On a new sheet, using the CRM data source, add Date as a continuous exact date (green pill) to Columns and Sent to Rows. Change the mark type to bar chart. Click on the Size button, and change to be Fixed.

Add Email Type to Colour and adjust. Update the Tooltip.

Create fields Window Start and Window End as before, then create

Emails to Include

[Date] > [Window Start] AND [Date] < [Window End]

and add to the Filter shelf, set to True.

Add pHighlightDate to the Detail shelf, and add a reference line as before.

Remove all row/column dividers, gridlines & axis lines; delete the axis titles; add a sheet title including the legends.

The add all 3 sheets to a dashboard – I placed them all in their own vertical container, so I could then distribute contents evenly.

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

Happy vizzin’!

Donna

Can you compare salaries to the median?

Donna ColesLeave a comment

For the first WOW challenge of 2024, I set the task of completing a viz related to HR salary data.

Creating the calculations

To start, we’ll build out all the necessary calculations required, and display them in tabular form. First create

Employee

[Forename] + ‘ ‘ + [Surname]

then add Employee ID and Employee to Rows, along with Department and Job Title. Add Pay Level as a discrete dimension (blue pill) to Rows too, then format Salary to £ with 0 dp, and add to Text.

We want to compare each employee’s salary with the median salary of all the employees in the same pay level. For this we need

Median Salary per Pay Level

{FIXED [Pay Level]: MEDIAN([Salary])}

Add this to the view too. You should see that the value is the same for those rows where the Pay Level is the same.

We need to compute the difference between these numbers (to draw a line from the median to the salary)

Difference From Median

SUM([Salary])- SUM([Median Salary per Pay Level])

and we also need to understand the salary as a proportion of the median

Salary / Median %

SUM([Salary])/SUM([Median Salary per Pay Level])

format this to % with 0 dp and add both these fields to the table

Each employee is given a scale value based on the proportion

Salary Scale

IF [Salary / Median %] > 1.2 THEN 6
ELSEIF [Salary / Median %] >1.1 AND [Salary / Median %] <=1.2 THEN 5

ELSEIF [Salary / Median %] >1 AND [Salary / Median %] <= 1.1 THEN 4

ELSEIF [Salary / Median %] > 0.9 AND [Salary / Median %] <= 1 THEN 3

ELSEIF [Salary / Median %] > 0.8 AND [Salary / Median %] <=0.9 THEN 2
ELSE 1
END

Set this to be discrete, then add to the view on Rows.

Finally we need the markers for the 80%, 90% etc of the median salary

80% Median

0.8 * [Median Salary per Pay Level]

90% Median

0.9 * [Median Salary per Pay Level]

110% Median

1.1 * [Median Salary per Pay Level]

120% Median

1.2 * [Median Salary per Pay Level]

Building the Viz

On a new sheet, add Employee ID, Employee, Department and Job Title to Rows. Add Salary to Columns and set the mark type to Circle. Set the Colour of the circle to suit (or use #64cdcc).

Add Median Salary per Pay Level, 80% Median, 90% Median, 100% Median, 120% Median to the Detail shelf. Add Pay Level, Salary Scale and Salary / Median % to Tooltip.

Format the axis (right click) and set the scale to be at £k with 0dp

This will also change the formatting of the salary on the tooltip, but we want it to be more detailed so create

Tooltip-Salary

[Salary]

and format this to £ with 0 dp. Add this to Tooltip too and adjust tooltip to match.

Add a reference line to the axis (right click axis > add reference line) for the Median Salary per Pay Level. Set it to be per cell and display a dashed line with no labels/tooltips displaying.

Add another reference line. This time set it to be a band at the cell level which ranges from the 80% Median to the 90% Median. Don’t display any labels or tooltips or lines and fill the band with the appropriate colour.

Repeat the above process, adding a reference band from 90% – 110% of the median, and a further band from 110% – 120% of the median.

Add another instance of Salary to Columns. Change the mark type of the 2nd Salary marks card to gantt and adjust the colour. Add Difference From Median to Size and decrease the size as small as possible. Double click into the Difference From Median pill on the size shelf and manually type in * -1 to the end.

Now make the chart dual axis and synchronise the axis. Right click on the top axis and move marks to back.

We want the user to be able to filter the chart, so add Department, Pay Level (select All values) and Salary Scale to the Filter shelf.

Finally, format the chart – make each row a bit wider; set the background colour of the worksheet to #f9f8f7; remove column dividers; set row dividers to a thick white line; remove all gridlines, zero lines and axis rulers. Reduce the size of the axis values and increase the width of the initial 3 columns so the text doesn’t wrap as much. Test your filters.

If all working, then add the sheet to a dashboard.

Building the Legend

To build the legend, I simply duplicated the core sheet, then filtered to a specific Employee ID and hid the Employee ID, Employee, Department and Job Title fields. I then edited the reference lines to add custom labels to label each line band, formatting the text to display to the top or bottom. I added an annotation to the Salary circle mark to label that as ‘Employee Salary’ which I then manually moved into position.

When I added this to the dashboard, I then floated a blank object over the top so the legend could not be interacted with.

My published viz is here.

Happy vizzin’!

Donna

Elf Economics

Donna ColesLeave a comment

For the penultimate challenge of 2023, Erica set this fun Christmas themed challenge to visualise the toy production in Santa’s workshop. It was a collaboration with the #PreppinData crew, where you were encouraged to complete their challenge to prep the data for this one. I did do that, but to ensure no discrepancies or field name differences, I used the outputs from the challenge itself as the source for my viz.

Building the Line Chart

This needs to show the quota vs the cumulative number of toys produced for each production manager/toy and uses the data from the Output 1 of the Prep challenge.

Add Week to Columns and change to exact date. Format the Week pill on the Columns to show as custom format yyyy on the axis

then edit the axis and set the tick marks to be fixed from 01 Jan 2023 with an interval of 1 year. This will result in just 2 axis labels displayed, one for 2023 and one for 2024

Add Production Manager and Toy to Rows and then add Quota to Rows too. Then drag Toys Produced onto the Quota axis and drop it when the double green column icon appears.

This will convert the viz to have Measure Values on the Rows instead, and the Quota and Toys Produced pills sitting in the Measure Values section on the left.

Add a Running Total quick table calculation against the Toys Produced pill. Then edit the Value axis, so that the axis are independent axis ranges for each row & column.

The colour of the running total line needs to change based on whether the overall value is above or below the quota. Erica asked us not to use LODs in this challenge, so to determine this, we need

Colour – Over | Under

IF WINDOW_MAX(RUNNING_SUM(SUM([Toys Produced]))) > WINDOW_MAX(SUM([Quota])) THEN ‘Over’ ELSE ‘Under’ END

The WINDOW_MAX function is taking the highest value of the measure and essentially ‘spreads’ that across every row of data being plotted (in this case every week).

Add this field to the Detail shelf and then click on the 3 dot symbol to the left of the pill and change it to the Colour symbol. This allows multiple pills to be on the Colour shelf – Measure Names and Colour – Over | Under, resulting in 4 different colours in the colour legend.

Adjust the legend colours, so the two relating to the Quota are the same colour and the others coloured based on whether the value is Over or Under.

On the Label shelf, check the show mark labels option, and then select most recent. Adjust the font to be bold and match mark colour. Format both the pills sitting in the Measure Values section to be Millions with 1 dp.

Add Week to the Tooltip shelf and format to be in the <day of week>, <day> <month> <year> style. Adjust the tooltip accordingly.

Hide the Production Manager and Toy fields (uncheck show header). Edit the title of the Value axis and the Week axis. Remove all gridlines, zero lines, row & column dividers, but ensure the axis are displayed. Change the worksheet background colour.

Update the Title of the sheet to reference the Toy, then name the sheet Line or similar.

Building the KPIs

We’re still using the data from Output 1. We’re going to do this in 2 sheets, as we want to format the text of the PM name differently. To start, we need some additional calculated fields.

Rate of Production

AVG([Toys Produced])

Then we need to work out for those Production Managers who were under their quota, how far off they were and how long, based on their production rate, it would take for them to fulfil that difference. So first we need

Difference

AVG([Quota]) – SUM([Toys Produced])

This gives us how far under (or over) the PM was from their target quota.

We can then calculate

Weeks Needed to Meet Quota

IF MIN([Over or Under Quota?]) = ‘Over’ THEN 0 ELSE
CEILING([Difference] /[Rate of Production])
END

If the PM has exceeded their quota, then 0, as there’s nothing to build, otherwise determine the number of whole weeks. The CEILING function ensures even if the result is only a fraction over a number, the result is ’rounded up’ the next whole number so 12.1 weeks and 12.9 weeks are both reported as 13 weeks.

Add Production Manager and the 2 fields above onto a new sheet and display in tabular form.

Set the sheet to Entire View and adjust the text to be larger (I used bold 18pt font). Format the column headings to be larger too (I used 12pt). Stop the tooltips from displaying, remove row/column dividers and row banding. Set the background colour of the worksheet and hide the Production Manager column (uncheck show header).

Name this sheet KPI or similar.

On a new sheet, add Production Manager to Rows and add Production Manager to the Text shelf too. Double click in to the Columns shelf and type ‘Production Manager’ to create a heading for the text column.

Set the sheet to Entire View, then adjust the font of the Text shelf. I chose a handwriting script font and set to 18pt and bold. The hide the Production Manager field on Rows, and hide the ‘Production Manager’ column label heading (right click – hide field labels for columns). Adjust the font of the column heading and remove all row/column dividers and row banding, Set the background colour. Hide the tooltip.

Name the sheet PM Name or similar.

Building the bar chart

For this, we’re now using the data from Output 2.

We’re plotting 2 measures for the bars – the amount under or over the quota which is a +ve (over) or -ve (under) number which will be plotted either side of a zero line as you would expect. The Toys Over/Under Quota field has this value.

We also need to plot the amount of toys produced, but while this is a positive number, it is displayed on the bar chart on the negative side of the zero line. So to enable this we need

Toys Produced to Plot

-1 * [Toys Produced]

ON a new sheet, add List and Toy to Rows. Then add Toys Produced to Plot to Columns, and then drag Toys Over/Under Quota onto the axis and drop when the 2 green column icon appears. This will result in the following display where Measure Names and Measure Values are automatically added.

Move Measure Names from Rows onto Colour, then change the order of the pills listed in the Measure Values section, so Toys Produced to Plot is listed first.

Create a new field

Colour – Over | Under

IF [Toys Over/Under Quota] < 0 THEN ‘under’ ELSE ‘over’ END

and add to the Detail shelf, then adjust the symbol to add this field to the Colour shelf as well to give you 4 colours on the legend. Adjust accordingly. Add Quota, Toys Produced and Toys Over/Under Quota to Tooltip and adjust.

For the label to display against each bar, we need to plot another measure, which is either 0 for those which were under production, or the value of the over production.

Label Value to Plot

IF SUM([Toys Over/Under Quota]) < 0 THEN 0 ELSE SUM([Toys Over/Under Quota]) END

Add this to Columns. On the Label Value to Plot marks card, change the mark type to circle and remove Measure Names from colour.

Create a new field

% Difference

SUM([Toys Over/Under Quota]) / SUM([Quota])

and apply a custom number format of 0%;0% which means -ve numbers will display as +ve.

Add this to the Label shelf along with Colour – Over | Under. Adjust the label text so the labels are displayed on a single line, are aligned middle right and the font matches mark colour and is bold. Make the chart dual axis and synchronise the axis (set the mark type of Measure Values to a bar if the display changes). On the Label Value to Plot marks card, reduce the opacity of the circle colour to 0% and reduce the size to the smallest possible. Remove all the text from the Tooltip.

To ensure the label text doesn’t overlap the bars, we can extend the axis by creating

Ref Line

WINDOW_MAX([Label Value to Plot]) *2

Add this to the Detail shelf of the Label Value to Plot marks card. Then right click on the top axis and Add Reference Line that refers to the maximum of the Ref Line field. Apply settings as below so the line is invisible.

Finally hide both axis, remove all gridlines, zero lines, axis and column dividers. Adjust the row dividers to be thick grey dashed lines. Update the title of the sheet.

Name the sheet Bar or similar.

Adding the interactivity

Using layout containers, add the sheets to a dashboard so they are arranged in the format required. Then add a dashboard filter action

Select PM

on select of the Bar sheet, target the KPI, Line and PM Name sheets. When the selection is cleared, keep filtered values Only allow 1 selection to be made at a time.

Click the bar against Barbie Doll to set the other charts to filter just to that toy, then unclick the bar again. The remaining charts should stay filtered.

And that should be it. Obviously you can add imagery as you wish but I didn’t go down that route – I just chose to set coloured borders on the layout containers.

My published viz is here.

Happy vizzin’! and enjoy the festive season.

Donna

Can you build this simple KPI card?

Donna ColesLeave a comment

For this week’s challenge, Luke asked us to recreate this KPI card on a single sheet.

We needed to display data for the last 2 years up to the latest complete month. If this was being built for a business situation, we’d make use of the TODAY() function to get a handle on the current date. Since this is being built with a static dataset which includes data up until 31st Dec 2023, I am using a parameter to ‘hardcode’ the ‘today’ date, as I want this viz to still present the relevant data on my public profile if it’s accessed in a year’s+ time.

pToday

date parameter defaulted to 12th Dec 2023

With this I can the define the data I want to include within the viz

Records to Include

[Order Date] < DATETRUNC(‘month’, [pToday]) AND [Order Date] >= DATEADD(‘year’,-2,DATETRUNC(‘month’, [pToday]))

based on pToday = 12th Dec 2023, this includes records where the Order Date is less than 01 Dec 2023 and greater or equal to 01 Dec 2021.

Add this to the Filter shelf and set to True. Then add Order Date set to the Continuous Month level (green pill) to Columns and Sales to Rows.

The add another instance of Sales to Rows and change the Mark type on the ‘Sales 2’ marks card to Area. Make the chart dual axis and synchronise the axis. Adjust the opacity of the area chart via the Colour shelf as required, and amend the Tooltip on the All marks card to display the month and sales value in the relevant format.

This has formed the basis of the sparkline. Now we need to determine the calculations we need which are displayed in the text.

The text displays information related to the month the user ‘selects’ by hovering over the sparkline. By default the information for the latest full month (in this case Nov 2023) is displayed. We need to capture this latest month in a field

Latest Month

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

We also need to capture the month the user selects via the hover into a parameter.

pSelectedMonth

date parameter defaulted to 01 Jan 1900 (think of this as a ‘null’ date)

With this we can then create

SelectedMonth

IF [pSelectedMonth] = #1900-01-01# THEN [Latest Month]
ELSE [pSelectedMonth]
END

format this to the <month year> format ie November 2023

and then also create

Selected Month Sales

{SUM(IF DATETRUNC(‘month’, [Order Date]) = [Selected Month] THEN [Sales] END)}

notice the function is wrapped in { } which makes it a Level of Detail (LOD) calculation, so the value is fixed across every row in the data set.

We also need to work out the sales from the same month in the previous year

Select Month Sales -PY

{SUM(IF DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’, -1, [Selected Month]) THEN [Sales] END)}

and subsequencty we can work out the change in sales

Change from PY

(SUM([Selected Month Sales]) – SUM([Selected Month Sales – PY ]))/SUM([Selected Month Sales – PY ])

To sense check what we’ve got, on a new sheet display the pSelectedMonth parameter then build a sheet as below

with the parameter set to 01 Nov 2023 we can see the values for Nov 2023 and Nov 2022 and captured in the relevant fields, and then the % change between the two also reflected.

But the % change is displayed on the KPI in different coloured text depending on whether the field is +ve or -ve. FOr this we need

Change from PY +ve

IF [Change from PY] >=0 THEN [Change from PY] END

and

Change from PY -ve

IF [Change from PY] <0 THEN [Change from PY] END

apply a custom number format to both fields of ↑0%;↓0% and add both fields to the sheet. Only 1 of these columns will ever be populated. If you change the parameter to 01 Aug 2022, you’ll see a negative change.

Now we have these fields, we can start to add the text element to the sparkline chart.

We’re going to plot a ‘mark’ against the first point in the x-axis, in this instance the point associated to the 1st Dec 2021. But we don’t want to ‘hardcode’ this date, so we can use

Dummy Y-Axis

IF FIRST() = 0 THEN 1 END

where FIRST() is a table calculation that is 0 for the first point on the month axis.

Add this to Rows before the Sales pills.

We have a single mark plotted for 1st Dec 2021 on a second Y-axis at position 1 on the axis. But no other marks. Change this mark type to shape and use a transparent shape (see this blog for details on how to do this).

Add Selected Month as an exact date to the Label shelf, along with Selected Month Sales, Change from PY +ve and Change from PY -ve. We also need

Previous Year

YEAR(DATEADD(‘year’,-1,[Selected Month]))

convert this to a dimension (drag to be above the line in the left hand data pane) and then add to Label too. Adjust the layout of the label as below and align top left. Note I added some spaces to the front on each line of text.

You should have something that looks similar to

To get the vertical line to display on hover, we need to create

Ref Line

IF [pSelectedMonth]<>#1900-01-01# THEN [pSelectedMonth] END

Add this to the Detail shelf of the Area chart sales marks card and set to be exact date (green pill). The right click on the date axis and Add Reference Line

Changing the pSelectedMonth parameter the line will display

Finally clean up the chart by hiding all the axis, removing all row & column dividers, gridlines, axis lines and zero lines. Hide the ‘null’ indicator.

Add the sheet to a dashboard, then create a parameter action

Select Month

on hover of the KPI card, update the pSelectedMonth parameter with the value from the Month(Order Date) field. When the selection is cleared, set the value to 01 Jan 1900.

Note – you may find that based on the size of the dashboard, you don’t get the text part to display. This is an annoyance in Desktop, that it isn’t completely WYSIWIG (what you see is what you get). I spent time adjusting font sizes etc to make the text display in Desktop, but once published to Tableau Public, it all looked too small. After setting it all back to the sizes I wanted in Desktop and re-publishing, I found it did actually display ok on Public. So you may find you just need to play around a bit to get the display as you want.

My published viz is here.

Happy vizzin’!

Donna

Can you find how many quarters it took for a customer to order again?

Donna Coles4 Comments

It was Lorna’s turn to set the challenge this week, and she opted for a gentle workout to soothe us into the Christmas holidays – I certainly appreciated it as I am super busy and super stressed trying to finalise Christmas presents and preparations!

I built this with 4 calculations in total. Let’s start by just building out a tabular view of the data, so we know what we’re looking for.

Add Customer ID and Order Date set to be a discrete (blue) exact date onto Rows.

To start with, we want to capture, against each customer, the earliest order date

First Purchase Date

{FIXED [Customer ID]: MIN([Order Date])}

Add this into Rows as a discrete exact date.

You can see that for every row associated to the same customer, the First Purchase Date matches the first row.

Now I want to identify the row that represents the first order for each customer

Order is first order

[Order Date] = [First Purchase Date]

Add this onto Rows and we can see the first row against each customer is True, while the rest are False.

I can now use this to identify the date of the 2nd order for each customer – it is the earliest date where the order isn’t the first order

Second Purchase Date

{FIXED [Customer ID]: MIN(IF NOT [Order is first order] THEN [Order Date] END)}

Add this onto Rows as a discrete exact date, and you can see that every row associated to the customer, now has the date that matches the 2nd Order Date listed.

Remove Order Date and Order is first order from the Rows and we have 1 row per customer with the 2 dates we’re interested in.

From this we can work out

Quarters Since First Purchase

DATEDIFF(‘quarter’, [First Purchase Date], [Second Purchase Date])

Add this onto Text, so you can validate the result.

Now we can build out the matrix.

On a new sheet add First Purchase Date to Rows and set to be at the discrete (blue) quarter level. Then add Quarters Since First Purchase to Columns and change to be a discrete Dimension.

This already gives us the shape we’re after. Right click on the Null value in the column heading and select Edit Alias and update the word to Lapsed.

Add Customer ID to the Colour shelf, and the click on the pill and change to be a Measure of Count Distinct. This will change how the viz is displayed. Adjust the colours accordingly.

Finally update the Tooltip to match the required text, and adjust the formatting so there are pale grey thin dotted row and column dividers on the pane only, but not the header.

Add the viz to a dashboard, and you’re done! My published viz is here.

Happy vizzin’!

Donna

Can you add candlesticks to bar charts?

Donna ColesLeave a comment

For the final week of global recognition month, Shunta Nakjima set this challenge inspired by one of the ‘founders’ of #WorkoutWednesday, Andy Kriebel.

Let’s get stuck in, by starting with the selector sheets.

Building the Measure Selector

The measure selector will be used to set a parameter which will store the particular measure selected, so we need

pSelectedMeasure

string parameter defaulted to the value Sales

We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.

Measure Selector Alias

CASE [Segment]
WHEN ‘Consumer’ THEN ‘Sales’
WHEN ‘Corporate’ THEN ‘Profit’
ELSE ‘Quantity’
END

Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.

Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.

We need to identify which measure has been selected, both through colour and an arrow indicator. So we need

Is Measure Selected

[Measure Selector Alias] = [pSelectedMeasure]

Add to the Colour shelf and adjust to suit.

Then create

Measure Selected Arrow

IF [Is Measure Selected] THEN ‘►’ END

I use this site to get the characters I need.

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.

Name the sheet Measure Selector.

Building the Year Selector

In order to not ‘hardcode’ the latest year, we need

Current Year

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

format this to be a number with 0dp and not to show the thousands separator.

From this we can create

Comparison Year

IIF(YEAR([Order Date])<>[Current Year],YEAR([Order Date]),NULL)

On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).

As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.

We’re going to need a parameter which will capture the year selected

pSelectedYear

integer parameter defaulted to 2022 with the display format set to not include the thousand separator

We need to identify which year has been selected, both through colour and an arrow indicator. So we need

Is Selected Year

[Comparison Year] = [pSelectedYear]

Add to the Colour shelf and adjust to suit.

Then create

Year Selected Arrow

IF [Is Selected Year] THEN ‘►’ END

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.

Building the Current Year ‘card’

Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.

Building the bar chart

Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need

Measure to Display – Curr Year

IF YEAR([Order Date]) = [Current Year] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

and

Measure to Display – Comp Year

IF YEAR([Order Date]) = [pSelectedYear] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.

Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters

So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.

Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.

Difference

SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])

custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.

% Difference

IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0)
OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN
[Difference]/ABS(SUM([Measure to Display – Comp Year]))
ELSE 0
END

If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to ▲0.0%;▼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.

Diff is +ve

[Difference]>0

Add this to the Colour shelf (remove Measure Names) and adjust accordingly.

Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.

Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.

Adding the interactivity

Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.

To select the year and the measure, I needed parameter actions

Select Year

on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the Comparison Year field

and

Select Measure

on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field

Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action

Deselect Years

On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.

Create another similar filter action for the Measure Selector sheet, and that should then be it!

My published workbook is here.

Happy vizzin!

Donna

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