Generation Population

For week 17 of #WOW2021, Sean Miller decided to challenge us with recreating a chart by Nathan Yau (see here for the original). The aim was to recreate within 1 sheet, which I managed to do. So how did I do it? Read on 🙂

  • The groundwork
  • Colouring the bars
  • Adding the year labels
  • Final formatting

The groundwork

The chart itself follows a straight forward structure of multiple blue dimension fields on Columns with a green measure on Rows similar to this view below based on Superstore Sales data – it’s just formatted a bit more creatively!

The data provided just contains 3 fields : SEX, AGE and 2019 Population. We want to present the 2019 Population measure for the Total SEX only by Year. We need to create the Year field, which is simply

Year

2019-[AGE]

I dragged this into the ‘dimensions’ section of the data pane (above the line).

This allows us to create the basic bar chart required

We now need to define the various fields that we will need to add as additional dimensions on the Columns shelf to create the ‘generation’ data panes.

Generation

IF [Year]<= 1927 THEN ‘Greatest
Generation’
ELSEIF [Year]<= 1945 THEN ‘Silent Generation’
ELSEIF [Year]<=1964 THEN ‘Baby Boomer’
ELSEIF [Year] <= 1980 THEN ‘Generation X’
ELSEIF [Year]<=1996 THEN ‘Millennials’
ELSEIF [Year]<=2012 THEN ‘Generation Z’
ELSE ‘Gen
Alpha’
END

NOTE – there is a deliberate carriage return in the condition for ‘Greatest Generation’ and ‘Gen Alpha’ which will force the field to ‘wrap’ when displayed.

Having defined the above, we need to determine

Total Population Per Generation

{FIXED [Generation], [SEX]: SUM([2019 Population])}

and then

% of Total Population

SUM([Total Population Per Generation]) / TOTAL(SUM([Total Population Per Generation]))

NOTE – to create this field, I originally created a ‘quick table calculation’ against the Total Population Per Generation field which I’d displayed on a view, and then dragged the resulting pill into the measures pane to create the new field with the desired calc.

Let’s put these in a table, so we can then check the values, and see that the 2nd and 3rd columns are the same value for each row associated to a particular generation, which is what we need.

Right, so now we need to determine the rank based on the Total Population Per Generation

Rank

RANK_DENSE(SUM([Total Population Per Generation]))

Format this to a custom number with 0 decimal places, but prefixed with #

When added to the table we get

The intention, is that Rank will be displayed as discrete ‘header’ pill rather than a measure, so let’s move Rank to be the 1st pill on the Rows shelf and change to be discrete.

But we need the Total Population Per Generation and % of Total Population fields to be combined into a single pill. So we need to do a bit of string manipulation/ number formatting for this

Total | Percent

STR(ROUND(SUM([Total Population Per Generation])/1000000,1)) + ‘M’ + ‘ | ‘ + STR(ROUND([% of Total Population] * 100,1)) +’%’

This looks complicated, but its because even though you may have applied the relevant display number formatting against the individual numeric measures of Total Population Per Generation and % of Total Population, the formatting is not preserved, when converted into a string field, which this field needs to be. So the relevant calculations need to be applied within the field itself.

This outputs the below

Now we need a way to sort the data so the ‘Greatest Generation’ associated to the earliest years is listed first. I did this by determining the minimum date within each Generation.

Min Year Per Generation

{FIXED [Generation], [SEX]: MIN([Year])}

Add this into the view as the first pill in Rows, and the data should automatically sort from lowest to highest

We can now build the viz – duplicate the table sheet, remove Total Population Per Generation and % of Total Population from the Measure Values section. Drag 2019 Population to Columns, then click the swap rows & columns button :

Colouring the bars

The bars are coloured based on each ‘Generation’ pane. You could hardcode this along the lines of ‘Generation = x OR Generation = y or Generation = z etc’ where x, y and z etc are generations of the same colour. This would return true or false, which you can then add to the colour shelf and adjust accordingly.

I decided to be a bit more dynamic, deciding I wanted to set the colour based on whether it was an odd or even pane.

For this I created another ‘rank’ field based on the field I’d used to ‘sort’ the data, the Min Year Per Generation field.

Sort Position

RANK_DENSE(MIN([Min Year per Generation]),’asc’)

If you add this into the data table, you’ll see each section is numbered 1 -7

From this, we can then determine if the number is even (or not)

Sort Position is even number

[Sort Position]%2=0

Add this onto the Colour shelf which will return True or False and colour accordingly.

Adding the year labels

The year labels are achieved by using a dual axis chart, to plot a point for each specific year (based on the Min Year Per Generation field) at some arbitrary value.

Point to Plot Year Label

IF [Year]=[Min Year per Generation] AND [Year]<>1919 THEN 4700000 END

For each ‘min year’ that isn’t 1919, plot a value at 4.7M.

Add this field to the Rows shelf, change mark type to circle, reduce size to as small as it can, and set the colour transparency to 0.

Add Min Year Per Generation to the Label shelf, then change the alignment to vertical.

Now you make the chart dual axis and synchronise the axis. Some of the colours/marks may change, so reset by removing Measure Names from the Colour shelf and changing the mark types bar to bar & circle.

Final formatting

So at this point all the main components are there. It’s now a case of formatting – removing right and bottom axes, removing gridlines. The vertical dashed lines, are column dividers, set at the pane level only.

The solid left hand axis is set via

The text is formatted using the fonts advised in the requirements and sizes adjusted to suit.

Add on a tooltip, and set the background colour of the worksheet and you should be done.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you use Quick LoDs to recreate this view?

It was Lorna’s turn to set the challenge this week, and she took the opportunity to ask us to use a new feature in Tableau v2021.1 – Quick LoDs (you’ll obviously need v2021.1 to use the functionality, but the LoDs can be created manually in earlier versions if need be).

This blog will focus on

  • Creating the Sub-Category Average Sales by Category LoD using Quick LoDs
  • Formatting the difference calculation
  • Colouring the bars
  • Text for Tooltip
  • Putting it all together

The viz itself isn’t that complex once you’ve nailed the LoD, so lets’ start with that bit.

Creating the Sub-Category Average Sales by Category LoD using Quick LoDs

The what…? It’s a bit of a mouthful,.. the “Sub-Category Average Sales by Category”. What we’re essentially after here is the Total Sales per Category / No of Sub-Categories in the Category. To do this with LoDs, we first need to create an LoD to represent the total sales in each Sub-Category.

As mentioned in the requirements / referenced in the KB article above, you use a combination of ctrl/command click & drag to create an LoD via the Quick LoD feature. In this case I dragged the Sales measure onto the Sub-Category dimension, and this automatically created

Sales (Sub-Category)

{ FIXED [Sub-Category]: SUM([Sales]) }

which is the sales per sub-category.

From this, I then dragged this measure onto the Category dimension, which automatically then created

Sales (Sub-Category) (Category)

{ FIXED [Category]: SUM([Sales (Sub-Category)]) }

BUT I then edited this to change the aggregation to AVG, so the field became

{ FIXED [Category]: AVG([Sales (Sub-Category)]) }

This gives the average value required, which you can see is the same across the rows in a single Category :

Formatting the difference calculation

The viz displays the % difference between the Sub-Category sales and the average. This is calculated with

Difference

(SUM([Sales])- SUM([Sales (Sub-Category) (Category)])) / SUM([Sales (Sub-Category) (Category)])

This is then custom formatted as ▲0%;▼0% (I use this site to get my shapes from).

Colouring the bars

The bars need to be coloured based on the value of the Difference field, so another calculated field is required

Colour

[Difference]>0

This will just return true or false and can dropped on the Colour shelf of the bars.

Text for Tooltip

Within the tooltip, the % difference is displayed, along with some text which indicates if it is above or below the average. For this we need another calculated field to reference in the Tooltip.

Above | Below

IF [Difference]>0 THEN ‘Above’ ELSE ‘Below’ END

Putting it all together

With all the calculated fields built, the the chart itself is a relatively simple dual axis chart

  • Add Category then Sub-Category to Rows
  • Add Sales to Columns and sort descending
  • Add Sales (Sub-Category) (Category) to Columns. Make dual axis and synchronise axis
  • Click on the All marks card and remove Measure Names from the Colour shelf.
  • Click on the Sales marks card and change mark type to bar; add the Colour field to the Colour shelf and adjust accordingly; add Difference to the Label shelf and format appropriately.
  • Click on the Sales (Sub-Category) (Category) marks card and change mark type to Gantt; adjust the Size to be as large as possible; set the colour to the relevant grey.
  • On the All marks card, add the Above | Below field to the Tooltip shelf, then edit the Tooltip on the All marks card to create the required text.
  • Finally, remove axis, remove the column heading labels, remove grid lines and column borders and format the displayed text appropriately. Title the viz.

A very brief post today, but hopefully I’ve ticked off all the required elements, My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you use Dashboard Extensions?

For this week’s #WOW2021 challenge, Lorna tasked us with using dashboard extensions, some of which can also be used on Tableau Public (see here). I haven’t had an opportunity to use extensions before, so this was going to be a brand new learning experience for me.

Modelling the Data

In a break from the ‘norm’ Lorna provided us with a new set of data to use, which had to be retrieved from the sources Lorna provided in the challenge

  • Emoji Sentiment – a csv file containing a summary of how often a particular emoji was used within tweets in 2015 and whether the tweets were classified as being positive, neutral, or negative in sentiment.
  • Emoji Database – a link to a site where after registering, you can download a csv file which defines and classifies each emoji

The first part of the challenge requires these data sources to be modelled using relationships in Tableau Desktop. Lorna hinted that a join calculation would be required on the codepoint fields.

I found this a bit odd, as both data sources contained an Emoji field which was automatically set to be the relationship field, and seemed to work.

For the purpose of this blog though, I’m going to attempt to rebuild my solution as I type, and use the suggested fields. The Unicode codepoint field in the Emoji Sentiment data needs to map to the codepoint field in the Emoji Database, but they’re not an exact match. We can prefix ‘0x’ to the codepoint field and ensure the case of the letters match. We can use a relationship calculation for this.

Grouping the Groups

This chart shows the top 20 emojis based on Occurrences and should filter to the appropriate group when a ‘category’ is clicked on the dashboard extension image on the left hand side.

The Emjoi Database data has a Group field, but this doesn’t exactly match the groupings on the image – some groups are ‘grouped’ together. I used Tableau’s in built grouping functionality to group the entries as required (right click field -> create -> group).

Category

I grouped People & Body and Smileys & Emotion together and named them Smileys and People

Filtering by Top 20

The list of Emojis needs to show the Top 20 based on Occurrences, but also filtered by Category. Build out a basic table of

  • Emoji Sentiment : Emoji1, Unicode name on Rows
  • Occurrences on Text
  • Emoji1 on Filter, set to filter by Top 20 of Occurrences
  • Category as a Context Filter – select all values to filter by, then right click -> add to context. This means the data will be filtered based on the Category first before the Top 20 filter is applied. The pill will change to grey to show it is a context filter. Show the filter and you can test it’s working

Building out the Top 20 Chart

Now we have the basics of the filtering functionality, we can build out the rest of the chart.

We need to display percentages, so need fields

% Positive

SUM([Positive])/SUM([Occurrences])

formatted to 1 decimal place. Create similar fields for % Negative and % Neutral

First start by creating a duplicate of the Occurrences field and call it #. Then add this as a discrete pill to the Rows shelf. Remove Occurrences from the Text shelf. Then add Measure Values to Columns and filter by Measure Names so only % Positive, % Neutral and % Negative measures are displayed. Add Measure Names to Colour.

All this has been done on one axis, so now we can add another to display the Position – add this field to Columns. This will create a 2nd marks card. Remove the Measure Names fields from the card, and change the mark type to circle.

Add an additional field MIN(1) to Columns by ‘typing in’ (double click on the Columns area) . Then click on the pill and select dual axis which will combine this field with the Position measure. Synchronise axis. This unfortunately will probably set all the marks to be circle type

so we need to reset…

  • change the mark type of the Measure Values card to bar
  • remove Measure Names from the Position card
  • remove Measure Names from the MIIN(1) card
  • change mark type of MIN(1) card to bar, and reduce size to as small as possible

This should give you the core chart, which can now be formatted accordingly.

Rounded Bar Chart

I always forget how to build these for some reason, so a quick google gave me a refresher via Andy Kriebel’s tutorial on YouTube

  • Add Category to Rows
  • Occurrences to Columns
  • Type in MIN(0) on Columns
  • Drag the MIN(0) pill and drop it on the Occurrences axis. This will change the view so Measure Values is now on Columns and Measure Names is on Rows
  • Change the mark type to Line and drag the Measure Names pill from the Rows onto the Path shelf
  • Increase the size of the mark

Exclude the Null Category and manually sort the categories to match the order in which the entries are listed on the left hand image.

Set to show mark labels, only displaying them at the end of the line, and aligning middle right

Add Category to the Colour shelf and colour accordingly, then apply the relevant formatting again to remove gridlines, axis and hide the Category pill from displaying.

Adding & Configuring the Dashboard Extension

This challenge makes use of the Image Map Filter extension, which you need to download from this link and save the .trex file somewhere appropriate on you machine. I also chose to make use of the image Lorna used rather than create anything, so saved the image from the challenge page to my laptop.

Create a dashboard and add the 2 charts you’ve already built, then add the Extension object to the dashboard. When prompted select My Extensions and browse to the Image Map Filter .trex file you’ve saved.

You’ll then be prompted to configure the extension as below, selecting the image you’ve saved, which is set to scale to container; choosing the Category dimension which is what the chart is filtered by, and then selecting the Top 20 sheet

You then need to select the rectangle option, which allows you to ‘draw’ on the image

Create a rectangle around one of the options, and when prompted select the appropriate Category which the selection relates to.

Repeat this for all the options in the image.

Average Legend

To build this I simply duplicated the Top 20 chart, removed all the pills from the Rows shelf, removed the Emoji pill from the Filter shelf and then changed the aggregation of the Position pill from SUM to AVG. I had to re-tweak the tooltips too.

And after all that, I hope you have all the components you need to deliver this solution. My published viz is here.

Happy Vizzin’! Stay Safe!

Donna

Can you predict the future?

Continuing on from last week’s challenge, Candra Mcrae set this #WOW2021 challenge to introduce another new feature of v2020.4 (so you’re going to need this version as a minimum to complete this challenge). Predictive modelling isn’t something I use at all, so I was quite reliant on the help documentation Candra referenced. I’m also not overly familiar with the various different models, and when they should be used, so this isn’t something I’m likely to use personally, without careful consideration and thought – it’s worth reiterating Candra’s warning :

Viz Responsibly: While predictive analysis in Tableau has never been easier, we encourage you to be intentional in how you use this powerful feature because not doing so (e.g., selecting a model type not appropriate for your data) could lead to inaccurate results.  

The focus of this blog will be

  • Building the main chart
  • Creating the tooltips
  • Determining the data for the title
  • Building the measure selector
  • Adding the measure selector interactivity

Building the main chart

The data provided has some records related to years before 1993, but the requirement is just to use data from 1993 onwards, so the first thing I did was to set a data source filter (right click on data source -> Edit Data Source Filters) to restrict the whole data source to Year 1 >=1993

Next I created the measures we need to display

Total Enrollment

[Total enrollment 2 All students]

simply references the existing measure.

% Black Students

SUM([Total enrollment 2 Black students]) / SUM([Total Enrollment])

% Non-Black Students

1- [% Black Students]

These 3 measures need to be displayed on a single chart where the actual measure displayed is determined by user selection. This selection will be driven by the use of a parameter, that will be set by a parameter action. For now, we just need to establish the parameter.

pSelect_Measure

This is simply a string parameter which will store the value of Total Enrollment by default.

Using this parameter, we can now decide which value we’re going to plot on the chart

Actual Value

CASE [pSelect_Measure]
WHEN ‘Total Enrollment’ THEN SUM([Total Enrollment])
WHEN ‘% Black Students’ THEN [% Black Students]
ELSE [% Non-Black Students]
END

Add this to a view by placing

  • Year 1 as continuous (green) pill on Columns
  • Actual Value on Rows

and you get the basic shape, although it’s not as ‘peaky’. This is resolved by editing the Actual Value axis (right click axis -> edit) and unchecking the Include zero checkbox.

Now change the text in the pSelect_Measure input parameter that’s displayed to % Black Students, and the chart will change. Verify it changes with the text % Non-Black Students too.

In reading through the Tableau KB documentation about Predictive Modeling Functions in Time Series Visualizations, I came to learn of the feature to Extend Date Range, something I’ve never come across before, and I’m not sure what version it first appeared in. Anyway, for this, you need to be working with a field which is a date datatype. The Year 1 field provided is an int.

I’m not entirely sure what I’ve done next is the optimum method, but it worked for me… some of it involved a bit trial and error when I came to defining and invoking the modelling feature later on. In writing this up, I’m essentially helping you to avoid the ‘back and forth’ steps I took in getting it all to work.

Anyway, I needed a date field to represent the year

Year

MAKEDATE([Year 1],1,1)

This resolves to a field containing 1st Jan YYYY for each Year in the data set.

Replace Year 1 on the chart with this field, and changing it to the ‘continuous’ instance of the date by choosing the second ‘Year’ option from the context menu

This changes the pill to green and then the option to Extend Date Range is visible on the menu. Set this to 5 years using the custom option

After doing this you’ll get a 5 nulls indicator displayed, which is basically saying there’s some years without any data. This what we expect for now.

Now onto the modelling part. We need a new calculated field to contain the predicted value using the Gaussian process regression.

Predicted Value

MODEL_QUANTILE(‘model=gp’, 0.5,[Actual Value],ATTR(DATETRUNC(‘year’,[Year])))

again, this took a bit of trial and error to get the date field in the required format.

Add this to the Rows and again edit the axis to ‘exclude zero’. You should now see the data extending for the predicted value beyond 2018. You can now hide the 5 nulls indicator (right click -> hide indicator)

You can now combine to be a dual axis chart (don’t forget to synchronise axis), and apply the relevant formatting to change the marks, apply relevant colours, hide axis & gridlines etc. Note, I set the area chart to 25% opacity, rather than the 75% stated in the requirement, as this gave me the colour most similar to the solution.

Creating the tooltips

Hovering over the chart, the tooltips display the Actual, Predicted and Residual (Actual-Predicted) value for each point. But depending on the measure selected, the format differs – Total Enrollment is in K and the others are in %.

We can’t use the number formatting feature of a field to resolve this, so we need to be a bit more creative. I confess I started creating individual fields for each measure (Actual, Predicted, Residual) based on the measure type selected (Total Enrollment, % Black Students, % Non-Black Students), but this meant I was creating loads of calculated fields, which just seemed a bit unnecessary.

So after a bit of a think, I realised there was a better way.

First up, let’s get our residual

Prediction Residual

[Actual Value]-[Predicted Value]

Now, if we put the values in a tabular form, you can see what the precision of the values are depending on the measure stated

We need to format appropriately. Both displays required the values to be formatted to 1 decimal place, and both have a suffix, either a K or a %.

To get the value in the required display format

Tooltip – Actual Value

IF [pSelect_Measure]=’Total Enrollment’ THEN [Actual Value]/1000
ELSE [Actual Value] * 100
END

Format this to 1 dp.

Create a similar field for the predicted value, which should also be formatted to 1 dp.

Tooltip – Predicted Value

IF [pSelect_Measure] = ‘Total Enrollment’ THEN [Predicted Value]/1000
ELSE [Predicted Value] * 100
END

And finally Tooltip – Residual

[Tooltip – Actual Value] – [Tooltip – Predicted Value]

This needs to be custom formatted to +#,##0.0;-#,##0.0 which ensures a + symbol is always displayed for positive values.

Pop these onto the tabular display we built earlier, and you can see the values are now displaying in the way we need

Finally we need to create a field to store the required suffix

Tooltip – Value Suffix

IF [pSelect_Measure] = ‘Total Enrollment’ THEN ‘K’
ELSE ‘%’
END

We can now add these 4 fields onto the Tooltip shelf of the ‘All’ marks card, and create the tooltip as required

Determining the data for the title

As we need to only use 2 sheets to build this solution, and 1 sheet will be required for the measure selection, we have to incorporate the summary data displayed at the top of the dashboard as part of the title of the chart viz.

In the title, we need to display the following :

  • The latest year in the provided data set (ie 2018)
  • The latest year including the extended date range (ie 2023 – 5 years later)
  • The actual value from 2018 based on the selected measure
  • The predicted value from 2023 based on the selected measure
  • An indicator to show whether the values were likely to increase or decrease

The requirement was to ensure there was no ‘hardcoding’. And as we’re working on getting information related to a specific row (ie year) in a set of data that consists of multiple rows (years), then we’re going to need to make use of table calculations for this.

Let’s start with the ‘easier’ bits first. We want the Year of the latest in the actual data set, and we want this value to be essentially stored against every row in the data

Latest Year

{FIXED: MAX([Year])}

This returns the value of 2018.

Latest Year + 5

DATE(DATEADD(‘year’,5,[Latest Year]))

This simply adds 5 years to the Latest Year, so returns 2023.

Now when I’m using table calculations, I often prefer to see what the data is doing in the table itself, so I can be sure I’m doing things correctly. With the ‘extended year’ stuff, it’s a bit fiddly creating the table from scratch, so I simply started by duplicating the chart sheet ‘as crosstab’ (right click on the sheet name tab, -> Duplicate as Crosstab). Rearrange the fields so Measure Names is on Columns and Year is on Rows and the ‘Tooltip’ named fields are visible. Add Latest Year and Latest Year+5 to Rows, and you can see how these fields show the same value against every row.

Now, remove these fields, as by adding them, we’ve lost the additional ‘extended dates’ rows (ie the ‘fake’ rows that don’t actually exist in the data). Ok, so now we want to get the Actual Value associated to 2018, but then perpetuate this across every row in the data.

Latest Year – Actual

WINDOW_MAX(IF MIN([Year]) = MIN([Latest Year]) THEN [Tooltip – Actual Value] END)

If the Year is the same as Latest Year, then display the value from the Tooltip – Actual Value field. The WINDOW_MAX table calc, then spreads this same value across all rows displayed. Format to 1dp and add this to the table.

We need to do something similar to get the Predicted Value for 2023

Latest Year +5 – Predicted

WINDOW_MAX(IF LAST()=0 THEN [Tooltip – Predicted Value] END)

If we’re at the last row in the data, then display the value from the Tooltip – Predicted Value field. Again the WINDOW_MAX spreads the value across all the rows. Set this to 1 dp and add to the table.

And now we just need to get the increase/decrease indicator

Increase | Decrease

IF ([Latest Year – Actual])-[Latest Year +5 – Predicted]>0 THEN ‘decrease’ ELSE ‘increase’ END

So now we know we’ve got the correct values we need, we can add these fields to the Detail shelf of the chart sheet, so we can reference them in the Title of the chart.

We also need the Latest Year and Latest Year +5 fields added to the Detail shelf, but when you add these, you’ll notice that you lose the ‘extended years’. You can fix this by wrapping the fields in an ATTR function. Double click on the field, which will allow you to ‘type in’ to the field.

You should now be able to create the text in the chart title

Building the measure selector

Phew! Are you still with me… there’s a fair bit going on already, and now we’ve got to build the chart that will drive the user selection.

On a separate sheet, add

  • Measure Names to Rows
  • Measure Values to Detail
  • Measure Names to Text
  • Measure Names to Filter, and restricted to the 3 original measures – Total Enrollment, % Black Students, %Non-Black Students

Uncheck Show Header on the pill on the Rows, then format

  • Set background colour of the pane to a navy blue
  • Set row & column borders to be white
  • Set the text label to be white text, centred, and increase the font
  • Turn off the tooltip

Adding the measure selector interactivity

Create the dashboard and add both the charts. To add the interactivity so that on click of a row in the Measure Selection sheet, it changes the measure being displayed, we need to add a dashboard action, that changes a parameter (Dashboard menu -> Actions -> Add Action -> Change Parameter). Set the action to run on Select when the Measure Select sheet is clicked. The target parameter is pSelect_Measure and the Measure Names field should be passed into this.

And with all that, you should hopefully now have a working solution. My published viz is here (note, my Measure Selection sheet is slightly different from what I’ve described above). The above is a bit simpler I think.

Happy vizzin’! Stay Safe!

Donna

Can you build a Control Chart?

Lorna Brown returned this week to set another table calculation based challenge involving a line chart which ‘on click’ of a point, exposed the ability to ‘drill down’ to view a tabular view of the data ‘behind’ the point. This is classic Tableau in my opinion – show the summarised data (in the line chart) and with ‘drill down on demand’. Lorna added some additional features on the dashboard; hiding/showing filter controls to change how the data is displayed in the chart, and a back navigational button on the ‘detail’ list.

The areas I’m going to focus on in this blog are

  • Setting up the parameters
  • Defining the date to plot on the chart
  • Restricting the data to the relevant years
  • Defining the reference bands
  • Colouring the marks
  • Working out the date range for the tooltip
  • Building the table
  • Drill down from chart to table
  • Un-highlight selected marks
  • Hide/Show filter controls
  • Add navigation button

Setting up the parameters

This challenge requires 3 parameters.

Select a Date

a string parameter containing the 2 options available (Date Submitted & Date Selected) for selection, which when displayed on the dashboard will be set to a single value list control (ie radio buttons)

Latest X Years

an integer parameter, defaulted to 3, which allows a range of values from 1 to 5.

NOTE – Ensure the step size is set to 1, as this is what allows the Show buttons option to be enabled when customising the Slider parameter control type.

STD

another integer parameter, defaulted to 1, that allows a range of values from 1 to 3

Defining the date to plot on the chart

The Select a Date parameter is used to switch the view between different dates in the data set. This means you can’t plot your chart based on date field that already exists in the data set. We have to create a new field that determines which date field to select based on the parameter

Date to Plot

DATE(DATETRUNC(‘week’, IIF([Select a Date]=’Date Submitted’,[Date sent to company], [Date received])))

The nested IIF statement, is basically saying, if the parameter is ‘Date Submitted’ then use the Date sent to company field, else use the Date received field. This is all wrapped within a DATETRUNC statement to reset all the dates to the 1st day of the week (since the requirement is to report at a weekly level).

Note – there was some confusion which field the parameter option should map to. I have chosen the above, but you may see solutions with the opposite. Don’t get hung up on this, as the principal of how this all works is most important.

Restricting the data to the relevant years

The requirement is to show ‘x’ years worth of data, where 1 year’s worth of data is the data associated to the latest year in the data set (ie from 01 Jan to latest date, rather than 12 months worth of data). So to start with I calculated, rather than hardcoded, the maximum year in the data

Year of Latest Date

YEAR({MAX([Date to Plot])})

Then I could work out which dates I wanted via

Dates to Include

[Date to Plot]>= MAKEDATE([Year of Latest Date] – ([Latest X Years]-1),1,1)

In the MAKEDATE function, I’m building a date that is the 1st Jan of the relevant based on how many years we need to show.

So if Year of Latest Date is 2020 and Latest X Years =1 then Year of Latest Date – (Latest X Years -1) = 2020 – (1-1) = 2020 – 0 = 2020. So we’re looking for dates >= 01 Jan 2020.

So if Year of Latest Date is 2020 and Latest X Years =3 then Year of Latest Date – (Latest X Years -1) = 2020 – (3-1) = 2020 – 2 = 2018. So we’re looking for dates >= 01 Jan 2018.

This field is added to the Filter shelf and set to true.

So at this point, our basic chart can be built as

  • Year on Columns (where Year = YEAR([Date to Plot])), and allows the Year header to display at the top
  • Date to Plot on Columns, set to Week Number display via the pill dropdown option, and also set to be discrete (blue pill). This field is ultimately hidden on the display.
  • Number of Complaints on Rows (where Number of Complaints = COUNT([XXXX.csv], the auto generated field relating to the name of the datasource).

To get the line and the circles displayed, this needs to become a dual axis chart by duplicating the Number of Complaints measure on the Rows, synchronising the axis and setting one instance to be a line mark type, and the other a circle.

Defining the reference bands

The reference bands are based on the number of standard deviations away from the mean/ average value per year.

Avg Complaints Per Year

WINDOW_AVG([Number of Complaints])

Once we have the average, we need to define and upper and lower limit based on the standard deviations

Upper Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * [STD])

Lower Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * -1 * [STD])

Add both these fields to the Detail shelf of the chart viz (at the All marks card level) and set the table calculation of each field to Compute By Date to Plot

This ‘squashes’ everything up a bit, but we’ll deal with that later.

Add a Reference Band (right click on axis – > Add Reference Line) that ranges from the Lower Limit to Upper Limit.

If an Average Line also appears on the display, then remove it, by right clicking on the axis -> Remove Reference Line – > Average

Colouring the marks

I created a boolean field based on whether the Number of Complaints is within the Upper Limit and Lower Limit

Within STD Limits?

[Number of Complaints]<[Upper Limit] AND [Number of Complaints]>[Lower Limit]

Add this to the Colour shelf of the circle mark type, and set to Compute Using Date to Plot. The values will be True, False or Null. Right click on the Null option in the Colour Legend, and select Exclude. This will add the Within STD Limits? to the Filter shelf, and the chart will revert back to how it was. Adjust the colours accordingly.

The Tooltip doesn’t show true or false though, so I had another field to use on that

In or Out of Upper or Lower Limits?

If [Within STD Limits?] THEN ‘In’ ELSE ‘Out’ END

Working out the date range for the tooltip

The Tooltip shows the start and end of the dates within the week. I simply built 2 calculated fields to show this.

Date From

[Date to Plot]

This 2nd instance is required as Date to Plot is formatted to dd mmm yyyy format and also used in the Tooltip. Whereas Date From is displayed in a dd/mm/yyyy format.

Date To

DATE(DATEADD(‘day’, 6, [Date to Plot]))

Just add 6 days to the 1st day of the week.

Building the table

Create a new sheet and add all the relevant columns required to Rows in the required order. For the last column, Company response to consumer, add that to the Text shelf instead (to replace the usual ‘Abc’ text). The in the Columns shelf, double click and type in ‘Company response to consumer’ which creates a ‘fake’ column heading. Format all the text etc to make it all look the same.

Add the Dates to include = true filter.

Also add the WEEK(Date to Plot) field to the Rows shelf, as a blue discrete field (exactly the same format as on the line chart). But hide this field (uncheck Show Header). This is the key linking field from the chart to the detail.

Drill down from chart to table

Create one dashboard (Chart DB) that displays the chart viz. And another dashboard that displays the table detail (Table DB). On the Chart dashboard, add a Filter Dashboard Action (Dashboard menu -> Actions -> Add Action -> Filter), that starts from the Chart sheet, runs as a Menu option, and targets the Detail sheet on the Detail dashboard. Set the action to exclude all values when no selection has been made. Name the action Click to Show Details

On the line chart, if you now click a point on the chart, the tooltip will display, along with a link, which when clicked on, will then take you to the Detail dashboard and present you with the list of complaints. The number of rows displayed should match the number you clicked on

Un-highlight selected marks

What you might also notice, is when you click on a point on the chart, the other marks will all ‘fade’ out, leaving just the one you selected highlighted. It’s not always desirable for this to happen. To prevent this, create a new field called Dummy which just contains the text ‘Dummy’. Add this onto the Detail shelf of the All marks card on the chart viz.

Then on the chart dashboard, add another dashboard action, but this time choose a highlight action. Set the action to run on select and set the source & target sheets to be the same sheet on the same dashboard. But target highlighting to selected fields, and select the Dummy field only

Hide/Show filter controls

Check out this post by The Data School that explains very simply how to work with floating containers to show/hide buttons. When creating in Desktop, the ‘onclick’ interactivity won’t work, you’ll have to manually select to show and hide, but once published to Tableau Public, it’ll behave as desired.

You have options to customise what the button looks like when the container contents are hidden, and what it looks like when they’re shown, via the Button Appearance

Add Navigation Button

On the Detail dashboard, simply add a Navigation object to the dashboard

and edit the settings to navigate back to the chart dashboard, as well as customise the appearance

Hopefully I’ve covered all the key features of this challenge. My published viz is here.

Happy vizzin’! Stay safe!

Donna

Can you find the variance along a line?

So after a couple of weeks off blogging due to Christmas, I’m back providing solutions for #WOW2021! New contributor Candra Mcrae started off the year with this gentle workout focussing on table calculations. For those who have followed my previous blogged solutions, you’ll probably recall that table calcs don’t phase me as much as they do some – I started using Tableau before LODs existed, so they were the only tool at my disposal in the past. That said, I’m still not such an expert that I get it right first time – there’s often plenty of trial and error as I choose which fields I want to compute by, although I’ve got much better at this since I read Andy Kriebel’s Table Calculations Overview a couple of years ago.

As with any challenge that involves table calcs, I tend to start by building out a tabular view of all the data I’m going to need to build the viz. This ensures I can validate the data much easier and set the table calculation settings to what I need.

So let’s start….

For the line chart itself, we’re simply going to be plotting Year against Food insecurity which has been formatted to 1 decimal place and displays the % as a suffix

We will also need to capture 2 other values which will represent the coloured circles. Parameters are needed to help identify what these circles will be.

pSelected Year is an integer parameter that can be built by right clicking on Year and selecting Create -> Parameter. This will populate the parameter with all the values of the Year field. Default the value to 2018 and adjust the formatting of the displayed value so that it is 0 decimal places and include thousand separators is unchecked.

With this parameter, we can capture the value associated which is represented by the pink dot in the viz

Selected Year %

IF [Year] = [pSelected Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END

It’s a bit more work to identify the black dot, as this will vary based on another parameter

pComparison

I created this an integer parameter storing the values 0, 1 & 2 (defaulted to 2), but aliased for display as First Year, Most Recent Year, Previous Year. I’ll be using this is an case statement/if clause later and comparing integers to strings is much more efficient in Tableau.

I like to take things step by step, especially when there’s table calcs involved, to ensure all the values I’m referencing are correct, so rather than identifying the selected value in a single calculated field, I’m using multiple.

Firstly I want to identify the 1st year in the dataset (without hardcoding).

First Year

WINDOW_MIN(MIN([Year]))

This will store the value of the earliest year (1995) against every row in the data that is outputted in the view (a bit like you would get with {FIXED : MIN([Year])}, but this is a no LOD challenge).

From this I can work out

First Year %

IF MIN([Year]) = [First Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END

Notice the MIN() functions used in this statement, as opposed to the Selected Year % above. This is because First Year is a table calc which is an aggregation, and subsequently other fields referenced in the calculation also need to be aggregated. In this case other aggregations such as AVG, MAX, ATTR would also suffice.

Similarly, I’m going to derive the Latest Year % with

Latest Year

WINDOW_MAX(MAX([Year]))

Latest Year %

IF MIN([Year]) = [Latest Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END

Finally, I’m also going to work out the value for the previous year

Previous Year

[pSelected Year]-1

Previous Year %

IF [Year] = [Previous Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END

With these 3 % fields, I can now create a fourth field which stores the value of the % I want to compare with

Selected Comparison %

CASE [pComparison]
WHEN 0 THEN [First Year %]
WHEN 1 THEN [Latest Year %]
WHEN 2 THEN SUM([Previous Year %])
END

This ultimately just stores the 2nd value required for the black dot. This could all have been written within this single calculation, but I find it easier to troubleshoot if things are broken down a bit.

Putting these onto a table we can see how the values in each row change, as the parameters are changed.

Note, for all the table calculation fields (all denoted by the triangle symbol on the pill), I have explicitly set them to Compute Using the specific dimension of Year rather than the default of table down. While this will give the same result, I prefer to be explicit to ensure the values don’t change if pills get subsequently moved around the canvas (in the case of the Selected Comparison % field, all Nested Calculations within the Edit Table Calculation dialog box need to be set).

This is enough information to build the main viz itself by

  • adding Year to Columns (green continuous pill)
  • adding Food insecurity to Rows
  • adding Selected Year % to Rows next to Food insecurity
  • then drag Selected Comparison % to the Selected Year % axis which will automatically change the display to have Measure Values on Rows instead. Set the table calculation setting to compute by Year

This chart can then be set to be dual-axis and the axes synchronised. The Food insecurities should remain as as line mark, and the Measure Values should be a circle. The colours, formatting and tooltip then need to be applied.

Now we need to go back to our table of data to build out other calculations. The requirement is a single viz, so we need to provide the % value of the selected year within the title of the chart, along with the difference from the comparison value. For this to work, we need to store the relevant values against every row in the data set.

We already have the Selected Year % value identified, but this is only captured against the row of the selected year in the data output. To get it to display against every row we need

Window Max – Selected Year %

WINDOW_MAX(MIN([Selected Year %]))

This is formatted to 0 dp with the % sign as suffix, as this is the field that will be displayed in the title. Added to our data table, with the table calc set to compute by Year, you can see the value replicated across every row

Similarly, we already have the Selected Comparison % captured, but in order to work out the difference, we also need to get this value against every row too

Window Max – Selected Comparison %

CASE [pComparison]
WHEN 0 THEN WINDOW_MAX([First Year %])
WHEN 1 THEN WINDOW_MAX([Latest Year %])
WHEN 2 THEN WINDOW_MAX(MAX([Previous Year %]))
END

Adding this in to the table (and remembering to set the table calc settings), you can also see the relevant value perpetuated against every row. Change the pComparison value and you’ll see the values change accordingly, but still the same on every row.

So for the difference…. at this point I chose to deviate from the solution published. We’re already dealing with a measure that is quantified as a % (as opposed to a measure that is quantified as £ say). So to get the % difference between two percentage measures, I simply chose to show the difference between the two values (ie selected – comparison). Some would refer to this a the point difference between the values. This makes most sense to me for this particular scenario. The alternative is to calculate the % difference in the more traditional way of (selected – comparison) / comparison as you may do if you were presenting the % difference between the value of sales in different years. But I personally found the result could be confusing to the reader .

Difference

[Win Max – Selected Year %]-[Win Max – Selected Comparison %]

BUT, the value displayed within the title is the absolute value (ie no -ve sign) and actually doesn’t display if the value is 0 (which you get if you select the year to be 2019 and comparison to most recent year). So I resolved this with

DISPLAY : Difference

IF [Difference] <> 0 THEN ABS([Difference]) END

which is formatted to 1 dp with % suffix

Finally, we have a symbol that is used to indicate if the change is +ve, -ve or remains the same.

Difference Indicator

IF [Difference] > 0 THEN ‘▲’
ELSEIF [Difference] < 0 THEN ‘▼’
ELSE ‘N/C’
END

Add this into the table too, once again remembering to set the table calc properties for all nested calculations.

So now you have these fields, you can add Window Max – Selected Year % and DISPLAY : Difference and Difference Indicator to the Detail shelf of the All marks card on the chart viz, and these will then be available in title dialog to add. Once again, ensure you set the table calcs to compute by Year.

You’ll also need to add some spacing in the title, to allow the parameter controls to be ‘floated’ into place on the dashboard. Getting the position right is really tricky. I positioned it very carefully on Desktop, but when I published, the controls were in completely different places. The easiest way I found to resolve this, was to use the Edit feature in Tableau public to edit the dashboard online and move the objects that way.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you highlight profits with Measure Names?

Luke Stanke provided his last challenge of #WOW2020 for the year with this viz to utilise Measure Names & Measure Values. This plots Cost against Sales per Sub-Category (the circles), which are then bounded by a lozenge. The Sales circle and the lozenge are coloured based on profitability, and the lozenges are labelled by Profit, although the position of the label is also based on profitability.

Obviously as the title of the challenge suggests, using Measure Names & Measure Values is key to this. As usual I’ll try to just pull out the main points I think you might find most tricky.

  • Building the core chart
  • Aligning the Sub-Categories against a horizontal line
  • Colouring the circles & lozenge
  • Positioning the labels
  • Formatting the labels
  • Creating a border around the white Cost circle

Building the core chart

You’ll need to create a calculated field to store the cost

Cost

SUM([Sales]) – SUM([Profit])

Once done, then add Sub-Category to Rows and sort by descending by Profit. Add Measure Values to Columns and filter just to Sales and Cost. Change the mark type to circle.

Add a 2nd instance of Measure Values to Columns and change the mark type to Line. Add Measure Names to Path.

Then make this chart dual axis and synchronise the axis. This will give the base chart.

Aligning the Sub-Categories against a horizontal line

Whenever you add a blue discrete pill to the rows or columns, you’ll get a ‘header’ pane, which means the label for the pill is by default positioned in the centre of a row, as demonstrated below if I add row dividers to the chart

The requirement is to show the row label aligned with a horizontal line. To achieve this, we need to add a MIN(0) field the Rows. Adding this green pill, creates an axis which has a 0 horizontal 0 line, which is positioned exactly in alignment with the row label

Removing the row dividers, and uncheck Show Header on the MIN(0) pill, and the right aligning the row label, we get the desired result.

Colouring the circles & lozenge

The circles are either coloured white for Cost or dark red or green for Sales depending on the profitability of the Sub-Category.

So we need a new calculated field

Is Profitable?

SUM([Profit])>0

This returns true or false.

On the circles Measure Names mark card, add Is Profitable to the Colour shelf. If it’s not already there, then add Measure Names to the Detail shelf, then select the detail icon and choose the colour icon to also add Measure Names to the Colour shelf

This has the effect of giving you 4 colour combinations

which can then be edited to set the 2 combinations relating to Cost to white, Sales & True to dark green and Sales & False to dark red (at this point, you probably want to be setting the background colour of the whole worksheet to light grey (right click on sheet, format, select the Shading icon)

For the lozenge, you just need to add the Is Profitable field the the Colour shelf of the line Measure Names card, and adjust the size of the to make it slightly bigger than the circles (you may need to move marks to back to get the lozenge to sit behind the circles).

Positioning the labels

So the tricky thing about the labels is where they are positioned – they are to the right for the profitable sub-categories, and to the left for the non-profitable sub-categories. When you add a label, you can’t provide that sort of conditional logic to control the positioning. However, we happen to have 2 sets of marks cards (as dual axis), and so can use different fields to apply the labelling.

Label Profit is +ve

IF [Is Profitable] THEN SUM(Profit) END

This will only return a value for profitable sub-categories, otherwise the field will store blank.

Label Profit is -ve

IF NOT([Is Profitable]) THEN SUM(Profit) END

Conversely, this will only return a value for the non-profitable sub-categories.

Add the Label Profit is +ve field to the Label shelf of the Circle marks card. Adjust the positioning to be middle right, and adjust the settings as below (tbh this was just a bit of trial and error to find this worked :-)). You may want to additionally add a few <spaces> to the start of the label text to add a bit of breathing room.

Then on the Line marks card, add the Label Profit is -ve field to the Label shelf, but this time choose to Label Line Ends, and select Label end of line. Note – you can try to apply the same settings as above, but I found for some reason it increased the size of the mark around the line end… weird…

Formatting the labels

Right click on the Label Profit is +ve/-ve fields created and custom format with

+”$”#,##0;-“$”#,##0

Creating a border around the white Cost circle

Finally, a sneaky requirement was to add a border around the white circle that represented Cost only. This implied that using the formatting options on the Colour shelf that allows a border to be added to the mark wasn’t going to work, as this will apply to all marks and not just to the Cost circle.

I handled this by creating a 2nd instance of the Cost field (right click Cost and select duplicate).

I then added this Cost (copy) field to the Measure Values section of the chart on the left hand side. This doesn’t appear to do much, since the mark is directly on top of an existing mark, but you will see the Colour legend associated to the Circle marks card change and add 2 new entries.

I changed the colours of the 2 new entries in the Colour legend to a dark grey, then added Measure Names to the Size shelf of the Circles marks card. I then adjusted the range and size of the circles so that the Cost measure is slightly smaller than the Cost (copy) measure. Ultimately all 3 circles are slightly different sizes, but the range is so narrow, it’s hard to tell.

And that’s it. A lovely looking viz with a fair bit going on under the bonnet, but nothing too complex! My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a variable width bar chart?

For #WOW2020 Week 48, Jami Delagrange set the challenge based on a problem she’d been puzzling over for a while.

The data set was one Jami created herself about turkeys creating a ‘conference’ (it is Thanksgiving week in the US).

The above is basically 2 bar charts plotting the time an event happened (on the x-axis) against the number of turkeys attending the event (on the y-axis). One bar chart is coloured based on the location, the other is a white bar which has a width based on the duration of the event.

There are a couple of calcs needed for the basic data

# Turkeys

COUNTD([User Name (Original Name)])

Duration (mins)

DATEDIFF(‘minute’,[Start time],[End time])

With these we can get a basic set of data

The Start Time and End Time fields can be custom formatted to hh:mm AMPM.

The first bar chart we need is very simple, plotting Start Time against # Turkeys, coloured by Location.

For the second chart, we need to make use of the Size shelf, and the feature that allows you to fix the size based on a field. If we add Duration (mins), we don’t get what we need

The bars are far too wide. As the Start Time being plotted is at the ‘minute’ level, each unit of the axis is 1 minute of a day. There are 24 * 60 = 1440 minutes in a day. So the size needs to be based as a proportion of the number of minutes in a day ie

Duration Proportion of Day

[Duration (mins)] / (24 * 60)

Adding into our data table, you can see what these values translate to

and if we drop this onto the Size shelf instead, we get the required display

Build these charts as a dual axis, and you’ve got the output. Tableau’s own help article, discusses the Size feature a bit more.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a small multiple waterfall chart?

For this week’s #WOW2020 challenge, Lorna Brown asked us to recreate a waterfall chart – a chart style that hasn’t featured in many previous challenges (if at all), and is always a useful one to know how to build.

I’m familiar with these, and this challenge didn’t cause me too many issues, so this blog is going to be brief.

  • Building the waterfall
  • Small multiple / grid layout
  • Adding the month label

Building the waterfall

So I’d built out the basic waterfall for each month and day by plotting Year(Order Date), Month(Order Date) and Day(Order Date) on Columns. The Day(Order Date) field was set to Show Missing Values so each day without an order was still plotted, and I was trying to figure out how to get the additional ‘long’ bar at the end.

I worked out it was essentially a ‘total’ bar and when I duplicated my data as crosstab and played round with the data in a tabular form, I got the subtotals I needed displayed.

But I seemed to be having issues displaying these on the chart view. So I turned to my usual route, Google, and had a search, and came across this blog from Tim Ryan at The Data School, which gives you the complete guide to building the waterfall, so there’s no need for me to repeat it all – thanks Tim! 🙂

My issue was I had a green continuous Day(Order Date) field rather than a blue discrete one – doh!

The only couple of things you need to make note of – you need to ensure you have 0 displayed for the missing dates

Actual Profit

ZN(SUM([Profit]))

and the gantt bars should be coloured red for negative profit, blue for positive and grey for the missing days

Colour

IF SUM([Profit])<0 THEN ‘Red’ ELSEIF SUM([Profit]) > 0 THEN ‘Blue’
ELSE ‘Grey’
END

Small multiple / grid layout

For this you need fields to add to the Rows and Columns shelf that position the month in the appropriate cell.

The Quarter(Order Date) (blue discrete) on Rows, is just used to define the row a month lands in.

You then need

Cols

IF MONTH([Order Date])%3 = 0 THEN 3
ELSE MONTH([Order Date])%3
END

which assigns each month a value of 1,2 or 3. You’ll need this on the Columns shelf.

Adding the month label

The label shows the month and the total profit in the month, so I created an LOD for this

Profit for Month

{FIXED YEAR([Order Date]),MONTH([Order Date]): SUM([Profit])}

From this I wanted the maximum value of all the monthly profits

Max Monthly Profit in Year

{FIXED Year([Order Date]): MAX([Profit for Month])}

I then used a dual axis to plot this field on the Rows, set the mark type to a line and set the opacity of the line colour to 0%, so it disappears.

The month and value were then added to the Label shelf and the label set to Label start of line only and also right aligned to get the required positioning.

And that’s it. I said this would be brief 🙂 My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a reference line for each dimension?

Ann Jackson returned this week with a challenge primarily focussed on formatting.

The core requirement this week was to be able to present different measures on a chart, based on a user selection, but where the values displayed were of differing numerical formats

  • Sales per order in $ to 0 decimal places, formatted to show a ‘,’ every 1,000.
  • Profit Ratio as a % to 1 decimal place
  • Items per order as a numerical value to 2 decimal places

My focus points this week are

  • Measure swapping
  • Adding the line labels
  • Labelling the y-axis
  • Adding the reference lines
  • Building the blocks

Measure Swapping

This is technique that should be in everyone’s arsenal, as it’s a great way to present multiple views of the data without the need for multiple instances of the chart – it saves space and clutter but continues to allow flexibility.

The 3 measures required needed to be defined through calculated fields

Profit Ratio

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

Sales per Order

SUM([Sales])/COUNTD([Order ID])

Items per Order

SUM([Quantity])/COUNTD([Order ID])

A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below

SELECT A MEASURE

Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected

Display Measure

CASE [SELECT A MEASURE]
WHEN ‘Profit Ratio’ THEN ROUND([Profit Ratio]*100,1)
WHEN ‘Sales Per Order’ THEN ROUND([Sales Per Order],0)
WHEN ‘Items Per Order’ THEN ROUND([Items Per Order],2)
END

It’s within this field I chose to define the number formatting I wanted to display, and by then setting the number format of the field to Number Standard, it seemed to show what I intended on hover. Add Display Measure to Rows and plot against QUARTER(Order Date) coloured by Category to get the display below.

Adding the line labels

However while the numeric format is what’s required, I haven’t got the $ or % symbol, and I can’t apply that as part of the default formatting.

Instead I created explicit prefix & suffix fields

$ Label Prefix

IF [SELECT A MEASURE] = ‘Sales Per Order’ THEN ‘$’ END

% Label Suffix

IF [SELECT A MEASURE] = ‘Profit Ratio’ THEN ‘%’ END

Adding these 2 fields to the Detail shelf, they can then be referenced in both the Tooltip and the Label as follows

<$ Label Prefix><AGG(Display Measure)><% Label Suffix>

Based on the logic, either both fields will be NULL/blank else, only one will be populated, so you’ll never get $1,000% displayed!

Labelling the y-axis

Amend the y-axis to delete the Display Measure title, then add the SELECT A MEASURE parameter to the Rows shelf. Rotate and format accordingly.

Adding the Reference Lines

Adding a reference line – simples, surely! But why would Ann be making a challenge if it was that easy….hmmmmm! So what were the challenges posed here

  1. If you add an ‘Average’ reference line, you don’t get a value per line (even if you select the ‘per cell’ option) – you just get one average line. If the chart was split so there was a row per category, you’d be able to get this.

2.The lines displayed can’t be created via a ‘dual’ axis chart where the 2nd axis is showing the average, because the line format is a finely dotted line, and we can’t format a line mark this way. Proper reference lines can be formatted though, so I concluded the lines had to be true reference lines.

3. However, the labelling of a reference line is quite limited, and while I can show the value, I can’t use other calculated fields (ie the Prefix/Suffix fields) on the reference line label…

I came up with the following solution : create separate fields to store the AVG values for each Category, so that I could add 3 separate reference lines to the main chart; then create a dual axis line chart which also showed the average per category, label the line accordingly, and reduce the opacity of the line to 0%.

Ref Line Per Category

WINDOW_AVG([Display Measure])

Stores the average of the data displayed, and can be varied based on the table calc settings.

Ref Line – Tech

IF MIN([Category]) = ‘Technology’ THEN [Ref Line per Category] END

Only stores the average for the Technology data. I created equivalent ones of these for Ref Line – Office and Ref Line – Furniture

All 3 fields were added to the Detail shelf, then added as 3 different reference lines, coloured and formatted as a dotted line accordingly

To make the labels, I added Ref Line per Category to the Rows shelf to create a secondary axis. The table calculation was set as below

This produces a straight line for each category on a second chart, which I duly labelled by choosing to label the start of line, and aligning top left

I then set the opacity of the line colour to 0%, which makes the line disappear

I then set the chart to be dual axis, and synchronised the axis.

Building the blocks

For the block chart, I started by building a Tree Map (using Show Me) based on Category and the Ref Line Per Category fields.

I created a Rank field as

RANK([Ref Line per Category])

which I added to the Tooltip. I also then filtered the chart to Rank=1 to give me the main block. I then duplicated this sheet, and changed the rank filter to Rank=2, and repeated again for Rank=3. This gave me 3 sheets I could then organise onto the dashboard, as just using the tree map view directly, I couldn’t control how the different sections would display.

This was a fun challenge this week, slightly less taking than the previous weeks! My published viz is here.

Happy vizzin’! Stay Safe!

Donna