Let’s switch those measures!

Donna ColesLeave a comment

It was time for Lorna to add her iteration to the dashboard challenge that’s been set over the last few weeks. The challenge this week was to incorporate a measure swap control which allows the user to select which measures they want to compare.

Lorna gave the option to build out either your own or an already published solution, or to build a simpler brand new viz that just demonstrates the technique.

By incorporating this measure swap control into an existing solution, existing calculated fields and other objects will need to be adjusted/renamed. I started to build against my own solution, but that already had multiple instances of calculated fields where I’d chosen to change from a table calculation version in week 5 to an LoD based solution in week 6. I decided instead to use Kyle’s solution from week 7, which is published here.

However, for the purposes of this blog, I will build out the simple solution, so the focus is on what you need to do to build the control.

Building the Measure Selector

So to start, I simply copied the text for the secondary data source off the challenge page

I then opened Tableau Desktop, connected to the Orders sheet of the relevant Superstore Sales excel spreadsheet, then on the Data menu, selected Paste, which added a ‘clipboard’ data source and presented the data on a sheet.

To start with I chose to Alias the Number field (right click > Aliases) to map the numbers in the data source to the names of the measures we want, as below

The values selected are going to be captured within a parameter, so I then set these up

pMeasureX

Integer parameter defaulted to 1 (ie Sales)

I created a similar pMeasureY too, this one defaulted to 3 (ie Profit Margin).

For the selector control, we need to indicate which measure has been selected, so we need

X Selected?

[pMeasureX]=[Number]

and

Y Selected?

[pMeasureY]=[Number]

I then added Number to Rows and created 3 MIN(1) fields on the Columns shelf

On the first MIN(1) marks card, I changed the mark type to Shape, then added X Selected? to the Shape shelf. I adjusted the shape using options from the Ratings shape palette, and adjusted the size to suit.

On the 2nd MIN(1) marks card, I changed the mark type to Circle, reduced the size to as small as possible, and changed the colour to be completely transparent (Opacity = 0%). I then added Number to the Label shelf, changed the alignment to be Middle Centre, and adjusted the height of the rows, so all the text labels are visible.

On the 3rd MIN(1) marks card, I repeated the steps taken for the 1st marks card, but added Y Selected? to the Shape shelf instead.

I then hid the axes and the Number pill (uncheck Show Header), and removed all row/column borders and gridlines/zero lines.

When this sheet is added to the dashboard, we’re going to need to add parameter actions to pass information from this sheet into the pMeasureX and pMeasureY parameters. For this I found I needed to create copies of the Number field, as using the same field to control both selectors caused me some issues. So I created

Number (X)

[Number]

and added this to the Detail shelf of the 1st MIN(1) marks card.

And then

Number (Y)

[Number]

which was added to the Detail shelf of the 3rd MIN(1) marks card.

Another feature of this sheet when added to the dashboard, is we don’t want the circle selected to be highlighted/remain selected, so we’ll use a filter action to solve that. But again we need some additional fields on the sheet to help us with that. Create calculated fields

True

TRUE

False

FALSE

And add both of these to the Detail shelf of the All Marks card.

Finally adjust the title of the sheet to contain the text ‘X Select a Measure Y’. You’ll need to play around with the number of spaces to use. It’s best to wait until the sheet is on the dashboard to get it right.

Building the Scatter Plot

On a new sheet, the first thing needed for this challenge is to create

Profit Ratio

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

Now we need to define fields that will determine what measure to display based on the value stored in the relevant parameter

X Measure

CASE [pMeasureX]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Y Measure

CASE [pMeasureY]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Add X Measure to Columns and Y Measure to Rows, and add State to Detail.

We need to label the X & Y axis based on the name of the selected measure, so we need

X Dimension

CASE [pMeasureX]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Columns shelf

Y Dimension

CASE [pMeasureY]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Rows shelf

Edit both axes and remove the title from both. Right click on the word ‘Profit Ratio’ and Rotate Label. Right click on ‘X Dimension’ at the top and select hide field labels for columns. Right click on ‘Y Dimension’ on the left and select hide field labels for rows. Format to remove the row and column gridlines.

The State a user clicks on/selects needs to be captured within a parameter

pSelectedState

String parameter defaulted to New Jersey

We then need to determine if the State matches that selected

Is Selected State?

[State]= [pSelectedState]

Change the mark type to Circle, then add Is Selected State? to the Colour shelf and adjust to suit. Add a border to the mark too. Drag the ‘True’ value on the colour legend so its listed above ‘False’.

Add Is Selected State? to the Size shelf too, and adjust so the sizes are reversed, and alter the range.

Use the slider on the Size shelf too to make all the circles smaller too if need be.

Adjust the Tooltip so the text will be dynamic based on the measure selections too.

Then, modify the title of the sheet, so it too is dynamic.

Finally, this sheet too also needs to ensure the State circle clicked on doesn’t remain ‘selected’ so as above, create a True = TRUE and a False = FALSE calculated fields, and add these to the Detail shelf.

Adding the interactivity

Add both the sheets to a dashboard.

Create the following dashboard actions:

Set X Measure

A parameter action, that runs on Select of the Measure Selector sheet and passes Number (X) into the pMeasureX parameter.

Create a similar parameter action called Set Y Measure that passes Number (Y) into the pMeasureY parameter.

Create a dashboard filter action Selector Sheet – Unhighlight which on select of the Measure Selector sheet on the dashboard, targets the Measure Selector sheet directly, passing True = False into the filter. All values should show when the selection is cleared.

With these 3 actions, you should be able to test what happens when you click the different combinations.

Now we need 2 further dashboard actions

Set Selected State

A parameter action that on select of the Scatterplot passes the State field into the pSelectedState parameter.

Add finally, add another dashboard filter action, Scatter – Unhighlight that on select of the Scatter sheet on the dashboard, targets the scatter sheet directly, passing the fields True = False as the filter.

Hiding the Measure Selector

On the dashboard, select the Measure Selector sheet and select the Add Show/Hide Button from the context menu.

This will create a X button that can then be moved and positioned where required.

On the context menu of the X button, select Hide and the measure selector sheet will disappear, and the X will be replaced by a different image

Select Show from the context menu of this button, and the sheet will reappear. Test the functionality in presentation mode where simply clicking the button will invoke the show/hide behaviour.

And this should be the core functionality to demonstrate this measure swapping feature. My published viz based on this more basic solution is here.

If you’re aiming to build on the viz you’ve built over the last 3 weeks, then you need to replace any references you had to SUM([Sales]) and [Profit Ratio] in the various calculations with the X Measure and Y Measure fields accordingly. You’ll also need to adjust tooltips and titles of the various sheets using the X Dimension and Y Dimension fields as appropriate. My adapted solution built on from Kyle’s week 7 solution is here.

Happy vizzin’! Stay Safe!

Donna

Adding more detail & context

Donna ColesLeave a comment

This week it was Kyle’s turn to add a 3rd piece to the dashboard (see challenge here).

I chose to build on my previous week’s solution, which you can download from here, so all fields referenced etc will be based on that (ie I may reference fields that don’t exist in the solution published by the #WOW crew, and that have not been created as part of this stage of the solution).

For this part of the challenge, we’re looking to add a trend line, which displays a line for each row in the bar chart above, which means we need to display the median values of the Sales and Proft Ratio measures, since the ‘Other’ bar represents a group of multiple states.

We also need to colour and size the lines according to whether the bar chart has been clicked on or not.

First up, to build the line chart, we need to create some new LoD fields, as the ones we have so far have been FIXED at a State level, and now we need to consider the month of each order. I’m essentially going to repeat the steps I used when building the bar chart, but this time as the month of the Order Date into the calculation

Sales by State & Date

{FIXED [State], DATETRUNC(‘month’, [Order Date]): SUM([Sales])}

PR by State & Date

{FIXED [State], DATETRUNC(‘month’, [Order Date]): SUM([Profit])/SUM([Sales])}

From these, I can then create the measures I need for the display

Sales by Display State & Date

{FIXED [State To Display], DATETRUNC(‘month’, [Order Date]): MEDIAN([Sales by State & Date])}

format this to $ with 0 dp

PR by Display State & Date

{FIXED [State To Display], DATETRUNC(‘month’, [Order Date]): MEDIAN([PR by State & Date])}

format this to % with 0 dp

I can then build the initial line chart, with Order Date on Columns, set to the continuous month level (eg May 2020) and Sales by Display State & Date and PR by Display State & Date on Rows. Add State To Display to Detail.

Now we need to colour and size the lines based on a combination of whether the State is the one selected in the scatter plot, or whether the user has clicked on one of the bars.

I decided that I would capture the ‘state’ the user selected on the bar into a new parameter. So I created

pSelectedStateFromBar

A string parameter defaulted to ‘blank’

With this, I can the create

Size – Line

[State To Display] = [pSelectedStateFromBar]
OR
[Selected State]

This returns true if the State To Display field matches the value captured in the parameter, or the (existing) Selected State field is true.

Add this to the Size shelf on the All Marks card and adjust so the True option is slightly thicker that the False option. You may need also need to adjust the slider against the Size shelf to get the thickness just right.

We also use these 2 parameters to determine what the colour of the line needs to be

Colour – Line

IF [Selected State] AND ([pSelectedStateFromBar]=” OR [pSelectedStateFromBar]=[State]) THEN ‘dark’
ELSEIF [State To Display]=[pSelectedStateFromBar] THEN ‘mid’
ELSE ‘light’
END

Add this onto the Colour shelf. You’ll never have all 3 options displayed at the same time, so you’ll need to set the pSelectedSateFromBar to empty and to a value in order to adjust the colours. You’ll also need to ensure ‘dark’ is listed above ‘light’ and then ‘mid’ is listed above ‘light’ so these lines appear ‘on top’.

The chart just needs tidying up now – edit/remove the axis titles, reduce the size of the text on the axis, remove gridlines and set the tooltips.

Once done, you’re ready to add to the dashboard.

I rearranged my dashboard to have a horizontal layout container to with the scatter plot in the left hand column, and a vertical container in the right hand column. The vertical container then had the bar chart above the line chart.

The final step was to the then add a parameter action that on ‘select’ of the bar chart, it passed the State to Display value into the pSelectedStateFromBar parameter.

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

Happy vizzin’! Stay Safe!

Donna

Adding Detail & Context

Donna ColesLeave a comment
It was Sean Miller’s turn to expand on last week’s #WOW2022 challenge, by adding an additional viz to the scatter plot (challenge details here).

The assumption is you should be able to build on the challenge solution you have built in the previous week. My solution to the Reference Box challenge is here. I adopted (and blogged about) a table calc solution. However, the published solution used LoDs. Both methods achieved the desired result, but I decided when starting this challenge, that I would build this ‘extension’ using LoDs too, so anyone who uses the published solution as a starting point, gets help via this blog.

So if you used my previous blog to build the challenge, you’ll need to first create the LoD equivalent of the calculated fields we used (note I did not change the scatter plot viz to use these fields) :

PR- 25th Percentile LoD

{PERCENTILE({FIXED [State]: SUM([Profit])/SUM([Sales]) },0.25)}

PR-75th Percentile LoD

{PERCENTILE({FIXED [State]: SUM([Profit])/SUM([Sales]) },0.75)}

Sales- 25th Percentile LoD

{PERCENTILE({ FIXED [State]: SUM([Sales]) },0.25)}

Sales- 75th Percentile LoD

{PERCENTILE({ FIXED [State]: SUM([Sales]) },0.75)}

Note – It’s also worth reiterating at this point, that I will be referencing calculated fields/parameters/objects in this blog created as part of my initial challenge.

In order to build the bar chart, we need to categorise each State into a grouping; the selected/highlighted state, the states in the reference box, all other states.

To identify the states in the reference box, we need to use FIXED LoDs to get the value of the Sales and Profit Ratio at the State level.

Sales by State

{FIXED [State]: SUM([Sales])}

PR by State

{FIXED [State]: SUM([Profit])/SUM([Sales])}

We can then use these fields along with the LoDs further above to determine whether a State is in the reference box

In Reference Box?

[PR by State]>=[PR-25th Percentile LOD] AND
[PR by State]<= [PR-75th Percentile LOD] AND [Sales by State]>=[Sales – 25th Percentile LOD] AND
[Sales by State]<= [Sales – 75th Percentile LOD]

This simply returns a boolean.

Now we can categorise each State

State to Display

IF [Selected State] THEN [State]
ELSEIF [In Reference Box?] THEN [State]
ELSE ‘Other (median)’
END

And with the above, we can then define the measures we want to show

Sales to Display

{FIXED [State To Display]: MEDIAN([Sales by State])}

PR to Display

{FIXED [State To Display]: MEDIAN([PR by State])}

And with these fields we can now build the bar chart.

  • Add Selected State to Rows and drag the dimension value, so True is listed before False.
  • Add In Reference Box? to Rows, and again drag so the True is listed before False.
  • Add State to Display to Rows
  • Add Sales to Display to Columns and Sort descending
  • Add PR to Display to Columns
  • On the All Marks Card add Selected State to Colour
  • Then add In Reference Box? to the Detail shelf. Then click on the … icon to the left of the In Reference Box? pill on the marks card, and change to the Colour icon. This should result in 2 fields on the Colour shelf.
  • Adjust the colours accordingly.
  • Add Sales to Display and PR to Display to the Tooltip shelf and adjust.
  • Change the titles of the axis
  • Remove row banding
  • Uncheck Show Header against Selected State and In Reference Band?
  • Hide field labels for Rows against the State to Display column heading

Add this sheet onto the dashboard and you’re done 🙂

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a reference box?

Donna Coles1 Comment

Erica Hughes set her first #WOW challenge this week, which is all about reference lines. The intention by the #WOW crew is to use this initial challenge as the basis for the next few weeks, so it’s going to be interesting to see how that works out.

But back to this week. We’re building a basic Sales vs Profit Ratio scatter plot by State, but then need various calculated fields to plot the reference lines and the reference box.

  • Building the basic chart
  • Adding the dotted reference lines
  • Adding the reference box
  • Making the y-axis symmetrical
  • Highlighting the selected state

Building the basic chart

First up we need to create our favourite calculated field

Profit Ratio

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

format to percentage with 0 dp.

Create the basic scatter plot as below

Remove all gridlines, zero lines and axis rulers and tick marks.

Adding the dotted reference lines

These lines are the median values for the Profit Ratio and Sales.

Sales – Median

WINDOW_MEDIAN(SUM([Sales]))

format to Custom Currency with 1 dp, $ prefix and display units to Thousands (K).

PR- Median

WINDOW_MEDIAN([Profit Ratio])

format to percentage with 0 dp.

Add both these fields to the Detail shelf, and adjust the table calculation settings of both fields so they are computing by State

Add a reference line to the Sales axis (I do this by right clicking on the axis > Add Reference Line, but you can also drag from the Analytics pane).

Set the reference line to be against the Entire Table, using the Sales – Median field, no label, and the tooltip set to custom as below. Set the line to be dotted.

Apply a similar reference line to the Profit Ratio axis, using the PR – Median value instead.

Adding the reference box

I have to admit, this bit took a bit of trial and error. I knew it was going to involve a combination of bands and lines and colouring above and below, but things didn’t always go to plan. It was this post by Jonathan Allenby that helped.

The block is basically bounded by the 25th & 75th percentile of the Sales and Profit Ratio values. So lets create them

Sales – 25th Percentile

WINDOW_PERCENTILE(SUM([Sales]),0.25)

Sales – 75th Percentile

WINDOW_PERCENTILE(SUM([Sales]),0.75)

Add these fields to the Detail shelf and set the table calculation on both to compute by State.

Add a reference line again to the Sales axis, and this time select Band that goes from the Sales – 25th Percentile to the Sales – 75th Percentile and fill based on the colour stated in the requirements.

Now add a reference line to the Profit Ratio axis. This time select Distribution.

Select the computation to be Percentiles and enter 25 as the value. Set Label, Tooltip and Line to None. Now this is the odd bit… set the Fill to the colour white, then check the Fill Below box, which then seems to set the Fill option to ‘No Fill’. Doing this seems to have the desired effect. Trying this in any other order, or manually selecting the Fill option to be No Fill, just doesn’t seem to work…

Now repeat the process adding another reference line on the Profit Ratio axis, and this time create a distribution for the 75th percentile. This time, after setting the Fill to white, check the Fill Above checkbox. Weirdly this time, the fill on my laptop got set the Grey Light, although it was correctly displaying as white on the screen. I manually changed it to ‘No Fill’.

It feels like there’s something a bit ‘buggy’ with all this, which might explain while my initial attempts were failing. I’ll be interested in knowing if you see this behaviour too (I created on v 2021.4.2).

Making the y-axis symmetrical

This was a ‘bonus’ feature, but is again achieved with a reference line. What we’re looking for here is to understand what is the maximum absolute value so we can determine where to place a hidden reference line – ie if the maximum profit ratio is 20% and the minimum is -30%, the maximum absolute value is 30%, and we’d need a reference line +30%, so the axis extends from -30% to +30%. Conversely if the maximum profit ratio is 40% and the minimum is -25%, the maximum absolute value is 40% and we need a reference line at -40% for symmetry.

I’ve encapsulated all this logic within one field below

Ref Line – Profit Ratio Symmetry

IF MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) = WINDOW_MAX([Profit Ratio]) THEN -1*WINDOW_MAX([Profit Ratio]) ELSE -1*WINDOW_MIN([Profit Ratio])
END

The statement MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) is returning the maximum of the max and min values (ie MAX(20, ABS(-30)) = MAX(20,30) = 30, MAX(40, ABS(-25)) = MAX(40,25) = 40.

Add this onto the Detail shelf and add another reference line to the Profit Ratio axis, setting it to use the Minimum of the Ref Line – Profit Ratio Symmetry field

Test how the field works, by selecting a few of the State marks towards the top and exclude; the axis should change, but still be symmetrical.

Highlighting the selected state

For this we need a parameter

pSelectedState

string parameter defaulted to Virginia

Then we need a field to identify which state has been captured

Selected State

[State]=[pSelectedState]

which returns a boolean. Change the mark type to Circle and add Selected State to the Colour shelf, and adjust accordingly (add a border to the circle too via the Colour shelf). Also add Selected State to the Size shelf and again tweak.

Once the sheet has been added to the dashboard, create a dashboard parameter action that passes the State field into the pSelectedState parameter on Select of the chart.

The final step to add is to stop all the other marks from ‘fading’ out when a State mark is selected. This is achieved by creating the following calculated fields

True

True

False

False

Add both of these to the Detail shelf of the scatter chart.

On the dashboard, create a dashboard filter action as below, which passes selected fields setting True = False, which can never be true and prevents the mark from being highlighted.

And with that, you should have the desired dashboard. I’m interested to see if this matches Erica’s solution (it’s likely I’ll start with the provided workbook next week, rather than mine, just in case there are some discrepancies – eg I’ve used a lot of table calcs… LoDs may have been possible…)

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

How have successful products performed?

Donna ColesLeave a comment

Luke Stanke provided us with this week’s #WOW2022 challenge, to test our LoD knowledge (amongst other things).

From interacting with Luke’s published solution, I figured I needed to start by working out some core calculated fields, before I even attempted to build the viz.

Defining the calculations

Firstly, we just need to determine how many distinct products (ie Product Names) in total were ordered per Category and Sub-Category. As the viz we’re building is already at the required level of detail, the calculation we need is simple

Count Products

COUNTD([Product Name])

That’s the easy bit :-)…. we then need to determine how many products are profitable (for the relevant Category & Sub-Category) every year, in each of the 4 years. This is a bit more complex, and requires several steps (at least it did for me).

We need to know what the Profit is for each Product Name in each year. We can use an LoD for this.

Profit per Product & Year

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

Popping this into a table view so we can check what’s going on…

We now need an indicator to know whether each value is profitable (ie +ve) or not. I want to return a 1 if it is and 0/null if not, and then I want to be able to ‘sum up’ all the 1’s, so I can conclude if the total is 4, the product is profitable every year. I need another LoD for this

Is Profitable per Year?

{FIXED YEAR([Order Date]), [Product Name]: SUM(INT([Profit per Product & Year]>0))}

[Profit per Product & Year]>0 returns a true or false, and so when true and wrapped within an INT, will return 1. When this field is added to the above view, it is further aggregated by SUM.

At the Product Name & Year level, it just returns the 1’s, 0’s or <nothing> as expected

but when we remove Year from the table (which is necessary for the viz we’ll be building), this field aggregates….

…and scrolling down we’ll find some rows where the value is 4, which means the Product Name has been profitable for every year.

Using this information, we can then create a Set of these products – Right click Product Name > create > set

Profitable Products

contains the set of Product Names where SUM(Is Profitable per Year?) = 4

Now we can identify the profitable products, we need to count how many there are

Count Successful Products

COUNTD(IF ([Profitable Products]) THEN [Product Name] END)

If the Product Name is in the Profitable Products set, then capture the Product Name, and count the distinct number of them.

And now we can compute the percentage of successful products

Pct. Successful Products

ZN([Count Successful Products]/[Count Products])

ZN means I’ll get a 0 for those cases when there aren’t any successful products. Format this to % with 0 dp.

Let’s see all these values in a different table now to verify we’re getting what we expect :

Great! So this has given us the data we need to build the bar chart part of the viz, but what about the barbell? This is based on profit ratio and all we actually need for this is the standard profit ratio calculation

Profit Ratio

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

formatted to % with 1 dp.

Building the viz

On a new sheet create a basic bar chart with Category and Sub-Category on Rows and Pct. Successful Products on Columns. Sort descending, label the bars and colour accordingly.

Now add Profit Ratio to Columns, and on the Profit Ratio marks card only..

  • change mark type to circle
  • remove labels
  • add Profitable Products set to Colour and adjust
  • edit the alias of the values in the colour legend (right click > edit alias)

To create the bar between the circles, add another instance of Profit Ratio to Columns, and on this marks card…

  • change mark type to line
  • remove labels
  • add Profitable Products to Path
  • change colour to grey

Now right click on the 2nd instance of the Profit Ratio pill in the Columns shelf and select Dual Axis.

(note – if you lose your bars at this point, change the mark type of the Pct. Successful Products mark cards to bar).

Right click on the Profit Ratio axis at the top and synchronise axis, then right click again and move marks to back, then finally, right click again and uncheck show header.

And that’s the core of the viz really. It needs some final formatting to remove column headers, column gridlines and to add row banding. I also chose to make the tooltips more relevant, so in my solution there are some additional fields on the marks cards to provide the relevant details and commentary.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you visualise percent difference from a selected date?

Donna ColesLeave a comment

Lorna Brown returned this week with her first challenge of 2022 which was aimed at practising tableau calculations, although when I completed the build, I realised I actually didn’t use any – oops! I’d ended up using an LoD based solution instead. As is often the case in Tableau, there can be multiple ways to solve a problem, so I created another solution involving table calcs. As I blog I will endeavour to provide both sets of calculations used and make it obvious when I do so. Hopefully, it’ll all make sense 🙂

Building the calculated fields

After connecting to the data, the first step I did was to create a parameter to store the ‘selected date’. I hard coded/defaulted this to 1st Nov 2020.

pSelectedDate

Date parameter defaulted to 01 Nov 2020

Show this on the worksheet, so you can test what happens when you manually change it later.

As I typically do, I’m going to work in a tabular format to sense check all the calculations we’re going to need before I start visualising the information graphically.

To start add Company then Date (set to exact date & discrete) to Rows then add Close to Text and change the aggregation to AVG.

I now want to work out what the value of the average Close is on the selected date (in this case 01 Nov 2020) for each company, and I want this value to be displayed against every row associated to that company.

Close on Selected Date per Company

LOD Option

{FIXED [Company]: AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END)}

Table Calc Option

WINDOW_MAX(AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END))

Drag this field onto the table. If you’re using the LoD option, change the aggregation to AVG (although as we’re working with the data at the lowest granularity, SUM gives the same value, it just makes ‘more sense’ to me). If you’re using the Table Calc option, change the table calculation so that it is computing by Date only. In the below screen shot, I have included both versions of the calculation which match each other. Scrolling down, you should be able to verify that the value displayed in the new field(s) for all rows for a specific company, matches the value in the first column for the selected date. Test by entering different dates in the parameter (though make sure you’re just entering 1st of month each time, as that is what our Date field contains).

Now that for each row we have an average Close value and the average Close value on a specific date, we can work out the percentage difference for each row.

% Diff from Selected Date

LOD option

(AVG([Close])-AVG([Close on Selected Date per Company]))/AVG([Close on Selected Date per Company])

Table Calc option

(AVG([Close])-[Close on Selected Date per Company])/[Close on Selected Date per Company]

There is a slight difference in the syntax as the table calc option is referring to a field that is a table calculation and which is already aggregated, so there’s no need to aggregate again (ie wrap in an AVG…. Tableau will error if you try to do this).

Format this to a Percentage with 0 dp.

Once again I am choosing to show both versions on the screenshot below. Again if you’re using the table calc version, you need to verify the calculation is set to compute by Date only.

So now we have the core fields we need to start building the viz.

I am now just going to refer to the calculated field names I’ve mentioned above and not differentiate between LoD or table calc version. The only thing to remember is when adding the table calc fields, set the compute by, and if you’re using the LoDs, set the aggregation to AVG (although SUM will work too due to the level of aggregation we’re at).

Building the viz

On a new sheet add Date to Columns and set to the continuous Month level or exact date (either will work in this instance, since we only have 1 date per month in the data set). Add % Diff from Selected Date to Rows. Add Company to Colour and adjust to suit (use the hue circle colour palette).

Add Company to the Label shelf and also add % Diff from Selected Date to the Label shelf.

Adjust the Label settings, so the labels only display at the end of the line.

To make the text match the mark colour, click Label >then expand the Font dialog by clicking the arrow to the right, and select Match Mark Colour.

To format the % value displayed, right-click on the % Diff from Selected Date pill on the Text shelf, and select format. In the formatting pane that appears on the left hand side, change the number formatting to custom â–²0.0%;â–¼0.0% ( I use this site to get the shapes I need – just copy and paste)

Now rearrange the text on the label so that company is displayed first.

To format the Date field within the tooltip and the axis, right click on the Date pill in Columns and select format. On the Format Date pane on the left hand side, set the Dates value on the Pane tab, in the Default section, to custom : mmm yy. Then click on the Axis tab and set the Dates value in the Scale section to the same custom mmm yy value.

The Tooltip

We need quite a few fields on the Tooltip to get the information required.

Add Close and Close on Selected Date per Company to Tooltip. We also need a couple more calculated fields

TOOLTIP – Selected Date

[pSelectedDate]

format this field to custom mmm yy

TOOLTIP – % Diff

[% Diff from Selected Date]

Format this to % with 0 dp.

Add both these to the Tooltip too, then adjust the wording on the tooltip as required.

The Reference Line

Add pSelectedDate to the Detail shelf. Then right click the date axis and Add Reference Line. Adjust the settings so there is a Line for the Entire Table, which references the pSelectedDate value and has a custom Label of Reference Date: <Value>. Set the tooltip to None and the Line to be dotted

Format the pSelectedDate value displayed, by right clicking on the pill on the Detail shelf and set the Dates format on the Pane tab to mmm yy.

The right click on the Reference Date: Nov 20 label itself and select Format, and change the alignment to the top.

Finally, remove the axes titles, remove the gridlines and all axes rulers and change the worksheet background colour. Again, format the reference line label, so there is 0 transparency.

Test the visualisation by manually changing the date of the parameter to the 1st of another month and seeing how the viz updates.

Building the interactivity

Add the sheet to a dashboard, then create a parameter action, the runs on select of a mark on your sheet. It should change (target) the pSelectedDate parameter passing in the Date field

The only problem with this, is that when selecting a mark, the line relating to the mark is highlighted while the others ‘fade’ into the background. To prevent this from happening :

  • Create a new calculated field called True which contains the value True
  • Create a new calculated field called False which contains the value False
  • Add both these fields to the Detail shelf on the line chart.
  • Add a Dashboard Filter action, which runs on select, and has the sheet on the dashboard as it’s source, and the sheet in the workbook as it’s target. Set to show all values when selection is cleared, and filter by selected fields, setting True = False.
And with that, you should have a functioning solution – there just may be a few further formatting tweaks to apply.

My published vizzes are here

Happy vizzin’! Stay Safe!

Donna

Can you build a weekly year-over-year line chart?

Donna Coles2 Comments

Week 2 of #WOW2022 was Kyle Yetter’s first challenge as official WOW coach. On first glance when it was posted on Twitter, I thought it didn’t look too bad… I figured they’d be some ‘baselining’ of dates that I’d need to do to get the axis to display.

However, it was a little trickier than I first anticipated, mainly in trying to ensure I got the right values to match with Kyle’s posted solution (which at one point seemed to change while I was building). I’ve also realised since clicking on the link to the challenge again, that it was tagged as an LoD challenge, although there was nothing specific in the requirements indicating this was a requirement. I don’t think I used any LoDs…

Anyway onto the build, and I’m going to start by getting the dates all sorted, as this I found was the trickiest part.

Firstly connect to the data, then verify that the date properties of the data source are set to start the week on a Monday (right click data source > Date Properties

Build a basic view that displays Sales by the week of Order Date and the year of Order Date. Exclude 2018 since we’re only focussing on up to the last 2 years of data.

Examining this data compared to the solution, the first points of each line relate to the data shown against the 7 Jan 2019, 6 Jan 2020 and 4 Jan 2021. Ie the first point for each line is the first Monday in the year.

For simplicity, to make some of the calculations easier to read , I’m going to store the start date of the order date week in a field.

Order Date Week Start

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

This means I can easily work out the last day of the week

Order Date Week End

DATE(DATEADD(‘day’,6,[Order Date Week Start]))

Add these fields as discrete exact dates (blue pills) onto the table and remove the existing WEEK(Order Date) field since this is the same as Order Date Week Start

When we examine the end points of each line in the solution, the points relate to the data shown against the week starting 30 Dec 2019 to 5 Jan 2020, 28 Dec 2020 to 3 Jan 2021 and 22 Nov to 28 Nov 2021. For the first 2 points, we can see the data is ‘spread’ across 2 years (ie 2 columns), which means when categorising the data ‘by year’, using the Year associated to the Order Date itself isn’t going to work. We need something else

Year Group

YEAR([Order Date Week Start])

Move this field into the Dimensions pane (above the line), then add to the table, replacing the existing YEAR(Order Date) field. 2018 will appear due to the very first row of data, but don’t worry about this for now.

If you scroll down to the week starting 30 Dec 2019, all the data is now aggregated in a single Year Group.

So things are starting to take shape. We can now work on how to filter the data.

The requirements indicate we should imagine ‘today’ is 1st Dec 2021, so we’ll use a parameter to hold this value.

Today

Date parameter defaulted to 1 Dec 2021

We only want to show data for the last 2 years and for complete weeks up to today

Core Data to Include

[Order Date Week End] < [Today] AND YEAR([Order Date Week Start]) >= YEAR([Today])-2

Remove the existing filter and add this field instead, set to True. You should now have just 3 columns and the data starting and ending at the right points.

The next area of focus is to think about how the data is going to be presented – the lines are all plotted against a single continuous (green) date axis, so we need to ‘baseline’ the dates, that is adjust the dates so they are all on the same year.

Date To Plot

MAKEDATE(2021, MONTH([Order Date Week Start]), DAY([Order Date Week Start]))

this is basically setting the week start dates to the equivalent date in 2021.

In the table we’ve been building, add Date To Plot to Rows and set to the week level and be discrete (blue). Remove the Order Date Week Start pill and move the Order Date Week End to the Tooltip as this is where this pill will be relevant in the final viz.

We’re starting to now see how the data comes together, but we’ve still got some steps to go.

I’m going to adjust the Year Group, so we can present the Current, Previous, Last 2 Yrs labels. Change as follows

Year Group

YEAR([Order Date Week Start]) – YEAR([Today])

This returns values -2, -1, 0 which means the values will be consistent even if the ‘Today’ value changes. The values can then be aliased (right click Year Group > Aliases

Next focus is on the % difference in sales. Add a Percent Difference quick table calculation to the existing Sales pill. The vales will change to those we can see when hovering over the points in the solution.

Edit the table calculation and modify to explicitly compute by Year Group, which is important to understand as, when we build the viz, whether the data is going across or down may change, so ‘fixing’ like this ensures we retain the values we know are correct.

In order to manage the custom colour formatting in the tooltip, we’re going to ‘bake’ this field as a calculated field. Press CTRL, then click and drag the pill into the data pane and name the field accordingly. If you examine the field, it’ll probably look quite complex

% Sales Diff

(ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

I’m going to start building the viz now, and then we’ll add the final calcs needed for the formatting later.

On a new sheet

  • Add Core Data to Include to Filter and set to True
  • Add Date to Plot to Columns and set to a continuous week level (green pill)
  • Add Sales to Rows
  • Add Year Group to Colour and adjust accordingly
  • Reorder the values in the Year Group colour legend, so the ‘current’ line in the chart is displayed on the top, and the ‘2 yrs ago’ line is at the bottom
  • Format the WEEK(Date To Plot) field to be a custom date of dd mmm (ie 10 Jan)
  • Format the Sales axis to be $ with 0 dp
  • Add Order Date Week End to the Tooltip shelf and format the field to custom date format of mmm dd (ie Jan 10).

Now we need a couple of additional calcs to help format the % sales difference displayed on the tooltip.

% Sales Diff +ve

IF [% Sales Diff] >= 0 THEN [% Sales Diff] END

% Sales Diff -ve

IF [% Sales Diff] < 0 THEN [% Sales Diff] END

Format both these fields with a custom number format as â–²0.0%;â–¼0.0%

Add both these fields onto the Tooltip and adjust the table calculation settings of each to compute using by Year Group only

The 2 yrs ago line has no % Sales Diff value, and also no label, so we need a field to help this too

TOOLTIP: Label YoY%

IF NOT(ISNULL([% Sales Diff])) THEN ‘YoY%:’ END

This means the text ‘YoY%’ will only display if there is a % Sales Diff value.

Add this field onto the Tooltip too, and again adjust the table calculation settings.

Now format the text and tooltip as below, setting the two % Sales Diff pills to be side by side and colouring accordingly.

Hopefully, this means you now have a completed viz

My published viz is here. And nope… no LODs used 🙂 There are a few more calculated fields than what Kyle mentioned, but I could have condensed these by not having so many ‘building blocks’, but this may have made it harder to read.

I’m now off to check out Kyle’s solution and see whether I really over complicated anything…

Happy vizzin’!

Donna

Let’s analyse wildlife strikes

Donna ColesLeave a comment

Sean Miller began the start of #WOW2022 with this challenge where the focus was on layout, interactivity and maps.

  • Building the map
  • Building the bar
  • Building the area chart
  • The Unknown indicator
  • Adding interactivity

Building the map

I’m starting with this as in order to build the map, I found after a bit of trial and error I needed to build a data model which related the wildlife strike data source provided by Sean with the spatial file data source provided via the link to the community post. I downloaded all the files, but found the link to the us_ak_hi_territories_shift_conformal_faux_WM.hyper.zip was the file I needed once unzipped (the middle file to download from Sarah’s post on 19 July 2020 (see below)

Once I unzipped the downloaded file I copied the us_ak_hi_territories_shift_conformal_faux_WM.hyper file to my usual data sources repository on my laptop.

I then connected to Tableau and built a data model by first connecting to the wildlife strikes csv file, then adding a relationship to us_ak_hi_territories_shift_conformal_faux_WM on State Name = Name

To make things clearer, I created a fields to store the number of incidents

Wildlife Incidents

COUNT([2022_01_05_WW01_FAA Wildlife strikes (1990-2021).csv])

This is simply referencing the ‘count’ field that is automatically generated that is related to the wildlife strike data source.

I then built the map by

  • add Geometry and Name to Detail
  • add Wildlife Incidents to Colour

This should have created the below

Remove all the background imagery via the Map > Map Layers menu – uncheck all the options from the left hand pane.

Add Name to the Filter shelf and exclude American Samoa, Guam, Northen Marianas, Puerto Rico and the Virgin Islands. This will remove the cluster of shapes to the right (I’m not sure if this is the expected method or not..).

Change the colour palette to use the red-gold colour range.

Finally amend the Tooltip accordingly and also remove the row and column dividers.

Building the bar

The bar chart displays the top 10 incidents by species type, with the rest all grouped under ‘other’, and displayed at the bottom. We need to create a set for this. Right click on Species Type and Create > Set. Create a set based on the top 10 of the count of the wildlife incidents data source.

Species Type Set

We then need a field to display the info in the bar

Species Type to Display

IF [Species Type Set] THEN [Species Type] ELSE ‘Other’ END

ie if the Species Type is in the Species Type Set then display the Species Type, otherwise display Other.

Add Species Type Set and Species Type To Display to Rows and Wildlife Incidents to Columns and sort descending (just click the sort descending button in the toolbar)

Add Species Type Set to the Colour shelf and adjust accordingly. Remove the column and row dividers and the row gridlines. Adjust the Tooltip.

The final step we need is to make the title dynamic and display a state name if filtered.

Firstly we will need a parameter to capture the selected state

pSelectedState

A string parameter defaulted to <empty string>

We will use a parameter action to populate this parameter later. We need an additional field to use in the chart title

Title: Selected State

IF [pSelectedState] <> ” THEN ‘in ‘ + [pSelectedState] ELSE ” END

Add this field onto the Detail shelf, then adjust the chart title

Building the area chart

Add Incident Year to Columns and Wildlife Incidents to Rows and change to mark type = Area. Adjust colour accordingly, and remove the column gridlines. Adjust the Tooltip.

Again the chart title needs to be dynamic based on state name and the species type selected, so we’ll need another parameter

pSelectedSpecies

string parameter defaulted to <empty string>

Add Title: Selected State to Detail and adjust the title as below

The Unknown indicator

On a new sheet double click in the space below the marks card to create a ‘type in’ pill

Enter the text ‘Unknown Location’ (including the single quotes) and the add this pill onto the Text shelf.

Change the mark type to square and adjust the Size to the maximum.

Add Wildlife Incidents to the Tooltip shelf. Then add Name to the Filter shelf and filter to only show the Null values. Adjust the colour and the Tooltip.

Adding interactivity

Create a dashboard and use layout containers to position the charts in the relevant places. The colour legend and the ‘unknown’ indicator will need to be ‘floated’ into position.

We’re going to need 4 dashboard actions; one to set the selected State, one to set the selected Species Type, one to filter the bar and area chart based on the the selected state, and one to filter the map and area chart based on the selected species.

Select Species

this is a parameter action to set the pSelectedSpecies paramater on selection of the bar chart, using the value in the Species Type To Display. The parameter should be reset to <empty string> when unclicked.

Select State

similar to above, but runs on selection of the map chart, and passes the Name field into the pSelectedState parameter.

Filter by State

This is a filter action that runs on selection of a state in the map chart. It affects all other charts on the dashboard except the unknown sheet. It should only filter on the Name field and not All fields.

Filter by Species

Another filter action, that runs on selection of the bar chart. This one does impact all the other charts on the dashboard, but again only filters based on the Species Type to Display field rather than all fields.

Hopefully, with all this, you should have a working solution. My published viz is here.

Happy vizzin’!

Donna

Can you build a table with one measure?

Donna Coles1 Comment

For the final challenge of 2021, Luke set this challenge, to present a table containing both actual Sales values and % of Total Sales values using a single measure only.

The basic principal of this is explained within this Tableau KB article, although to get the exact display, will require a bit extra.

Building the table

Add Category & Sub-Category to Rows and Order Date to Columns. Change the Order Date so it is just displaying the month only. Add Sales to Text. Add Grand Totals and all Sub-Totals (Analysis > Totals menu). Set the totals to display at the top (Analysis > Totals menu again).

What we’re looking to do is change all the Total values in each Category section to be a % of Total instead, but just display the actual Sales value for all the other cells.

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE SUM(Sales)/TOTAL(SUM(Sales))
END

Add this onto the Text shelf too and edit the table calculation so both the Custom Measure and Size nested calculations are computing by the Category field only. This should give you 0s in the Total fields for the Custom Measure value.

Remove Sales from the Text shelf, as we don’t need this now.

Format the Custom Measure

Click on the Custom Measure pill and select Format. The Format dialog should present on the left hand pane.

On the pane tab, set the default format to be $ 0dp, set the Totals format to be % 0 dp and set the Grand Totals format to be $ 0dp

Change the Total Labels

Right click on one one of the Categories (eg Furniture) and Format. On the left hand pane, change the Grand Total label to be Total.

The right click and format one of the Sub-Categories and change the Totals label to be % of Total.

Colouring the cells

Add Custom Measure to Colour (ensure the table calculation is set as described above) and change the mark type to square.

Edit the colour legend to use the green-blue-white diverging palette and set to include totals and full colour range. Set the centre of the range to be 0.

This doesn’t quite give us what we need, as the % of Totals aren’t coloured as we need.

The values for these although displaying as % are essentially the values between 0 and 1. But the ‘green’ will only display for values less than 0. So we can amend the Custom Measure to deal with this

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE -SUM(Sales)/TOTAL(SUM(Sales))
END

We make the % of total to display as -ve, which now gives the colouring we need,

Finally, we need to ensure the -ve doesn’t display so we just need to re-custom format the Totals field of the Custom Measure to be 0%;0%

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Let’s visualise #WorkoutWednesday submissions

Donna Coles1 Comment

Sean Miller decided to use the data gathered by the #WorkoutWednesday team in this week’s challenge, to visualise how people use the submission tracker. I try to fill it in immediately after publishing and tweeting my solution, which is usually within the same week as when the challenge is set, unless I’m on holiday. Sometimes I do forget and as I know I’ve completed every challenge, I will fill it in if I see gaps in the tracker. However, I do think there’s something a bit awry with my submission data as there’s a few holes that don’t seem to tally properly with the weeks in the month…. there’s probably a typo or duplication in my data (if any of the team happen to read this, could you have a look.. or let me see my data to see if I can find the problem 😉 )

Anyway onto the challenge – we’re going to build a heat map and 2 bar charts and then add some interactivity between the two – nothing majorly taxing this week, so hopefully this blog won’t take too long to write… I’m prepping for Christmas so have lots to do 🙂

Building the heat map

The heat map displays the week number in the year across the top, and year and day of week down the side. We need to create some calculated fields to extract some of these values from the Timestamp date field provided (note if your Timestamp field doesn’t import automatically as a date, then right click > Change Data Type > Date to convert it).

Week Submitted

DATEPART(‘week’,[Timestamp])

Format this to custom format of 00, so you display 01 rather than 1. Drag the pill into the top half of the data pane, so it’s stored as a dimension.

Weekday Submitted

LEFT(DATENAME(‘weekday’,[Timestamp]),3)

Add Week Submitted to Columns and then YEAR(Timestamp) and Weekday Submitted to Rows.

To get the numbers and display to match Sean, you will need to ensure your week starts on a Sunday. If you’re in the UK like me, you may have it set to Monday. To change this right-click on the data source > Date Properties > Week start = Sunday.

To colour the cells, we need

Submissions

COUNT([2021-12-22-WW51-WOW_Challenge_Tracker.txt])

where the field referenced is automatically generated field that is created (ie what used to be Number of Records).

And then we need to calculate the Rank Percentile, which isn’t as scary as it sounds – there’s a handy function…

Percentile of Submissions

RANK_PERCENTILE([Submissions])

Format to a percentage with 1 dp.

Add this onto the Colour shelf, and adjust the table calculation so it is computing by all the fields.

Edit the colour range to use the one specified, and adjust the settings so it only uses 4 colours, and ranges from 0 to 1

Add Software to the Filter shelf and select Tableau

This is the core of the chart complete. Add column and row dividers, rotate the Year headings, narrow the columns and hide the row label headings.

Now we just need to sort the Tooltip. Add Timestamp to the Detail shelf, and change to the Day May 8, 2015 display. This will change the viz, but don’t panic. Re-edit the table calculation so Day of Timestamp is also checked.

Then add Submissions to Tooltip and adjust the tooltip text as required.

Finally, also add Submissions to the Label shelf and edit so the value on shows on selection.

Building the bar charts

Firstly, amend the Software filter on the heatmap so it is set to apply to worksheets > all using this data source.

Next wee need to identify whether the challenge was submitted in the same wee as it was set

Same Week Submission

([Challenge Week]=[Week Submitted]) AND ([Challenge Year]=YEAR([Timestamp]))

This returns a boolean of true or false, but we can alias these values (right click field > Aliases) to give the displayed options

Now add Same Week Submssions to Columns, Submissions to Rows and Same week Submissions to Colour and adjust accordingly. Amend gridelines, borders etc to get the required display format

For the next bar chart we need to build the text for

Challenge Submitted

STR([Challenge Year])+’w’+STR([Challenge Week])

Add this to Rows and Submissions to Columns and add Same Week Submission to Colour. Again adjust formatting accordingly.

Adding the Interactivity

On a dashboard, add a Vertical container. Drop the Heat map into it. Then underneath, still within the Vertical container, add a Horizontal container. Add the two bar charts side by side. You may have other objects, but part of your layout hierarchy should look like

Add a dashboard filter action to the heatmap chart that on select affects the two bar charts, but when unselecting, excludes all values.

As you click on the heatmap and then unclick, the bar charts should disappear and the heat map should fill out the space.

Finalise the dashboard adding a title, supplementary text, the software filter and colour legend.

My published viz is here.

Happy vizzin’!

Donna