Tag: week 20

Let’s practice using the multi-fact relationship model

Donna ColesLeave a comment

Yoshi chose to challenge us this week to try using the multi-fact relationship model, something I hadn’t used before so I was keen to have a go.

I have to admit, I did ultimately find the challenge a bit tricky… I did have to look at the hint in the challenge to see how Yoshi had folded in the additional month data source, and then when building the vizzes, I was getting slightly different numbers on occasion. I wasn’t sure whether this was due to differences in how I’d applied the actual relationship fields between the tables in the model, but can’t actually get to Yoshi’s data model in the solution to tell. I used the information on the help article to determine the linking fields, so I think it’s right…

I also struggled with the final requirement – to always show all the libraries in the display, even when there were no copies of the selected book. As soon as I restricted the display to just show the data for the selected book, the libraries which didn’t stock the book disappeared, which with relationships is what I expected as they act like ‘inner joins’. Even looking at Yoshi’s solution, I can’t see what magic trick has been applied, unless there’s something in the model I haven’t applied..

So I’ll explain what I did, and if you can figure out where I may have gone wrong, or if you concur with my set up, then please let me know 🙂

Modelling the data

Download the 3 data sources from Yoshi’s link.

Connect to the Bookshop excel file. Drag Checkouts onto the canvas first – this is one of the base tables.

Drag Book onto the canvas and verify the tables are related by the Book ID field from each table.

Drag Author onto the canvas after Book and verify the tables are related on the Auth ID fields.

Drag Award onto the canvas after Book and verify the tables are related on the Title fields.

Drag Edition onto the canvas after Book and verify the tables are related on Book ID

Drag Info onto the canvas after Book. This time you’ll need to join Book ID from Book to a relationship calculation from Info of [BookID1]+[BookID2]

Drag Ratings onto the canvas after Book and verify the tables are related on Book ID

Add a new connection to the BookshopLibraries excel file and then drag in Catalog after Edition and verify the tables are related on ISBN

Drag in Library Profile after Catalog and verify the tables are related on Library ID

Next from the Bookshop excel data source, add Sales Q1 onto the canvas and drop when the New Base Table option appears

Multi select the Sales Q2, Sales Q3 and Sales Q4 tables then drag onto the canvas and drop onto the Sales Q1 table when the Union option appears

Right click on the Sales Q1 unioned object and rename to Sales. Add a relationship from Sales to Edition verifying the tables are related on ISBN.

Add a new connection to the Months csv file, then add the Month.csv object onto the canvas so it is related to the Sales table. This time you’ll need to create a relationship calculation on Sales as DATEPART(‘month’, [Sale Date]) and join to Month

Finally also add a relationship from Checkouts to Month.csv, creating a relationship on Checkout Month to Month.

Now we have a model, we can start building the vizzes.

Building the scatter plot

To start, to verify I’m getting the data I expect, I’ll build out a table

Add BookID (Book) and Title to Rows. We need to indicate if the book has won an award.

Awarded?

COUNT([Award])>0

Add this to Rows.

We will be plotting the average number of checkouts per month against the average number of orders per month., so create

Avg Checkouts per Month

SUM([Number of Checkouts])/COUNTD([Checkout Month])

Then create

Avg Orders per Month

COUNT([Order ID])/COUNTD(MONTH([Sale Date]))

Add into the table. The number won’t be correct though, as we are displaying data from the Checkouts base table and the Sales base table without including any field from any tables that link them. To resolve this add BookID (Edition) onto Rows, and if you spot check some of the numbers against the scatter plot solution, they should match (or be very close). – figures for Portmeirion matched for me.

We also need to highlight which book is selected, so create a parameter

pSelectedBookID

string parameter defaulted to <emptystring>

Then create field

Is Selected Book

[BookID (Edition)] = [pSelectedBookID]

Show the parameter and enter the string PP866 (for Portmeirion). Add Is Selected Book to Rows to verify true is displayed against this row.

On a new sheet, add Avg Checkouts per Month to Columns and Average Orders per Month to Rows. Add Book ID (Edition) to Detail. Add Awarded to Shape and adjust shapes as required. Add Awarded to Size and adjust. Add Is Selected Book to Colour and adjust to suit. Make sure True is listed before False for the Colour legend to ensure the selected book mark is always ‘on top’. Hide the null indicator

Create a new field

Label: Selected Title

IF [Is Selected Book] THEN [Title] END

Add this to Label, adjust the font style, match mark colour and align top centre. Adjust the tooltip. Update the title and name the sheet Scatter or similar.

Building the Ratings Bar

Add Rating as a discrete dimension (blue disaggregated field) to Rows. Add Is Selected Book to Columns and add Ratings (Count) to Text. Exclude the Null column that appears (Filter for Is Selected Book). Add a percent of total quick table calculation to CNT(Ratings) and adjust to compute using Rating.

Duplicate the sheet. Move CNT(Ratings) to Columns and Is Selected Book to Colour. Adjust colours to suit. Unstack the marks (Analysis menu > stack marks off). Add Is Selected Book to Size. Adjust so that True is listed first on the size legend, so it is both narrower than false, and will also now be ‘in front’. Update the tooltip and hide the Rating label heading (right click > hide field labels for rows).

Create a new field

Selected Book Ratings

IF [Is Selected Book] THEN
{FIXED [Title]: COUNT([Ratings])}
END

and add to Detail. Then update the title of the sheet adding a reference to this field. Exclude the Null option that now appears (Rating is added to the Filter shelf). Name the sheet Ratings Bar or similar.

Build the trend line

We need to show the average number of books ordered and the average number of books checked out each month. For this we need

Avg Orders per Book

COUNTD([Order ID])/COUNTD([BookID (Edition)])

and

Avg Checkouts per Book

SUM([Number of Checkouts])/COUNTD([BookID (Edition)])

On a new sheet, add Month (from Months.csv) to Rows as a discrete dimension (blue pill) and add Avg Checkouts per Book and Avg Orders per Book into the table text. Then add Is Selected Book to Columns.

This is where some of my numbers don’t align with the values in Yoshi’s solution, but it’s not clear why… Exclude the Null columns (Is Selected Book added to Filter).

We’re going to want to display the month numbers as the month names, so create a new field to create a ‘date’ out of the number

Month Name

//convert to a date, then use month/formatting functions in display
MAKEDATE(2000, [Month],1)

On a new sheet add Month Name to Columns as a continuous pill at the month (May) level (green pill). Add Avg Orders per Book and Avg Checkouts per Book to the Rows and add Is Selected Book to the Colour shelf of the All marks card. Adjust colour if needed, and ensure True is listed first (on top).

Adjust the y-axis titles, and update tooltip if required. Remove the title from the x-axis. Format the x-axis so the scale uses abbreviated month names

Add Is Selected Book to Filter and exclude Null. Update the sheet title, Name the sheet Monthly Trend or similar.

Building the Library Bar

Add Library to Rows and Number of Copies to Columns. Add Is Selected Book to Filter and set to True. Show mark labels, and adjust to match mark colour. Hide the x-axis. Hide the Library row header. Apply row banding so every other row is shaded. Update the tooltip and sheet title. Name the sheet Library Bar or similar.

Creating the title sheet

On a new sheet add Is Selected Book to Filter and set to True. Then add Title, First Name, Last Name and Genre to Text. Set the mark type to shape and select a transparent shape (see here for info on how to set this up). Set the sheet to Entire View. Organise the text as required and align middle left. Stop the tooltip from showing. Name the sheet Title or similar.

Building the dashboard and interactivity

Use layout containers to position the objects onto the dashboard, and use padding to provide the relevant spacing between objects.

The Title, Ratings Bar, Monthly Trend and Library Bar sheets should all be in a single vertical container which has a dotted blue border surrounding it.

Create a dashboard parameter action

Set Book

on select of the Scatter sheet, set the pSelectedBookID parameter with the value from the BookID (Editiion) field. When the selection is cleared, retain the value.

The layout of my dashboard including the item hierarchy is below

My published viz is here.

Happy vizzin’!

Donna

Can you combine categorical & sequential colours in the same chart?

Donna ColesLeave a comment

For this week’s challenge, Kyle aimed to solve a problem that he discussed with a #TC24 attendee while waiting in line for a session. How to show solid (categorical) and graduating (sequential) colours within the same chart.

For this we’re going to create 2 sheets, one displaying Sales and one for Profit Ratio.

Data Preparation

As the requirements referenced the use of the Manufacturer field, I connected to the Superstore Sales.tds file I had stored locally, rather than the .xls file, as Manufacturer doesn’t exist in the xls file.

Once connected, I also added a data source filter where Sub-Category = Storage (right click on data source > edit data source filter).

Building the Sales bar chart

Add Manufacturer to Rows and Sales to Columns and sort descending. Format Sales to be $ with 0 dp, and show mark labels.

Create a new field

Colour – Sales Bracket

IF SUM([Sales]) > 15000 THEN ‘>$15k’
ELSEIF SUM(Sales) >= 4000 THEN ‘$4k-$15k’
ELSE ‘<$4k’
END

Then create another field

Colour – Sales Range

IF SUM([Sales])<4000 THEN SUM([Sales])*-1 END

By default, this will be a continuous field as it returns a numeric value. But when having multiple fields on the Colour shelf, which is what we’re going to do, the fields need to be discrete. So convert Colour – Sales Range to discrete (right click on field).

You also might be wondering why we’re multiplying the value by -1. This is to ensure the values when listed in the colour legend are sorted in the way we want. This will become clearer shortly.

Add Colour – Sales Range to the Detail shelf, then click on the icon to the left of the pill, and select the Colour icon, to add this pill to the Colour shelf along with the Colour – Sales Bracket field.

Re-order the pills so Colour – Sales Range is listed first.

Your viz will look something like this

To change the colours without having to go through each legend option individually, edit the colour legend and choose a sequential colour palette that suits. In my case I used a custom one I had installed, but you could just opt for the Red-Gold that should be installed by default.

Click the Assign Palette button, and Tableau will automatically assign the colours in the graduated sequence, which is why the ordering of the entries matters. The colour legend lists the entries in ascending order, and as we want to display the values in descending order, multiplying by -1 reverses the order for the colour legend.

Manually set colours for the NULL, >$15k and NULL, $4k-$15k options. Add a pale grey border around the marks (via the Colour shelf). Then adjust the tooltip, remove the Manufacturer column heading label, hide the axis and remove all gridlines, zero lines, axis rulers, row/column dividers, and update the title.

Note – I chose to re-order how the two colour pills were listed on the Colour shelf so that there was a more noticeable difference between the colour chosen for the $4k-$15k range vs the first entry for the <$4k range.

Building the Profit Ratio bar chart

For this you will need similar fields

Colour – PR Bracket

IF [Profit Ratio] > 0.15 THEN ‘>15%’
ELSEIF [Profit Ratio] >= 0 THEN ‘0-15%’
ELSE ‘<0%’
END

and

Colour – PR Range

IF [Profit Ratio]<0 THEN [Profit Ratio]*-1 END

and Profit Ratio should be formatted to % with 1 dp.

You then just need to go through similar steps to that described above.

Building the dashboard

I used a horizontal container to position the two sheets in, side-by side. I set the outer padding of each sheet to 0 and inner padding to 5 and set to fit entire view. Between the sheets I added a blank object which I set to have outer padding of 0. I set the background colour of this blank object to a mid grey, then set the width to be 2, which produces the thin divider line.

To enable the highlighting between the sheets ‘on click’ I simply selected all fields from the highlight menu button.

A relatively short blog this week, but a great concept that’s worth knowing. My published viz is here.

Happy vizzin’!

Donna

Can you build a heat map with bathymetry lines?

Donna Coles2 Comments

What??? That was the first thought that went through my mind when Luke set this challenge. Bathy..huh… ??? What’s that all about. Well read the challenge to find out more 🙂

The what?? was quickly followed by errr…..? The indicator on the challenge overview page said ‘hard’ and this was a Luke challenge after all, so certainly not for the faint hearted! I wasn’t sure how this was going to go, so just started with a basic tabular view and went from there. (hint – it might be worth reading the blog to the end before you start building.. it could save some time 😉 )

I quickly built a basic heat map. I added Order Date at the discrete (blue) month level to Columns and Sub-Category to Rows. I added Sales to Colour and changed the mark type to square then added Sales to Label.

We ultimately need the cumulative Percent of Total Sales per Sub-Category, to display as the label. Click on the Sales pill on the Label shelf and Add Table Calculation. From the dialog window, choose Running Total and verify the calculation is computing Table (across) (or amend and select Specific Dimensions and ensure Month Order Date is selected). Then check Add secondary calculation and in the secondary calc dialog, select Percent of Total, again ensuring Table (across).

If applied correctly, the values in the December column should all be 100%.

I wanted to ensure this calculation was stored so I could reuse, so while pressing shift key, I dragged the Sales pill from the label shelf into the data pane, and renamed the calculation

Cumulative % of Total

RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))

Format the field to be % with 0 dp

Editing the field should show the details above. Of course, you can just create the calculated field manually and type in the syntax. If you do that, then replace the Sales field on the label field, with this one. Centre align the text.

With this field, we can create the calculation Luke provides for the colouring

Colour

ROUND([Cumulative % of Total]*50, -1)

Replace the Sales field on the Colour shelf with this field, and adjust the colour to use the Blue sequential colour palette. You should now have the basic structure and colouring of the heat map.

Adjust the font style and alignment of the Sub-Category and Order Date Month label headings, and change the month labels to be abbreviated. Hide the Order Date column label and the Sub-Category title (right click hide field labels for columns/rows). Remove all gridlines, row/column dividers etc.

Looking good.. but what next.. how to get those divider lines…. I wasn’t too sure at this point, but I knew I had to identify the cells when a ‘change in colour’ happened both horizontally and vertically.

So the first thing I did was to duplicate the above sheet into a basic crosstab (right click sheet > duplicate as crosstab), and I removed Cumulative % of Total from the display, so I just had the values used for the colour.

I decided I wanted to flag each cell with a 1 or 0 depending on whether the next cell was different or not. I started horizontally. So for each row, and starting with January, I wanted to compare the colour value for Jan with the colour value for Feb. If they were the same, I wanted to record 0 against Jan. If they were different I wanted to record 1 against Jan. I used the following calculation

Horizontal – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 0.99 END

If the Colour value for the current cell (eg Jan) matches the Colour value of the next cell (eg Feb) OR, the current cell is the last month (ie Dec), then return 0 otherwise there is a mismatch so return 0.99. Originally I used 1, but later found I had to adjust the calc to make the line show as I wanted.

Add this field to the tabular display and verify the table calculations used within the field are running Table (across).

You can see that for Accessories, the Colour value for Feb is not equal to the Colour value for Mar, and so the Horizontal – Next Value Diff value for Feb is 0.99. Whereas as the Colour value for Apr matches May, the Horizontal – Next Value Diff for Apr is 0.

I created a similar calculation to test the vertical settings

Vertical – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 1 END

This is essentially exactly the same calculation, but when added to the tabular view, the table calculation for the Vertical – Next Value Diff calc should be set to Table (down) instead

You can see that in Feb, the Colour value for Paper is equal to the Colour value for Phones, so the Vertical – Next Value Diff calc for Paper is 0. But in Mar, the Colour value for Storage is different from the Colour value for Supplies, and so the Vertical – Next Value Diff calc for Storage is 1.

Using these markers, I now want to ‘plot’ them on a viz.

So back to the heat map sheet we built above, we need some axis.

Double click into the Columns shelf and type MIN(1.0) and change the mark type to bar.

You can see a hole has appeared. To fix this, on the Analysis menu, select Infer Properties from Missing Values. The cell will populate.

Confession – I didn’t get this bit initially. I had the chart built with the lines in the correct places, but with a hole. I’d tried all sorts of combinations of ZN, and LOOKUP(expression,0) in calculations to try to make the number appear, but couldn’t get anything to work. I showed my colleague Sam Parsons, who mentioned the above setting. I have honestly never ever used it and was completely unaware of it’s existence. But it solved the problem, so massive hi-five to Sam 🙂

Double click into the Rows shelf and again type MIN(1.0). Having an axis on both rows and columns, means we can now fix the Size of the bar. Click on the Size shelf and select Fixed, width in axis units to 1 and alignment right.

Now we have both an x and y axis, we can add additional detail to get the white bar dividers displaying.

First, adjust the label so it is aligned middle centre.

Add Horizontal – Next Value Diff to Columns. Ensure the table calc settings are Table (across).

Remove both the Colour and the Cumulative % of Total fields from the marks card, and then add Horizontal – Next Value Diff as a discrete (blue) pill to Colour. Remove all gridlines, zero lines, axis ticks and row/column dividers. Change the Size to be Manual and reduce size. Make the chart dual axis and synchronise the axis.

You should have something similar to the above, assuming the colours for the Horizontal – Next Value Diff are distinguishable enough.

I adjusted the colours to set 0 to be a transparent colour (refer to this blog to learn how to add a transparent colour hex code into your custom colour palette, and then 0.99 to be white. Edit the x-axis and fix to be from 0 to 1. The white dividers between the relevant months should now be noticeable.

To set the dividers between the Sub-Category, I decided I just needed to use a constant 0 reference line. For this I needed

Vertical Ref Line

IF [Vertical – Next Value Diff] = 1 THEN 0 END

Add this to the Detail shelf on the All marks card, and verify the table calc setting for the Vertical – Next Value Diff calc is set to Table (down).

Right click on the y-axis and Add Reference Line. Set it per cell using the Vertical Ref Line field, with no label or tooltip displaying. Set the Line properties to be white, 100% opacity and a thick width.

Then edit the y-axis to also be fixed between 0 and 1. The lines separating the Sub-Category should now be prominent.

Then it’s just a case of tidying up – hide the axis, stop tooltips from displaying, and then add to the dashboard, setting as fit to entire view.

If need be you may want to tweak either the size of the bar on the Horizontal – Next Value Diff marks card, or change the thickness of the reference line to get similar sizes.

I have no idea when I’d ever need to use this type of display, but I enjoyed the puzzle and discovered something new which is what I love about using Tableau!

This version of my published viz is here.

BUT….

after writing all this up, and stepping away for a bit, I suddenly realised I’d over-complicated things! I didn’t need a dual axis after all. I could just use a reference line to show the dividers between the months, exactly like I did for the 0 constant reference line – doh! So I made some adjustments..

Amend

Horizontal – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 1 END

Then create

Horizontal Ref Line

IF [Horizontal – Next Value Diff] = 1 THEN 1 END

Then remove the Horizontal – Next Value Diff field from the Rows shelf, so there is no longer a dual axis. Add Horizontal Ref Line to Detail , and display the x-axis MIN(1.0) axis, and add a reference line per cell which references Horizontal Ref Line. Apply the same settings to the reference line as detailed above.

And this resulted in a much simpler looking viz

I know I could reduce the number of calcs used, but I like to have ‘building blocks’ to follow my thought process. I have published the simpler version within the same workbook on a separate tab, here.

Happy vizzin’!

Donna

Monthly Sales vs Targets – the #data22 Vegas Edition

Donna ColesLeave a comment

This week the whole #WOW2022 crew and myself were lucky enough to be able to attend #data22 – the Tableau Conference in Las Vegas. In the 10 years I’ve been involved in the Tableau Community, this was my very first US conference, and my first opportunity to meet some of the people I engage with on a weekly basis, in person. Meeting all those who have been involved in the WOW challenges over the years and my fellow regular participant, Rosario Gauna, was a big highlight for me.

Erica led the live #WOW2022 session at the conference on Thursday morning (not sure the organisers fully understood the title), with this challenge.

Erica walked through the challenge end-to-end in the session, but I attempted to build out my solution, just as I would at home, so let’s crack on.

Modelling the data

The viz compares actual sales vs target, and an additional data set was provided to store the target data. As a consequence this needs to be combined/joined/related with the actual sales data.

For this you need to download the two excel data sources provided in the challenge – SuperStore Sales and Superstore Category Monthly Sales Targets.

Connect to the Superstore Sales file and drag the Orders sheet into the canvas. The Add a new connection to the Superstore Category Monthly Sales Targets file and drag in the Sales Targets sheet to the right of the Orders object in the canvas.

This will try to create a relationship between the two objects, but can’t as it needs to be defined.

In the section at the bottom, relate the Category field from the Orders object to the Category field in the Sales Target object.

This creates a valid link between the two objects, but it’s not enough. We also need to relate on a date field. In the Sales Targets object there is a field Month which stores the date on the 1st of a month. So to relate the Orders data we need to

  • Click + to Add more fields
  • From the Select a field dropdown on the Orders side, select Create a Relationship calculation
  • In the calculation window type in DATE(DATETRUNC(‘month’, [Order Date])). This returns the 1st of the month related to each Order Date into a Date rather than Datetime datatype.
  • Select Month from the Sales Targets dropdown.

Building the bar in bar chart

You can build this type of chart using a dual axis chart, where both axis are set to use the bar mark type, but the sizes of the bars are different. However, if you go down this route you’ll struggle to get the labels right (as the labelling is the trickiest part of this challenge).

Instead for this challenge you need to build a combined axis chart, which is possible since both measures are represented by the same mark type, a bar.

First up, add Order Date to the Filter shelf and select to filter by the Year 2021.

Then add Order Date to Rows and set it to the discrete (blue pill) ‘month’ level (the ‘May’ option rather than the ‘May 2015’ option on the context menu). Add Sales to Columns and change the mark type to bar.

Then drag Sum Targets from the left hand pane onto the Sales axis, releasing the mouse when you see the ‘double column’ green icon appear.

This will result in a combined axis chart being created, and the fields Measure Names and Measure Values automatically added to the viz.

Move Measure Names from the Columns and place on Size. Additionally add another copy of Measure Names onto Colour and adjust accordingly.

On the Analysis menu, select Stack Marks -> Off so the bars both start from 0, rather than being placed on top of each other.

We now need to adjust the bar sizing. Edit the sizes via the Size legend on the right hand side, so the range between the sizes is less than that set.

We now need to work on colouring the Sales bars based on whether they met target or not. For this we first need to work out whether the Target is bigger or not.

Met Target?

SUM([Sales]) > SUM([Sum Targets])

This returns a boolean true/false result. We want this on the colour shelf in addition to the Measure Names field that is already there. If we just drag it onto colour, it will replace Measure Names. Instead, drag Met Target? onto the Detail shelf. Then click on the 3 dots immediately to the left of the Met Target? pill in the marks card, and select the Colour icon.

This has the effect of adding this as an additional colour field, so four options rather than two are now presented in the colour legend. Adjust the colours once again.

We’ve now got the core bar-in-bar chart. We can just add some final formatting to this section.

Format the month axis to set the dates to be abbreviated, and then rotate the labels.

Click the Order Date label at the top of the chart and Hide field labels for columns.

Edit the Value axis and rename the title to Sales ($).

Amend the number format of both the Sales and Sum Targets fields to be a number with 0dp and $ prefix. Add both fields to the Tooltip and adjust so that both values display when you hover over either the Sales bar or the Targets bar.

Labelling the bars

Labelling the bars to match Erica’s display is the trickiest part of this challenge. Bottom line – it shouldn’t be so tricksy but that’s just the way it is until Tableau see fit to fix it in the product.

Anyway, on examining Erica’s published solution before I started, I could see, by hovering my mouse over the viz, that there was a small mark highlighted above the bars that missed target. This provided a clue that there was a dual axis involved (hence the need for a combined axis to display the bars), with a mark plotted at some distance above the existing data…but where…?

Before working that out, I need to build a couple of fields

Difference

SUM([Sum Targets])- SUM([Sales])

which returns the difference between the Sales and Sum Targets for each month. But since I only want the values when the target hasn’t been met, I need

Missed Target Diff

IF NOT([Met Target?]) THEN [Difference] END

which is formatted to $ with 0dp.

I also needed some text to only display when the target was missed

Label Text Missed Target

IF NOT([Met Target?]) THEN ‘Target missed by’ END.

Now to figure out what measure to plot….

In Erica’s solution, she worked out an ‘uplift’ of the Sum Targets value to plot, but only when the target was missed. She did this using quite a complex looking nested LOD calculation (check out her solution for this).

After much deliberation, discussions, trial and error, I finally came up with a alternative that isn’t perfect, but is ‘good enough’ in my book.

Firstly, I needed another measure to plot

Target to plot

IF NOT([Met Target?]) THEN SUM([Sum Targets]) END

This plots the Targets value but only against the months where the target wasn’t month.

I then added this to the Columns shelf and I changed the mark type to circle on the Target to Plot marks card only.

I then made the chart dual axis and synchronised the axis.

Then, remove Measure Names from the Colour and Size shelf on the Target to Plot marks card, and also remove the Met Target? field from the Colour shelf too.

Adjust the Size of this mark to the smallest possible, and change the Opacity (via the Colour shelf) to 0%, so the circle mark becomes invisible except on hover.

Add Missed Target Diff to the Text shelf of the Target To Plot marks card. Edit the text in the label as below

I added a carriage return, formatted the text to 11pts and orangey/red and added 5 spaces to the front of the 2nd line.

I then changed the label alignment so the text was rotated and it was positioned top centre.

Next I added Label Text Missed Target to the Label shelf on the Measure Values marks card.

I edited the text as below, left aligning and again adding 5 spaces in front

Then I adjusted the alignment to be top left

Adding Category to the Filter shelf and testing with the different filters, suggested this technique seemed to work (at least on Desktop).

Last step is to remove the secondary axis, remove the row & column dividers, and hide the ‘nulls indicator’. Then add to a dashboard.

My published instance is here.

This challenge certainly raised questions over these formatting specifics. As I mentioned above, it shouldn’t be that hard to add a label that looks like this – left aligned and positioned above the bar. However the product doesn’t let you achieve this easily as yet, which is disappointing and certainly confusing to new users of the product – it seems such a straightforward requirement after all.

If I was doing this for my own work, I’d have kept with whatever options a single label allowed (where the text was all right aligned), or placed on a single line. The impact on performance of a complicated calculation along with the maintainability of a dashboard using it, are important considerations when deciding what’s ‘good enough’, and are arguments that should be made if a client/user insists. After all, what real benefit does this particular format provide over

or

Happy vizzin’!

Donna

Can you compare Same Day to a Selected Date?

Donna ColesLeave a comment

Week 20 for #WOW2021, and Lorna Brown set the challenge based on comparing dates. The requirement was to avoid LoDs in preference of table calculations. Unintentionally, I managed to build a solution without table calcs either. This just happened based on the problem solving process I ended up taking. I figured I would encounter a need for a table calc as I progressed, but it didn’t happen.

  • Defining the key calculated fields
  • Building the BAN
  • Building the Line Chart
  • The Reset button
  • Adding the dashboard interactivity

Defining the key calculated fields

To drive this viz, you’ll need 2 parameters – one to store the selected date and one for the selected comparison timeframe.

pSelectedDate

pComparison

Set to be an integer (more efficient in calcs later), but display text on screen

We now need to work out what the comparison date will be for each of the selections.

We’ll start with the date last year. We’re looking for the ‘equivalent’ day of the year in the previous year. I didn’t read the hint provided, so came up with my own method based on the month and whether we’re in a leap year or not

Equivalent Day Last Year

DATE(IF ((DATEPART(‘month’,[pSelectedDate]) <=2) AND ((YEAR([pSelectedDate])-1)%4=0)) OR ((DATEPART(‘month’,[pSelectedDate])>=3) AND (YEAR([pSelectedDate])%4=0))
THEN DATEADD(‘day’,2,DATEADD(‘year’,-1,[pSelectedDate]))
ELSE DATEADD(‘day’,1,DATEADD(‘year’,-1,[pSelectedDate]))
END)

So what is this saying….

If the month of the pSelectedDate is Jan or Feb AND the previous year is a leap year (as it can be divided by 4 with no remainder), OR if the month of pSelectedDate is not Jan or Feb and this year is a leap year, then get the exact same date from last year, but add on 2 days, otherwise get the exact same date from last year, but add on just 1 day.

Now let’s consider the same day for last month. I played with Lorna’s solution, entering difference dates to see what the compare date came back as, and it wasn’t always clear to me what the logic was that was being used. So this is what I came up with. If the pSelectedDate was for example, the 3rd Tuesday in the month, then I wanted to get the 3rd Tuesday in the previous month. However, based on the length of months and when months start and end, some months can have nearly 5 weeks in a month, while the previous may only have 4. In my logic therefore, if there was no 5th Tuesday in the previous month, I would return the 4th Tuesday of the month. So in that instance if pSelectedDate is the 4th Tuesday in the month, I’d compare to the 4th Tuesday of the previous month. If pSelectedDate is moved to the 5th Tuesday in the same month, it would also compare to the 4th Tuesday of the previous month – PHEW! all a bit mind boggling perhaps, and it certainly took the most amount of time in this challenge.

I broke this down into multiple calculations.

DoW Selected Date

DATEPART(‘weekday’,[pSelectedDate],’Sunday’)

What is the day of the week number for the pSelectedDate? If pSelectedDate is 18th May 2021, this will be 3 (ie Tuesday).

Exact Date Prev Month

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

as it says on the tin – exact same date last month, so if pSelectedDate is 18th May, this will be 18th April.

Prior DoW Prev Mth

DATE(CASE [DoW Selected Date]
WHEN 1 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Sunday’)
WHEN 2 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Monday’)
WHEN 3 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Tuesday’)
WHEN 4 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Wednesday’)
WHEN 5 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Thursday’)
WHEN 6 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Friday’)
WHEN 7 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Saturday’)
END)

If pSelectedDate is a Tuesday, then this returns the date of the Tuesday that is before the exact date last month. If pSelectedDate is Tuesday 18th May, Exact Date Prev Month is 18th April, which is a Sunday. Truncating this date to the start of the week, where the week starts on a Tuesday, returns Tues 13th April.

Next DoW Prev Mth

DATE(DATEADD(‘week’, 1, [Prior DoW Prev Mth]))

Just adds 1 week onto the above.

Equivalent Day Last Mth

IF MONTH([pSelectedDate]) = MONTH([Next DoW Prev Month]) THEN [Prior DoW Prev Mth]
ELSE [Next DoW Prev Month]
END

If the months of pSelectedDate and New DoW Prev Mth are the same, then use the ‘previous’ date, otherwise use the ‘next date’. This took some pen and paper to work out!

So now having worked out what the dates might be, I just plugged them into the below to get the comparison date we need.

Compare Date

DATE(IF [pComparison] = 1 THEN [Equivalent Day Last Year]
ELSEIF [pComparison] = 2 THEN [Equivalent Day Last Month]
ELSE DATEADD(‘week’, -1, [pSelectedDate])
END)

Further fields we then need are

Selected Date Sales

IF [Order Date] = [pSelectedDate] THEN [Sales] END

Compared Date Sales

IF [Order Date] = [Compare Date] THEN [Sales] END

% Difference

(SUM([Selected Date Sales]) – SUM([Compared Date Sales])) / SUM([Compared Date Sales])

custom formatted to â–²0.0%;â–¼0.0%

Building the BAN

I built this using a bar mark type with MIN(1) on Columns and the axis fixed from 0 to 1.

Then all the relevant fields were added to the Label field and organised accordingly. All date fields were formatted to the <weekday>, dd mmmm, yyyy format.

The % Difference field was added to Colour, and then the colour legend formatted as below

Building the Line Chart

The line chart only needs to show the information between the Compare Date and the pSelectedDate.

Dates to Show

[Order Date]>=[Compare Date] AND [Order Date]<=[pSelectedDate]

This needs to be on Filter shelf and set to True.

The main line chart is then just simply Order Date (exact date, continuous) plotted against Sales

For the circle markers, we can add Selected Date Sales and Compared Date Sales as ‘shared measures’ onto another axis

To get the ‘label’ points to show, we’re going to plot 2 more marks at an arbitrary point on the axis, but at the dates we care about. For this I created

Minus 500

IF [Order Date] = [pSelectedDate] OR [Order Date]= [Compare Date] THEN -500 END

Add this into the Measure Values section, but aggregated to Avg rather than Sum

We want to label these marks, but not with the -500 value.

Selected | Compared Date Sales

IF [Order Date] = [pSelectedDate] THEN ([Selected Date Sales])
ELSEIF [Order Date] = [Compare Date] THEN ([Compared Date Sales])
END

Add this field onto the Label shelf of the Measure Values marks card and show mark labels

But we only want the labels to show against the lower marks. I have to admit I tried many things to try to make this work, but in the end had to peek at Lorna’s solution. The label setting needs to be – its the Measure Names field that is key here!

I then set the colour of the Minus 500 measure to white, so you can’t see it

This chart can then be set to dual axis (don’t forget to synchronise those axis). You may need to reset some of the mark types etc. Remove all headers/borders/gridlines.

The Reset Button

I created a Default Date field storing DATE(#2021-05-18#) and added to a sheet as below.

Adding the dashboard interactivity

On click of a point on the chart, it should set the pSelectedDate parameter. This is a parameter action.

We don’t what the chart to show the point as ‘selected’/highlighted though, so we need to use a dashboard filter action to supress this from happening.

Create fields True = True and False = False. Add these 2 fields to the Detail shelf on the line chart.

Then on the dashboard, add a dashboard filter action as below, setting the fields to map True to False.

Finally, the reset button needs to set the parameter too :

Hopefully this should be enough to help you complete this challenge. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

How much do these states contribute to the total?

Donna ColesLeave a comment

By Week 20 of #WOW2020, Tableau v2020.2 was released, so guest challenger, Sean Miller, returned with a challenge to demonstrate one of the new bits of functionality that had been released – the ability to remove values from a set via dashboard actions.

Selected states on the map are highlighted and added to the list displayed on the right; clicking a state on the list then removed it from being highlighted on the map.

This felt like it should be straightforward, which was quite a relief after the last couple of weeks, and I’m hoping this blog doesn’t take too long to write 🙂

Spoiler alert! It was straight forward – hurrah! I do really value these ‘simpler’ challenges, when the main purpose is to introduce new features and functionality. In a business environment, it’s not always possible to work with the latest release, so having these challenges as a useful working example of a new feature to reference in future, is of great benefit.

So, if you hadn’t already realised, you’re going to need v2020.2 to complete this challenge, which you can get here , or you may need to use Tableau Public instead.

Creating the State set

Double-click State, double-click Sales and change mark type to Filled Map, and you’ve got your basic starting point 🙂

We want to be able to have different colours for the states depending on whether they’re in or out of a set of selected states.

Create a Selected States set by right clicking on State and selecting Create -> Set. Choose a random set of states.

Note – in v2020.2, the Selected States set is now just listed in the top left hand ‘dimensions’ pane (which also is now no longer labelled dimensions) with a ‘set’ icon to indicate its ‘data type’, rather than being listed in a separate ‘Sets’ section towards the bottom.

Drag this set onto the Colour shelf of the map. The states selected in the set will be coloured differently from the other states. Adjust the In/Out colour legend to suit.

Remove the map features

The displayed map in the solution is very ‘clean’; all labels,non US countries etc are hidden. This is set by selecting Map -> Map Layers from the top menu, and unchecking all the pre-selected Map Layer options.

Map Tooltip

The tooltip displays more measures than just the Sales, so we need some calculated fields

# Orders

COUNTD([Order ID])

# Customers

COUNTD([Customer ID])

Add these to the Tooltip shelf and adjust the display accordingly.

Creating the State List

There are several ways to build a basic list, but to get the formatting nicer, I created it using a bar chart as follows

  • Type in ‘Min(1)’ to Columns
  • Add State to Rows
  • Add Selected States to Filter shelf
  • Add State to Text shelf
  • Adjust Text to read ‘x <State>’ and format to white text, and align left middle

Adjust the colour of the bar to suit, then

  • Uncheck Show Header from the State pill in the Rows
  • Adjust the axis to be fixed from 0 to 1
  • Hide the axis.
  • Change the Tooltip text to ‘CLICK TO REMOVE’
  • Change the title to include the instruction

Invoke the Set Actions

Add both sheets to a dashboard.

Add a Change Set Values Dashboard Action to the Map sheet as below

As you click a state or select a group of states, it/they will be added to the list.

Add another Change Set Values dashboard action, this time based off the state list as follows

As you click on a state in the list now, it will disappear from the list and the state will no longer be highlighted on the map.

You will notice though, that as you click states on the map, your selections remain ‘selected’ until you click again. To fix this we need to use a little ‘true = false’ trick to ‘automatically deselect’ the states.

Automatically deselect states

Create new calculated fields called

True

True

False

False

and add both of these to the Detail shelf on the Map sheet.

Then back on the dashboard create a Filter action as follows

Building the Bar Charts

The above has completed the main part of the challenge and demonstrates the new feature, the option to Remove values from set in the Set Action dialog.

However Sean added some extra charts to the display.

Sales Chart

Add Sales to Columns and Selected Sales to Colour. Change Sales to be a Quick Table Calculation and set it to Percent of Total.

The tooltip and the chart title have additional information so we need

Total Sales

{FIXED:SUM([Sales])}

Sales of Selected States

{FIXED: SUM(IF [Selected States] THEN [Sales] END)}

% Sales of Selected States

[Sales of Selected States] / [Total Sales]

Format all these accordingly.

Add all these to the Detail/Tooltip shelf as required and amend Tooltip and Chart Title as necessary.

Orders Chart

This is pretty much the same as above….

Add # Orders to Columns and Selected Sales to Colour. Change # Orders to be a Quick Table Calculation and set it to Percent of Total.

The tooltip and the chart title have additional information so we need

Total Orders

{FIXED:COUNTD([Order ID])}

Orders of Selected States

{FIXED: COUNTD(IF [Selected States] THEN [Order ID] END)}

% Orders of Selected States

[Orders of Selected States] / [Total Orders]

Customers Chart

This doesn’t work quite the same (I only found out after the event), as a customer can order against more than 1 state it would seem. So while you’ll still need the following

Total Customers

{FIXED:COUNTD([Customer ID])}

Customers in Selected States

{FIXED: COUNTD(IF [Selected States] THEN [Customer ID] END)}

% Customers of Selected States

[Customers of Selected States] / [Total Customers]

we need to build it as a dual axis, with % Customers of Selected States on Columns alongside Min(1)

This should be all the building blocks needed. My published viz is here.

Happy vizzin’! Stay Safe!

Donna