Can you filter a small multiples chart?

Donna ColesLeave a comment

Erica set the challenge this week, and I’m not gonna lie, I found this tough. On the face of it, it looks like something I felt I should be ok at, but nuances cropped up as I was building that meant I often had to change tact and try something different.

My intention was to build each version – Beginner, Intermediate then Advanced, adding to my solution each time, but decisions I made early on, then caused me grief later. For example, I did choose to utilise a lot of table calcs, but that meant when it came to applying the sorting mechanism that I wanted, I couldn’t reference the sort field I needed, as it contained a table calc. So I had to unpick the logic and build with LODs instead which took me a while to get right. Getting single lines to display in each ‘state’ cell also proved tricky at times, and that was even before I’d got to the requirement to pad out the ‘missing values’ with 0s. I also seemed to find that some things only seemed to work if I added pills and applied settings in a particular order. All in all, quite a challenge, and while I did get there in the end, I did have to peek at the solution at times to figure out if I was going nuts, but I found trying to scale back Erica’s solution to the beginner/intermediate version also suffered the same issues I was experiencing. I built with Desktop v2024.1 and there were times I was wondering if something had “broken” in that version, although having finally reached the end, I’ve yet to test that theory.

So, I’m blogging this guide with several caveats – Going from beginner to intermediate is ‘okay’, but when it gets to advanced I had to start again(ish). Some of the calcs I provide will just be ‘as is’. I will do my best to explain what’s going on, but there are times that I just don’t get it, and it’s just been trial and error that got me the results I needed – sorry!

So with all that in mind, let’s get building.

Initial steps

After connecting to the provided hyper file, I found I did have to create the modified sales value

Sales Modified

IF SUM([Sales])<5000 THEN SUM([Sales])*10 ELSE SUM([Sales]) END

I also decided to add a data source filter (right click data source > edit data source filters) to restrict the data to just Country/Region = USA.

The later versions of superstore have Canadian Provinces included too, and I don’t think these were listed in Erica’s solution. It just felt easier all round to exclude these records from source.

Beginner challenge

When building a trellis chart, we need to determine which Row and which Column our specific dimensions (in this case State/Province) will sit in. As the requirements already stated it was to be a 3 column grid, our calculations for this didn’t need to be so complex.

Cols

(INDEX()-1)%3

INDEX() returns an incremental number starting at 1 for whatever dimension or set of dimensions we’re counting over. In this case we’re counting the number of State/Provinces. %3 returns the remainder when the index is divided by 3, so we get values of 0, 1 and 2.

Change this field to be discrete (right click -> convert to discrete)

Add State/Province to Rows then add Cols to Rows. Edit the table calculation so the field is computing by State/Province. You can see that the first 3 rows will be positioned in columns 0, 1,2 respectively and so on.

Create a new field

Rows

INT((INDEX()-1) / 3)

This takes the index value (minus 1), divides by 3 and ’rounds’ to a whole number. Make this discrete too and add to Rows, setting the table calculation as described above. Now we can see the first 3 rows will all actually be in the same row (row 0), then next 3 rows in row 1 and so on.

Shift the pills around so Cols is on Columns, Rows is on Rows and State/Province is on Detail. Add Sales Modified to Rows.

Create a new field

Quarter Date

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

and add this to Columns setting it as a continuous exact date (green pill). We’ve got a bit of unexpected ‘spaghetti’ going on…

To fix it, do the following ..

Add Quarter Date to Detail as a discrete exact date (blue pill). Change Quarter Date on Columns to be a continuous attribute (green pill – first change to attribute, then change to continuous). Edit the table calculation settings for both the Rows and the Cols fields to be computing by both State/Province and Quarter Date at the level of State/Province.

I had to reference previous challenges and blog posts I’d written to manage this… maybe there is something simpler, as this is pretty taxing for the ‘beginner’ part of the challenge.

Add another instance of ATTR(Quarter Date) to Columns and make dual axis and synchronise the axis. This will create an axis at the top of the chart as well as the bottom.

Format the ATTR(Quarter Date) pill so the axis format is custom formatted to “Q”q “‘”yy

Edit all the axis (top/bottom and left) and update the title. Adjust the Tooltip. Hide the Cols and Rows fields (right click and uncheck show header). Change the Colour of the line to grey.

This should be the Beginner solution. I have published this here.

Intermediate challenge

For this part of the challenge, we need to set up lots of new calculations, so let’s do this first. As usual, I’ll manage this in a tabular format. So on a new sheet, add State/Province and Quarter Date as a discrete exact date (blue pill) to Rows. Add Sales Modified to Text.

We need to get the threshold value for each State/Province, which is the average of the numbers all listed above, multiplied by 2. We’ll use LODs for this

Threshold

{FIXED [State/Province]: AVG({FIXED [State/Province], [Quarter Date]: ([Sales Modified])})} * 2

working inside out… get the value of the Sales Modified value for each State/Province and Quarter Date (which is the same as the values you see listed above) and then average this at the State/Province level and multiple the final result by 2. Add this to the table. This is the field we’ll be using for the horizontal reference line.

Next we need to identify the rows where the Sales Modified value exceeds the threshold, and then return the Sales Modified values for only these rows. I’ll do this in 2 stages

Is Above Threshold?

INT([Sales Modified] > SUM([Threshold]))

This returns a 1 or 0 depending on whether the statement is True or False. Using actual numeric values rather than boolean helps later on. Set this field to be discrete and add to the table.

Above Threshold Sales

IF [Is Above Threshold?]=1 THEN [Sales Modified] END

Add this to the table too. For the rows where we have 1’s, a value is displayed. This is the field we’ll be using for the red circles.

Finally we need to determine some fields to help us define a reference band. These need to be dates as they’ll be applied to the date axis, but the band doesn’t stretch to the previous/next quarter, and is only present if the last value is over the threshold.

Again using LODs let’s get the final date in the quarter

Max Quarter Per State

{FIXED [State/Province]: MAX([Quarter Date])}

Add this to the table as a discrete exact date (blue pill).

Now we need to know if the value associated with the final quarter is above the threshold or not

Final Quarter Above Threshold

INT(MIN([Quarter Date]) = MIN([Max Quarter per State]) AND [Is Above Threshold?]=1)

Again this will return a 1 or 0. Change to discrete and pop that into the table too. We can see Colorado is the first state listed where this is true.

Now we want to ‘spread’ that value across every row associated to the state

State Has Final Quarter Above Threshold

{FIXED [State/Province]: MAX(
{FIXED [State/Province], [Quarter Date]: ([Final Quarter Above Threshold])})
}

For each State/Province and Quarter Date, get the Final Quarter Above Threshold value and then get the maximum value of this for each State/Province. This is where having the values as 1’s and 0s helps.

Make discrete and add this to the table. Every row for Colorado has this set to 1

Now we can work out some dates

Ref Band Min

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, -1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month less than the final quarter for that state.

Similarly

Ref Band Max

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, 1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month more than the final quarter for that state.

Add both of these of the table as discrete exact dates (blue pills).

Now we have all the building blocks to build the next bit of the challenge.

Start by duplicating the Beginner viz.

Add Above Threshold Sales to Rows and make Dual axis and synchronise the axis.. Remove the 2nd instance of the Quarter Date from Columns, so we now have marks cards relating to the 2 measures rather than the 2 dates. Remove Measure Names from the All marks card.

Change the mark type on the Above Threshold Sales marks card to circle and change the colour to red. Adjust size to suit.

Add Threshold to the Detail shelf of the All marks card. Right click on the Sales axis and add reference line. Set it to be per pane and use the Threshold field. Display the value as the Label. Don’t show a Tooltip. Display a dotted black line.

Update the Tooltip to now have a reference to the threshold value too.

Add Ref Band Max and Ref Band Min to the Detail shelf of the All marks card. Set them both to be continuous attributes (green pills).

Right click on the Quarter Date axis and add reference line. Set it to be a band per pane that goes from the Ref Band Min to the Ref Band Max. Don’t display labels or tooltips or a line. Fill with a pale shade ot red/pink.

Add back in the additional Quarter Date dual axis as described in the Beginner section to get the dates listed the top again.

Hide the right hand axis (uncheck show header) and hide the NULL indicator.

This completes the Intermediate challenge. My version is published here.

Advanced challenge

Go back to the tabular sheet we were using to check the calculations needed for the Intermediate challenge, as we’ll build on this.

Firstly the sorting. We need to sort the states based on whether the final quarter was above the threshold or not, and then by the number of times the state was above the threshold. Let’s get the count to start with

Above Threshold Count Per State

{FIXED [State/Province]: SUM(
{FIXED [State/Province], [Quarter Date]: ([Is Above Threshold?])})
}

For each State/Province and Quarter Date, get the Is Above Threshold? value and then sum these up for each State/Province. This is where again having the values as 1’s and 0s helps.

Make this discrete and add to the table

then create a field we’ll use for the sort. This is going to be a numeric field

Sort

IF [State Has Final Quarter Above Threshold] = 1 THEN
100000 + [Above Threshold Count Per State]
ELSE
[Above Threshold Count Per State]
END

We’re just using a very large arbitrary number to force those states where the final quarter is over the threshold to be higher in the list. Make this discrete and add to the table too.

We can now apply a Sort to the State/Province field to sort by the Sort field descending

This results in Colorado moving to the top of the list followed by Minnesota, which also has 3 quarters above the threshold, including the last quarter, but alphabetically falls after Colorado so is listed 2nd.

To filter the data, I created another field, just for ease

Filter

IIF([State Has Final Quarter Above Threshold]=1,’Urgent’,’Non-Urgent’)

Add this to the Filter shelf and set to Urgent. The states should now be restricted to just those where the final quarter is above threshold.

For the final requirement of this challenge, we’ll build out another table on another sheet to demonstrate, as we need to work with a different instance of the quarter date.

On a new sheet, add State/Province to Rows and add Order Date at the Quarter (month year) level as a discrete field (blue pill) to Rows. Add Sales Modified to Text

For Alabama, we don’t have a 2020 Q3 or a 2023 Q3. Click on the Quarter(Order Date) pill and select Show Missing Values. These quarters appear but with no Sales Modified value.

We’ve had to use this different way to define the date quarter as if we tried to ‘show missing values’ against the Quarter Date field that is set to ‘exact date’, we send up getting every day that is missing, not just the dates relating to the quarter. Also, the ATTR(Quarter Date) field we’ve used on the previous vizzes, doesn’t allow the Show Missing Dates option.

Anyway, we need to get 0s in to these dates.

Show Modified with 0

ZN(LOOKUP([Sales Modified],0))

Apart from the Rows/Cols calcs needed for the trellis, this is the only table calculation I ended up using. It’s basically looking up it’s own row (LOOKUP([field],0)) and if it can’t find a value (as it’s missing) it’s returning 0 (the ZN() function). Add that to the table.

Ok so now we have the components needed, let’s build the viz. We can use the Intermediate version as a starting point, but will need to reapply some of the features.

Duplicate the Intermediate viz.

Remove the 2nd instance of the ATTR(Quarter Date) pill on Columns. Drag the Sales Modified with 0 pill and drop it directly over the Sales Modified pill so it replaces it. Hopefully the chart should still look the same.

Right click the Order Date field from the left hand data pane, and drag it directly onto the ATTR(Quarter Date) pill. Release the mouse and select the continuous quarter/year option from the dialog that displays.

Things will start to look a bit odd… you’ve lost your lines… Remove the Quarter Date pill from the Detail shelf on the All marks card. Fix the Rows and Cols table calc fields by just updating them to compute by State/Province only. Adjust the table calc of the Sales Modified With 0 field to compute by Quarter of Order Date and State/Province in that order.

Things still look crazy…. but just one more step… Show Missing Values on the Quarter(Order Date) pill.

Now every cell should be associated to a single State/Province with no broken lines, and Wyoming, right at the bottom, should show more than a single dot. This was A LOT of trial and error to fathom all this out.

Add back in the reference band following the instructions above (you should find the reference line for the threshold value will just then appear) and re-update the Tooltip.

Add another instance of Order Date at the quarter/year continuous level (green pill) to Columns and make dual axis and synchronise the axis. Edit the axis titles, and format them to the “Q”q “‘”yy custom format.

Apply the sort to the State/Province field on the Detail shelf so it is sorting by Sort Descending. Add Filter to the Filter shelf and select Urgent.

Add this to a dashboard, and that should be the completed Advanced challenge which I’ve published here.

There really was some black magic going on here at times. Tough one this week!

Happy vizzin’!

Donna

Can you visualise when it gets busy?

Donna ColesLeave a comment

It was Yoshi’s first challenge as a #WOW coach this week, and it provided us with an opportunity to develop our data densification and date calculation skills. I will admit, this certainly was a challenge that made me have to think a lot and reference other content where I’d done similar things before.

Modelling the data

Yoshi provided us with an adapted data set of patient waiting times, based on the Healthcare dataset from the Real World Fake Data (#RWFD) site. This provides 1 row per patient with a wait start time and end time. The requirement was to understand how many patients were waiting in each 30 min time slot in a 24hr period, so if a patient waited over 30 mins, or the start & wait time spanned a 30 min time slot, then the data needed to be densified appropriately. Yoshi therefore provided an additional data set to use for this densification, and I have to be honest, it wasn’t quite what I was expecting.

So the first challenge was to understand how to relate the two sets of data together, and this took some testing before I got it right. Yoshi provided hints about using a DATEDIFF calculation to find the time difference between the wait start time (rounded to 30 mins) and the wait end time (rounded to 30 mins).

To work out the calculation required, I actually first connected to the Hospital ER data set and then spent time working out the various parts of the formula required. I wanted to work out what the waiting start time was ’rounded down’ to the previous 30 min time slot, and what the waiting end time was ’rounded up’ to the next 30 min time slot.

Date Start Round

//Rounding down the wait start time

IIF(DATEPART(‘minute’, [Date Start])<30 , DATETRUNC(‘hour’, [Date Start]) , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date Start])))

If the minute of Date Start is between 0 and 29, then return the date at the hour mark, otherwise (if the minute of Date Start is between 30-59) then find the date at the hour mark, and add on 30 minutes. So if Date Start is 23/04/2019 13:23:00 then return 23/04/2019 13:00:00, but if Date Start is 23/04/2019 13:47, then return 23/04/2019 13:30:00.

Date End Round

//Rounding up the wait end time

IIF(DATEPART(‘minute’, [Date End])<30 , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date End])) , DATEADD(‘hour’, 1,DATETRUNC(‘hour’, [Date End])))

If the minute of Date End is between 0 and 29, then find the date at the hour mark, and add on 30 minutes, otherwise (if the minute of Date End is between 30-59) then find the date at the hour mark, and add on 1 hour. So if Date End is 23/04/2019 13:23:00 then return 23/04/2019 13:30:00, but if Date End is 23/04/2019 13:47, then return 23/04/2019 14:00:00.

With this, I was then able to relate the Hospital ER data set to the dummy by 30 min data set by adding a relationship calculation to the Hospital ER data set of

DATEDIFF(‘minute’,
IIF(DATEPART(‘minute’, [Date Start])<30 , DATETRUNC(‘hour’, [Date Start]) , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date Start]))),
IIF( DATEPART(‘minute’, [Date End])<30 , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date End])) , DATEADD(‘hour’, 1,DATETRUNC(‘hour’, [Date End])))
)

(which returns the difference in minutes between the ’rounded down’ Date Start and the ’rounded up’ Date End – you can’t reference calculated fields in the relationship, so you have to recreate)

and set this to be >= to Range End from the dummy by 30 min data set

Let’s see what this has done to the data.

On a sheet add Patient ID. Date Start (as discrete exact date – blue pill), Date End (as discrete exact date – blue pill), Date Start Round (as discrete exact date – blue pill), Date End Round (as discrete exact date – blue pill) and Index (as discrete dimension – blue pill) to Rows and add Patient Waittime to Text.

Looking at the data for Patient Id 899-89-7946 : they started waiting at 06:18 and ended at 07:17. This meant they spanned three 30 min time slots: 06:00-06:30, 06:30-07:00 and 07:00-07:30, and consequently there are 3 rows displayed, and the ’rounded’ start & end dates go from 06:00 to 07:30.

Identifying the axis to plot the 24hr time period against

Having ‘densified’ the data, we now need to get a date against each row related to the 30 min time slot it represents. ie, for the example above we need to capture a date with the 06:00 time slot, the 06:30 time slot and the 07:00 time slot.

But, as the chart we want to display is depicted over a 24hr timeframe, we need to align all the dates to the exact same day, while retaining the time period associated to each record. This technique to shift the dates to the same date, is described in this Tableau Knowledge Base article.

Date Start Baseline

DATEADD(‘day’, DATEDIFF(‘day’, [Date Start Round], #1990-01-01#), [Date Start Round])

Add this into the table, you can see what this data all looks like – note, I’m just choosing a arbitrary date of 01 Jan 1990, so my baseline dates are all on this date, but the time portions reflect that of the Date Start Round field.

With the day all aligned, we now want to get a time reflective of each 30 min timeslot. The logic took a bit of trial and error to get right, and there may well be a much better method than what I came up with. It became tricky as I had to handle what happens if the time is after 11pm (23:00) as I needed to make sure I didn’t end up returning dates on a different day (ie 2nd Jan 1990). I’ll describe my logic first.

If the Index is 0 then we just want the date time we’ve already adjusted – ie Date Start Baseline

If the Index is 1, then we need to shift the Date Start Baseline by 30 minutes. However, if Date Start Baseline is already 23:30, then we need to hardcode the date to be 01 Jan 1990 00:00:00, as ‘adding’ 30 mins will result in 02 Jan 1990 00:00:00.

If the Index is 2, then we need to shift the Date Start Baseline by 1 hour. However, if Date Start Baseline is already 23:30, then we need to hardcode the date to be 01 Jan 1990 00:30:00, as ‘adding’ 1 hour will result in 02 Jan 1990 00:30:00. Similarly, if Date Start Baseline is already 23:00, then we need to hardcode the date to 01 Jan 1990 00:00:00, otherwise we’ll end up with 02 Jan 1990 00:00:00.

As the relationship didn’t result in any instances of Index > 2, I stopped my logic there. This is all encapsulated within the calculation

Date to Plot

CASE [Index]
WHEN 0 THEN [Date Start Baseline ]
WHEN 1 THEN IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=30, #1990-01-01 00:00:00#, DATEADD(‘minute’, 30, [Date Start Baseline ]))
WHEN 2 THEN IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=30, #1990-01-01 00:30:00#, IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=0, #1990-01-01 00:00:00#, DATEADD(‘hour’, 1, [Date Start Baseline ])))
END

Phew!

Add this to the table as a discrete exact date (blue pill), and you can see what’s happening.

For Patient Id 899-89-7946, we have the 3 timeslots we wanted: 06:00 (representing 06:00-06:30), 06:30 (representing 06:30 -07:00) and 07:00 (representing 07:00-07:30).

On a new sheet, add Date To Plot as a discrete exact date (blue pill) and then create

Count Patients

COUNT([Patient Id])

and add to Text and you have a list of the 48 30 min time slots that exist within a 24hr period, with the patient counts.

We need to be able to filter this based on a quarter, so create

Date Start Quarter

DATE(DATETRUNC(‘quarter’,[Date Start]))

and custom format this to yyyy-“Q”q

Add to the Filter shelf selecting individual dates and filter to 2019-Q2, and the results will adjust, and you should be able to reconcile against the solution.

Building the 30 minute time slot bar chart

On a new sheet, add Date Start Quarter to the Filter shelf and set to 2019-Q2. Then add Date to Plot as a continuous exact date (green pill) to Columns and Count Patients to Rows. Change the mark type to a bar and show mark labels.

Custom format Date To Plot to hh:nn.

We need to identify a selected bar in a different colour. We’ll use a parameter to capture the selected bar.

pSelectTimePeriod

date parameter defaulted to 01 Jan 1990 14:00 with a display format of hh:nn

Then create a calculated field

Is Selected Time Period

[Date to Plot] = [pSelectTimePeriod]

and add to the Colour shelf, adjusting colours to suit and setting the font on the labels to match mark colour.

A ‘bonus’ requirement is to make each bar ’30 mins’ wide. For this we need

Size – 30 Mins

//number of seconds in 30 mins divided by number of seconds in a day (24hrs)
(30 * 60) / 86400

Add this to the Size shelf, change it to be a dimension (so no longer aggregated to SUM), then click on the Size shelf, and set it to be fixed width, left aligned.

Via the Colour shelf, change the border of the mark to white.

Next, we need to get the tooltip to reflect the ‘current’ time slot, as well as the next time slot. For this I created

Next Time Period

IIF(LAST()=0, #1990-01-01 00:00:00#, LOOKUP(MIN([Date to Plot]),1))

This is a table calculation. If it’s the last record (ie 01 Jan 1990 23:30), then set the time slot to be 1st Jan 1990 00:00, otherwise return the next time slot (lookup the next (+1) record). Custom format this to hh:nn and add to the Tooltip shelf. Set the table calculation to compute by Date to Plot and Is Selected Time Period.

Update the Tooltip as required.

The final part of this bar chart is another ‘bonus’ requirement – to add 2 hr time interval ‘bandings’. For this I created another calculated field

2hr Time Period to Plot

IIF(DATEPART(‘hour’, MIN([Date to Plot]))%4 = 0 AND DATEPART(‘minute’, MIN([Date to Plot]))=0,WINDOW_MAX([Count Patients])*1.1,NULL)

If the hour part of the date is divisible by 4 (ie 0, 4, 8,12,16, 20), and we’re at the hour mark (minute of date is 0), then get the value of highest patient count displayed in the chart, and uplift it by 10%, otherwise return nothing.

Add this to Rows. On the 2nd marks card created, remove Is Selected Time Period from Colour and Next Time Period from Tooltip. Adjust the table calculation of the 2hr Time Period to Plot to compute by Date to Plot only. Remove labels from displaying.

The bars are currently showing all at the same height (as required) but at a width of 30 minutes. We want this to be 2 hrs instead, so create

Size – 2 Hrs

//number of seconds in 2hrs / number of seconds in a day (24 hrs)
(60*60*2)/86400

Add this to the Size shelf instead (fixing it to left again). Adjust the colour to a pale grey, and delete all the text from the Tooltip.

Hide the nulls indicator. Make the chart dual axis and synchronise the axis. Right click on the 2hr Time Period Plot axis (the right hand axis) and move marks to back.

Tidy up the chart by removing row & column dividers, hiding the right hand axis, removing the titles from the other axes. Update the title accordingly.

Building the Patient Detail bar chart

On a new sheet, add Is Selected Time Period to Filter and set to True. Then go back to the other bar chart, and set the Date Start Quarter Filter to Apply to Worksheets -> Selected Worksheets and select the new one being built.

Add Department Referral, Patient Id, Date Start (as discrete exact date – blue pill) and Date End (as discrete exact date – blue pill) to Rows and Patient Waittime to Columns. Manually drag the None Department Referral option to the top of the chart. Add Patient Waittime to Colour and adjust to use the grey colour palette. Remove column dividers.

The title of this chart needs to display the number of patients in the selection, as well as the timeframe of the 30 min period. We already have the start of this captured in the parameter pSelectTimePeriod. We can use parameters to capture the other values too.

pNumberPatients

integer parameter defaulted to 0

pNextTimePeriod

date parameter, defaulted to 01 Jan 1990 14:30:00, with a custom display format of hh:nn

Update the title of the Patient Detail bar chart to reference these parameters (don’t worry about the count not being right at this point).

Capturing the selections

Add the sheets onto a dashboard. Then create the following dashboard parameter actions.

Set Selected Start

On select of the 30 Min bar chart, set the pSelectTimePeriod parameter passing in the value from the Date To Plot field.

Set Selected End

On select of the 30 Min bar chart, set the pNextTimePeriod parameter passing in the value from the Next Time Period field.

Set Count of Patients

On select of the 30 Min bar chart, set the pNumberPatients parameter passing in the value from the Count Patients field, aggregated at the SUM level.

With these all set, you should find you can click on a bar in the 30 minute chart and filter the list of patients below, with the title reflecting the bar chosen.

The basic viz itself wasn’t overly tricky, once you get over the hurdle of the calculations needed for the relationship and identifying the relevant 30 min time periods.

My published viz is here. Enjoy!

Happy vizzin’!

Donna

Can you create these KPI Cards?

Donna ColesLeave a comment

Lorna set a table calculation filled challenge this week to recreate some KPI cards using an aggregated and amended version of the Superstore data set.

Lorna purposefully used a string Period field to define the timeframe to encourage the use of table calculations. No doubt, there are ways you could use string functions/regex to extract the relevant year and month number to come up with a different solution, but I’m going to head down the intended route.

And as with any table calculation based challenge, I find it best to always build out all the calculations I need into a tabular display to start with before building the viz. So let’s get started…

Building out the Calculations

We need to have a handle on more of the data than just that associated to the Period we’re interested in, so we can’t use a simple Quick Filter on the Period field to restrict the data, otherwise we can’t ‘access’ data for the previous period. So to manage which Period we want to focus on, I created a parameter

pSelectedPeriod

string field that uses a list where the values are added from the Period field. Default to P12 Y2022/23

On a new sheet, add Period to Rows and then add the Sales , Profit and Quantity measures to Text. Show the pSelectedPeriod parameter

What we’re going to do is get the values for each measure that is associated to the pSelectedPeriod, and display that value over every row.

Curr Sales

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Sales] END))

If the Period matches that in the parameter, then get the Sales value and then use Window_MAX to ‘spread’ that value over every row

Add this to the table, and edit the table calculation to ensure the value is being computed by Period.

Repeat the same for Profit and Quantity

Curr Profit

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Profit] END))

Curr Qty

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Quantity] END))

If you change the parameter, you should see all the values in the last 3 columns changing to reflect the value from the first 3 columns of the relevant row.

Now we need to get the value from the previous period, ie the data from the previous row

Prev Sales

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Sales]),-1) END)

The LOOKUP function is taking the Sales value from the previous 1 row (-1), and then WINDOW_MAX is once again ‘spreading’ this value across every row. We also need

Prev Profit

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Profit]),-1) END)

Prev Qty

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Quantity]),-1) END)

Add these to the table, and again adjust the table calc settings for every field to compute by Period.

Now we have the current and previous values for each measure, we can work out the % difference

Diff Sales %

([Curr Sales]-[Prev Sales])/[Prev Sales]

Format this to a % with 1 dp

Diff Profit %

([Curr Profit]-[Prev Profit])/[Prev Profit]

Diff Qty %

([Curr Qty]-[Prev Qty])/[Prev Qty]

Add all these onto the sheet, and remember the table calc settings (for these, there are nested table calcs, so make sure both are set properly).

We now need an arrow indicator to display up or down depending on the % value, and this needs to display as a different colour, so we need two fields per measure.

Diff Sales Indicator +ve

IF [Diff Sales %] >= 0 THEN ‘▲’ END

Diff Sales Indicator -ve

IF [Diff Sales %] < 0 THEN ‘▼’ END

I use this site to copy the text symbols from

Diff Profit Indicator +ve

IF [Diff Profit %] >= 0 THEN ‘▲’ END

Diff Profit Indicator -ve

IF [Diff Profit %] < 0 THEN ‘▼’ END

Diff Qty Indicator +ve

IF [Diff Qty %] >= 0 THEN ‘▲’ END

Diff Qty Indicator -ve

IF [Diff Qty %] < 0 THEN ‘▼’ END

Add these to the sheet if you wish too (apply the table calc settings), but you’ll only get a value for one or the other field depending on whether the difference was +ve or -ve. Below I’ve just added the two Sales indicators

The Tooltip displays the value of the two Periods being compared. One of these is in the parameter, but we need to capture the other

Previous Period

WINDOW_MAX(IF [pSelectedPeriod] = MIN([Period]) THEN LOOKUP(MIN([Period]),-1) END)

So now we have all the values we need for the KPIs captured against every row in the dataset. So now we want to just show a single row. It could be the first, it could be the last… based on Lorna’s hint, let’s filter to just show the row related to the pSelectedPeriod value.

Filter Selected Period

[pSelectedPeriod] = LOOKUP(MIN([Period]),0)

Using the offset of 0 with the LOOKUP, returns the value for the row you’re on, so adding this to the FIlter shelf and selecting True, filters the display to the row where the Period matches the parameter. NOTE if you adjust the table calc settings of this field after adding to the filter shelf, you’ll need to reselect the option to filter to True.

As this is a table calculation, the ‘filter’ is applied later in the order of operations, so information about the other rows in the table can be referenced. Filtering just by Period as a quick filter, is essentially a dimension filter and that happens earlier on in the process, meaning the data about the other rows would be inaccessible.

So we have all the fields, now build the cards.

Building the KPI Cards

On a new sheet, double click into Columns and type in MIN(0). Repeat this 2 more times. This gives us 3 axis to build each of the 3 cards.

On the All marks card, add Period to the Detail shelf. Show the pSelectedPeriod parameter. Add the Filter Selected Period to the Filter shelf and set to true (adjusting the table calc and resetting the filter value as required).

Change the mark type of the All marks card to shape and select a transparent shape (see here for more details).

On the first MIN(0) marks card, add Curr Sales, Prev Sales, Diff Sales %, Diff Sales Indicator +ve and Diff Sales Indicator -ve to the Label shelf. Remember to apply the table calc settings for all of the fields!

Adjust the text within the label, so it is formatted and positioned as required and then align middle centre.

On the middle MIN(0) marks card, do similar by adding the equivalent profit fields onto the Label shelf, and then repeat again for the bottom MIN(0) marks card, adding the quantity fields to the Label shelf.

On the All marks card, add Previous Period, Curr Sales, Curr Profit, Curr Qty Prev Sales, Prev Profit, Prov Qty, Diff Sales %, Diff Profit % and Diff Qty % to the Tooltip shelf (remember to set those table calc settings!) Then adjust the Tooltip to display the text as required. I used the ruler to shift the starting position, along with tabs (the tab keyboard button) to ‘try’ to get everything to align, and it works for most circumstances….

Finally format the KPI card

  • Set the worksheet background colour to light grey
  • Remove all gridlines, zero line, axis lines
  • Set the column divider to be a thick white line
  • Set the row divider to be a thick white line
  • Hide the axis

Add the sheet onto a dashboard, and you should be done.

My published viz is here.

Happy vizzin’!

Donna

Can you create an area chart tile map?

Donna ColesLeave a comment

This week, Kyle set the challenge of recreating area charts combined with a tile map / small multiple. He’d been inspired by a viz he uses at work, which he then realised had actually been inspired by a previous #WorkoutWednesday challenge from 2020 which I actually blogged about here.

So some of the steps for this solution guide, I’ll lift from my existing blog :-), but there are no table calcs in this instance.

Building the basic tile map

As per Kyle’s instructions, I started by building a new field that I could then format to millions

Pop

[Population (Population)] * 1000

I formatted this to be a custom number with 2 dp and displayed with Millions unit.

Put this into a table where State and Date on Rows and Pop on Text, just so we can validate what we’re up to…

We need to standardise/normalise the display, so the population for each State is ranging from 0 to 1. For this we need to determine

Min Pop Per State

{FIXED [State]:MIN([Pop])}

Max Pop Per State

{FIXED [State]:MAX([Pop])}

and we can then work out

Standardised Pop

(SUM([Pop]) – SUM([Min Pop per State]))/(SUM([Max Pop per State]) – SUM([Min Pop per State]))

Format this to display as a number with 2 dp. Add all 3 fields to the table. For each state the Standardised Pop should have a year when then value is 0 (equivalent to when the population is lowest for the state) and a year when the value is 1 (equivalent to when the population is highest for the state).

For the tile map, we also need fields Rows and Cols which are calculations that map each State to a number (and can be copied straight from the challenge sheet).

On a new sheet, add Rows to Rows (as a discrete dimension) and Cols to Columns (also as a discrete dimension). Add State to Detail. Your initial ‘map’ layout should start to take shape.

Add Date to Columns and set to be a the continuous Year level (green pill) and add Standardised Pop to Rows. Set the display to Entire View, so all the tiles are visible.

Change mark type to Area and add Pop to the Tooltip. Update the Tooltip as required.

Adding the State label and max value

For this we’re going to plot a single point that will be at the centre of the Date axis and slightly higher than 1. For this we need

Centre Date

DATE(DATEADD(‘year’,
FLOOR((YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])}))/2),
{FIXED:MIN([Date])}
))

This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of years between the minimum year in the whole data set (1900) and the maximum year in the data set (2023). This is

YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])})

We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).

We then add this number of years to the minimum date in the data set (DATEADD), to get our central year – 1961.

Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the area chart (which is 1). Based on what I did before, I decided 1.75 worked

Plot State Label

IF YEAR([Date]) = YEAR([Centre Date]) THEN 1.75 END

Add Plot State Label to Rows between Rows and Standardised Pop. Change the mark type of this axis to shape and use a transparent shape (see here for info). Note you can use a circle and reduce size to smallest and opacity to 0% if you wish. However, this will show a small dot when hovering, which you don’t get with a transparent shape.

Add Max Pop per State to Label and change State from Detail to Label. Adjust the Label accordingly

Remove all text from the Tooltip dialog of the Plot State Label marks card, and hide the Nulls indicator label. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.

Then remove all gridlines, zero lines, axis lines and row/column dividers. Hide all the axis and the Cols and Rows pills (uncheck show header).

Building the bar chart

Create a new field

Latest Pop per State

IF [Date] = {FIXED [State]:MAX([Date])} THEN [Pop] END

If the date is the maximum date for the state, then get the population. Format this to be 2dp in Millions.

Add State to Rows and Latest Pop for State to Columns and sort descending. Adjust the colour to suit. Show mark labels, and remove all gridlines etc and row/column dividers and hide the axis. Adjust the Tooltip.

Creating the highlight action

Add the two sheets to a dashboard. Add a dashboard highlight action

Highlight State

On hover of the bar chart, target the map, via the State field only.

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

Happy vizzin’!

Donna

Can you include and exclude options with a custom control?

Donna ColesLeave a comment

It was my turn this week to set the challenge which I based on an example of a viz that I’d seen at work.

I built the viz using 3 sheets and using techniques similar to that described in a previous challenge documented here where I used parameter actions. It’s likely set actions can be used too, although I haven’t tested this out.

Building the bar chart

Format Sales to $ with 0dp, then add State/Province to Rows and Sales to Columns and sort descending. Add State/Province to Filter and filter by Top 5 based on Sales.

Double click on the Columns shelf and manually type MIN(0) to create a second axis. Make the chart dual axis and synchronise the axis. Change the mark type of the Sales marks card to Bar and the mark type of the MIN(0) axis to Gantt. Add Measure Names to Rows.

Remove Measure Names from the Colour shelf of both marks cards. Then add State/Province and Sales the Label shelf of the MIN(0) marks card. Arrange the text to match.

Reduce the size of the MIN(0) gantt bar to as small as possible, and reduce the opacity of the mark (via the Colour shelf) to 0%.

Update the Tooltip on the Sales marks card to match, but delete all text from the Tooltip of the MIN(0) marks card.

Add State/Province to the Colour shelf of the Sales marks card, and adjust accordingly. I chose to set the opacity to 50% based on the colours I chose.

Hide both axis, and both row heading (uncheck show header). Remove all gridlines, row/column dividers, axis ruler etc.

Name the sheet Bar or similar. We’ll come back to this later.

Building the trend line

On a new sheet, add Order Date to Columns set to a continuous monthly level (green pill) and Sales to Rows. Add State/Province to Colour (and reduce opacity if need be).

Go back to the bar chart sheet, and apply the State/Province filter to the trend line sheet too (right click on the pill in the filter shelf > apply to worksheets > selected worksheets).

Add a Running Total quick table calculation to the Sales pill (right click > quick table calculation > running total).

Add State/Province to the Label shelf, and set the font of the label to match mark colour. Adjust the text in the Tooltip. Format the Running Total Sales pill on the Rows shelf to be in $k with 0 dp. Delete the titles of both axis, and reduce the font size on the axis. Remove axis rulers/tick marks, zero lines, row/column dividers. Name the sheet Trend or similar.

Now, we need to be able to capture which States are being excluded. We need a parameter for this.

pUnselectedStates

string parameter defaulted to empty string/nothing

Show the parameter and manually type ‘Texas’ into the field. Based on what is in this parameter, will determine whether the state is unselected or not.

State is unselected

CONTAINS([pUnSelectedStates], [State/Province])

This returns True if the associated State/Province is listed in the parameter (so shouldn’t be visible) and False if it’s not listed (so should display).

Add State is unselected to the Filter shelf and set to False. The line for Texas will now disappear

Experiment by typing multiple states into the parameter eg Texas California

Building the selection control

On a new sheet add State/Province to Rows and sort by the Sales descending

Set the State/Province filter from another sheet to also apply to this sheet.

Double click into Columns and type MIN(0). Add State/Province to Colour (and apply opacity if required – I set this to 75%). Set the mark type to Shape and add State is unselected to shape. Use the Ratings shape palette to set the options.

We want the Tooltip to say something different, depending on whether the State/Province is selected or not.

Tooltip Include/Exclude

IF [State is unselected] THEN ‘include’ ELSE ‘exclude’ END

Add this to the Tooltip shelf, then update the text

We want to align this sheet with the bar chart, so we need to add another instance of MIN(0) to Columns. Make the chart dual axis and synchronise the axis. Remove Measure Names from the Colour shelf of both marks cards. Add Measure Names to the Rows shelf.

On the first MIN(0) marks card, remove all the fields from the card, reduce the size to as small as possible, and the opacity to 0%. Set the Shape to be a transparent shape (see this post for more details).

Now, we need a way to populate the pUnSelectedStates parameter with values from the selector sheet itself. Given the set of ‘unselected’ states can ‘build’ up, we can’t just pass in a single state from the State/Province field itself. We need to capture a delimited list of the states as they are unselected & reselected. We will use the | character as the delimiter, and will modify the string as follows

ActionpUnSelectedStates
All states selected<empty string>
1 state unselected eg Texas|Texas|
2 states unselected eg Texas then California|Texas||California|
1 state then reselected eg Texas then California unselected, Texas reselected|California|
1 state reselected again eg Texas then California unselected, Texas reselected, then California reselected<empty string>

This is captured within this logic

States for Param

IF CONTAINS([pUnSelectedStates], [State/Province]) THEN
//state already in parameter, so remove it
REPLACE([pUnSelectedStates],’|’ + [State/Province]+ ‘|’,”)
ELSE
//add state to string
[pUnSelectedStates] + ‘|’ + [State/Province] + ‘|’
END

If the State is already stored in the parameter, then remove it, by replacing the text with ” (empty string), otherwise append the state to the existing text in the parameter.

Add this field to the Detail shelf of the first MIN(0) marks card.

Hide all the axis and the row headings. Remove all row/column dividers, gridlines, zero lines, axis rulers etc. Reset the parameter back to an empty string.

Name the sheet Selector or similar.

Identifying if the bars are selected or not

Before we build the dashboard, we can now tweak the bar chart, so the colour of the bars reflects whether the State/Province is selected or not.

Back onto the bar chart sheet, add State is unselected to the Detail shelf of the Sales marks card. Then use the icon next to the pill and change it from Detail to Colour to add a secondary pill to the Colour shelf

Swap the pills around, so the State is unselected pill is above the State/Province pill, and the colour legend should list entries as False, <State>. Readjust the colours if need be.

Now enter ‘Texas’ into the pUnselectedStates parameter. The legend will update so that True,Texas is now listed. Adjust the colour of this option to white, and set a light grey border (via the Colour shelf), so the outline of the bar is visible.

Go through each listed State/Province adding it to the parameter and the adjusting the True, < State> colour legend to white each time.

Once done, set the pUnselectedStates parameter back to empty string.

Building the dashboard and adding the interactivity

Using containers, add the objects to a dashboard. I used a horizontal container to store the 3 charts, but the selector and the bar chart were also then contained within their own horizontal container. All charts were set to fit entire view. This allows for a border to be set around both objects. If everything is set right, you should be able to get the selector circles aligned with the bars.

I also used background colours and padding to get everything displayed as required.

Add a dashboard parameter action to capture the user interactivity

Unselect State

on select of the Selector sheet, update the pUnselectedStates parameter by passing the value of the States for Param field. Keep current value when unselected.

Finally to prevent the selected option from remaining ‘highlighted’ and all the other options becoming ‘faded’ out’, we can use the True/False trick described here.

My published viz is here.

Happy vizzin’!

Donna

Top & Bottom Variance

Donna ColesLeave a comment

For #WOW2024 Week 6, Sean challenged us to create a viz depicting the top and bottom entries based on a variable set by the user. He instructed that no LoDs (level of detail calculations) should be used, and then also added a bonus to complete the challenge without using sets either (and so hinting that sets would solve the problem). I built both, and will provide the solution for both.

Setting up the common fields

Regardless of the solution, various calculated fields and parameters are required.

pTop

integer parameter defaulted to 12, which is used to drive how many entities to display in our top & bottom cohorts.

For the user to select a month, I first created a new field

Month of Order Date

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

and I changed the default sort order of this field to descending (right click the field > default properties > sort > choose descending in the disalog box).

I then created a parameter

pSelectedMonth

date field that selects from a list that is populated when the workbook opens from the Month of Order Date field. Default to July 2021 and format the display to a custom format of mmmm yyyy – having defined the sort order to be descending, the most recent month will be at the top (this wasn’t in the requirements, but is just a useful trick to know).

Now we have a handle on the month we want to report over, we can create

Selected Month Sales

ZN(IF [Month of Order Date] = [pSelectedMonth] THEN [Sales] END)

Return the sales only if the month is that selected, otherwise return 0 (this is what the ZN function will do)

Format this to $ with 0dp.

Previous Month Sales

ZN(IF [Month of Order Date] = DATEADD(‘month’, -1, [pSelectedMonth]) THEN [Sales] END)

Return the sales only if the month matches the previous month to that selected, otherwise return 0.

Format this to $ with 0dp.

and with these, we can then create

Difference in Sales

SUM([Selected Month Sales]) – SUM([Previous Month Sales])

Format this to $ with 0dp too.

And we can also create

Colour – Diff > 0

[Difference in Sales] > 0

Pop all these into a table sorted by Difference in Sales ascending to see what it all looks like.

Solution 1 – Using sets

Right click on the State/Province field and create > set

Top States

using the Top condition to get the Top n States where n is the value from the pTop parameter, based on the Difference in Sales field.

and then create another set

Bottom States

using the Top condition to get the Bottom n States where n is the value from the pTop parameter, based on the Difference in Sales field.

Then right click on one of these states and create combined set

Top & Bottom

shows all members in both the Top States and the Bottom States sets

Add Top & Bottom to the Filter shelf. By default only the records ‘in’ either of the sets will display. Modify the pTop parameter to see the list change.

To build the viz

  • add State/Province to Rows
  • Add Difference in Sales to Columns and sort ascending
  • Add Top & Bottom to Filter
  • Add Colour – Dff > 0 to Colour and adjust accordingly.
  • Add Selected Month Sales to Tooltip and adjust
  • Show the pTop and pSelectedMonth parameters

To display the name of the month selected as a column header, create

Selected Month

[pSelectedMonth]

and add to Columns as a discrete (blue) pill at the month-year level. Hide field labels for rows and columns, remove all row/column dividers, zero lines, axis rules & tick marks.

Solution 2 – not using sets

This solution involves using the Rank table calculation

Create a new field

Asc

RANK_UNIQUE([Difference in Sales],’asc’)

and another called

Desc

RANK_UNIQUE([Difference in Sales],’desc’)

Revert back to the data table we built and remove the Top & Bottom set filter. Now add Asc and Desc to the output.

There are now two fields indexing each row; one which starts at 1 and increments, and the other that decrements so the last row is 1. We can now use these to filter just to the records we want

Records to Include

[Asc]<= [pTop] OR [Desc]<=[pTop]

Add this to the Filter shelf and set to True, and we can now see our top 12 and bottom 12 listed

Build the viz exactly as described above, but this time, add the Records to Include field onto the Filter shelf instead.

Pop these onto a dashboard and job done!

My published viz is here : Set Solution | Rank Solution

Happy vizzin’!

Donna

Excel at Tableau Interactivity with Dynamic Zone Visibility

Donna ColesLeave a comment

Erica set the #WOW2024 challenge this week, asking us to recreate this ‘drillable’ functionality within a single dashboard.

To achieve this, I used 4 sheets

  • A large map
  • A small map
  • A table of data
  • A navigational control

along with parameter actions to drive the interactivity, and dynamic zone visibility to control what displayed when.

Building the large map

On a new sheet, double click on State/Province to automatically generate a map of the United States – if this doesn’t display by default, then make sure the location of your map settings are referencing the USA (Map menu > Edit Locations). Hide the ‘unknown’ indicator displayed bottom right. Change the mark type to map to create a filled map. Change the colour to a grey (I used #c0c0c0). Add State/Province to the Filter shelf and select Arkansas. Show the filter.

Click on the Tooltip button, and delete all the text in the dialog.

Now drag City onto the map and drop it on the section that appears labelled Add a Marks Layer. On the ‘City’ marks card that now appears, change the mark type to circle. Move City to the Label shelf and then also add State/Province to Detail. Change the colour of the circles to dark grey (I used #767f8b).

Update the Tooltip on the City marks card. Turn off all the map options to stop the pan/zoom map control options from appearing (map menu -> map options -> deselect all the options). Remove all row/column dividers.

Name the sheet Map-Large or similar.

Building the Small Map

Duplicate the Map-Large sheet, and name the new sheet Map-Small. Remove all background layers so just the state outline remains (Map menu -> background layers -> set washout to 100%).

On the Label shelf of the City marks card, uncheck show mark layers so the name of the city is not displayed and click on the Tooltip shelf and uncheck show tooltips, so they aren’t displayed either.

On this map, we need to highlight a selected City. For this we need a parameter to capture the City, and a calculated field to identify which City has been selected. We will also need a parameter to capture the State/Province to ensure the interactivity works as required.

pSelectedState

string parameters defaulted to ” (ie empty string)

pSelectedCity

string parameter defaulted to ” (ie empty string)

Is Selected City

[City] = [pSelectedCity]

Add Is Selected City to the Colour shelf of the City marks card.

Show the pSelectedCity parameter and type in ‘Fayetteville’. The colour legend should display values for True & False. Adjust colours to suit.

Also add Is Selected City to the Size shelf and adjust the sizes accordingly.

Building the table

On a new sheet, add

  • State/Province to Filter and filter to ‘Arkansas’
  • Add Is Selected City to Filter and filter to True
  • Add Customer Name, Order ID and Product Name to Rows
  • Right-click on the State/Province field in the Filter shelf and apply to worksheets > selected worksheets and select the Large Map & Small Map sheets. This makes the filter shared between all those sheets.

Create a new field

Unit Price

SUM([Sales])/SUM([Quantity])

and add to Text.

Drag Quantity onto the canvas and drop on the ‘unit price’ column when you see show me appear. This will automatically add Measure Values to Columns and Measure Names to Filter.

Do the same for Sales. Reorder the pills in the Measure Values box so they are arranged as required.

Add subtotals (Analysis menu > Totals > Add all subtotals), then display at the top (Analysis menu > Totals > Column Totals to Top).

Right-click on the Customer Name pill in Rows and uncheck Subtotals from the context menu.

Format the table: set the worksheet background colour to light grey and set the total (pane & header) background colour to a shade darker.

Adjust the width and height of the columns and rows to suit.

Uncheck show tooltip from the Tooltip shelf, and name the sheet Table or similar.

Building the Navigation sheet

The ‘back to main map’ navigational display is actually a worksheet rather than a navigational object on a dashboard, as we need to apply parameter actions to it in order to reset some parameters.

On a new sheet, double click into the space on the marks card below the shelf buttons, and type ‘X’

Change the mark type to Text and move the X field to the Text shelf. Update the text to include the additional words, adjust the font size and then align middle centre. Uncheck Show tooltip.

Create the following fields

City – Reset

”

State – Reset

”

and add both to the Detail shelf. Name the sheet Navigation or similar.

Building the dashboard

Using horizontal and vertical layout containers, arrange all the objects on a dashboard. You will need to adjust the background colour of some objects and the padding to get everything looking as expected. This can be quite tricky and is very hard to explain in a blog. The item hierarchy in the image below will help give you an idea

To draw horizontal or vertical lines, add a blank object to the layout container, change the background colour, reduce the padding to 0 and then adjust the height or width of the object so it is very narrow.

These are the properties associated with the red ‘tab’ in the heading – the background colour is red, inner and outer padding is 0, width is 4px and the object is contained within a horizontal container

For the City/State title, reference the pSelectedState and pSelectedCity parameters, along with a unicode symbol which I copy and past from here.

Adding the interactivity

Add the following dashboard actions

Set State

on select of the Map-Large sheet, set the pSelectedState parameter passing in the value of the State/Province field. When the selection is cleared, retain that value.

Set City

on select of the Map-Large sheet, set the pSelectedCity parameter passing in the value of the City field. When the selection is cleared, retain that value.

Reset State

On select of the Navigation sheet, set the pSelectedState parameter passing in the value of the State – Reset field. When the selection is cleared, retain that value.

Reset City

On select of the Navigation sheet, set the pSelectedCity parameter passing in the value of the City- Reset field. When the selection is cleared, retain that value.

Click around on the dashboard and sense check the parameters appear to be behaving – ie if you click on a city on the large map sheet, the table and small map should update, and if you then click the navigation sheet, the table shouldn’t show anything, and the small map shouldn’t show anything highlighted.

Hide and show the objects

Finally, we need to only show the required objects depending on the actions taken. For this we need some additional calculated boolean fields. Go back to any sheet, and create the following calculated fields

City is Selected

[pSelectedCity]<>”

City Not Selected

[pSelectedCity]=”

Note, these fields just have to exist, they don’t need adding to any sheet.

Navigate back to the dashboard.

Click on the Map-Large object, and on the Layout tab, check the control visibility using value option, and choose the City Not Selected value.

This means that when City Not Selected is true (ie the pSelectedCity parameter is empty), this object will display. When City Not Selected is false (ie the pSelectedCity parameter has a value), this object will be hidden.

Depending on what you’ve been clicking, this object might disappear immediately.

Click on the Table object, and do the same steps, but this time choose the City is Selected field. This means the table will only show then the pSelectedCity parameter has a value.

Apply the same settings so the small map, the table title, and the navigation sheet only show when City is Selected. Note – depending on how you have built the dashboard, you may find you can apply the setting once against a container which contains all the objects you want to hide rather than against each individual object.

I also chose to only display the Select a State filter control when the City Not Selected was true.

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

Happy vizzin’!

Donna

Can you create donut charts exceeding 100%?

Donna ColesLeave a comment

In 2024 the #WOW coaching crew has extended to include Yusuke Nakanishi (@YusukeNakanish3) and Yoshitaka Arakawa (@yoshi_dataviz) from the Japanese #datafam. This week Yusuke set this challenge asking us to build donut charts that represented when percentages were > 100%.

Building out the calculations

In the data set provided the Self-sufficiency ratio for food in calorie base [%] is a number that represents the actual % value ie 90 means 90%, 196 means 196%. To help me remember that, I formatted the field to be a number with 0 decimal places which had a % suffix.

But building the donut charts we need to ‘normalise’ the figures to represent a percentage out of 100. ie, if the value is 90%, we want 90%, but if the value is 196%, we want 96%. So I created

Self Sufficient %

([Self-sufficiency ratio for food in calorie base 【%】] %100) / 100

and then formatted this to a % with 0 dp.

but I can’t build a donut chart with just this value, I need to know the non self sufficient % too

Not Self Sufficient %

1-[Self Sufficient %]

and formatted this to be a % with 0 dp too.

Let’s put these into a tabular view. Add Prefecture to Rows and Measure Names to Columns. Add Measure Values to Text. Add Measure Names to Filter and restrict to the fields Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient %. Add Fiscal Year to Filters and restrict to FY2018. Sort the data by Self-sufficiency ratio for food in calorie base [%] descending.

You can see those rows where the Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient % is over 100% have a different value for the Self Sufficient %.

Showing the Fiscal Year as a filter the user can select, I can change to FY2019 and also see how these fields are behaving; ie when Self-sufficiency ratio for food in calorie base [%] is also over 200%, I’m getting the ‘remainder’ over 200 displayed ie 216% has a Self Sufficient % of 16%, which is what is needed for the ‘bonus’ challenge.

However, due to the way I’m building, I’m going to able to get the display working for the top & bottom 7 records regardless of year (making the build of the bonus challenge a bit easier).

So with this in mind, I now what to categorise the Self-sufficiency ratio for food in calorie base [%] based on what percentage range the values fall into.

% Bracket

FLOOR([Self-sufficiency ratio for food in calorie base 【%】] / 100)

This gives me values of 0, 1 and 2. By default this field will be created within the ‘measures’ section of the data pane (ie below the line). Drag it into the top section to convert it to a discrete dimension. Alias the values (right click -> Aliases) and set as below:

Then add to Rows.

and with the filter still set for FY2019, we can see the rows are categorised into 3 brackets.

Set the filter back to 2018. Now we want to restrict to the top and bottom 7 records only. For this I use Sets.

Create a set against Prefecture (right click the field > create > set).

Top 7

Top 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]

Then create another set, this time for the bottom 7

Bottom 7

Bottom 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]

Then create a Combined set (right click on one of the sets > create Combined set), that includes all values from both sets

Records to Include

Add this to the Filter shelf. By default it will show the records ‘in’ the set. However based on how the order of operations in Tableau works, this will apply the conditions based on the set first before it considers the year being filtered. Ie it will get the top and bottom 7 records based on the total sum of the Self-sufficiency ratio for food in calorie base [%] field for all the data in the data set, then filter to 2018. It means it could show some records that were in the top 7 overall, but not in the top 7 for 2018. To resolve this, and to ensure the data gets filtered by the Fiscal Year first, we need to add the Fiscal Year pill on the Filter shelf to context (right click pill and Add to Context). The pill will go grey.

Next we need a way to ‘categorise’ which rows are the top and which are the bottom. Add Top 7 to Rows which will split the rows into In or Out. Alias these values so In displays as Top 7 and Out displays as Bottom 7 (right click the text > Edit Alias).

Finally, when we build the viz, we need to ensure the 7 entries for each section align with each other. For this create

Top | Bottom Index

INDEX()

and convert the field to Discrete, then add to Rows before the Prefecture pill. Edit the table calculation so that it is computing based on the Prefecture and % Bracket pills only. This gives us an index from 1-7 for each set.

Building the Top & Bottom 7 Donut chart

On a new sheet, add Fiscal Year to Filter and set to 2018. Add the pill to context. Show the filter. Also add Records to Include to Filter.

Add Top 7 to Rows, then double click into rows and manually type MIN(0) to create a ‘fake axis’. Change the mark type to Pie.

Add Prefecture to Detail and Measure Values to Angle. Ensure only Self Sufficient % and Not Self Sufficient % are the only measures displayed (remove any others by dragging them out of the Measure Values box). Add Measure Names to Colour.

Add Top | Bottom Index to Columns and edit the table calculation so it is just computing by Prefecture. This should now give 7 columns of pie charts.

Add a Sort to the Prefecture pill on the Detail shelf, so it is sorting by the Self-sufficiency ratio for food in calorie base [%] field descending.

Add % Bracket to the Detail shelf, then click on the 3 dot icon to the left, and select the colour icon to add this pill to the Colour shelf as well as the Measure Names pill.

Re-edit the table calculation associated to the Top | Bottom Index pill so it is now also computing by the % Bracket field, so you get back to your correct top & bottom 7.

Adjust the order of the pills on the Colour shelf so that the % Bracket pill is listed before the Measure Names pill. Also adjust the order of the pills in the Measure Values box so Self Sufficient % is listed before Not Self Sufficient %.

Then adjust the colours on the colour legend as below and add a dark border to the marks (via the colour shelf).

Change the year filter to FY2019, and you can adjust the colours for the 200% entries too.

To make the ‘hole’ in the donut, double click into the Rows and add another instance of MIN(0). This will create a 2nd MIN(0) marks card.

On that marks card, move Prefecture from Detail to Label. Remove % Bracket, Measure Names and Measure Values. Add Self-sufficiency ratio for food in calorie base [%] to Label. Change the mark type to circle. Set the Colour of the circle to white. Align the label middle centre, and adjust the format/font to suit.

Set the chart to be dual axis and synchronise the axis. Adjust the Size of the pie chart mark independently from the size of the circle mark, so one is slightly larger than the other.

Finally tidy up the display by hiding the axis (uncheck show header), and the Top | Bottom Index field. Remove all gridlines, zero lines, axis lines/ticks and row/column dividers. Hide the In/Out Top 7 field label heading (right click label and hide field labels for rows. Update the title of the sheet so it references the Fiscal Year field. Change the filter back to 2018. Hide all tooltips.

Building the Legend

On a new sheet double click into Columns and type MIN(0.0). Add % Bracket to Rows. Change the mark type to square and add % Bracket to Colour and Label. Adjust colours to suit and add dark border to shape.

From the donut chart sheet, click on the Fiscal Year pill in the Filter shelf and set to apply to worksheets > selected worksheets and select the sheet you’re building the legend on. This will add the pill to this sheet too and changing the value on one sheet will impact the other.

Edit the axis so it is fixed to start at -0.1 and end at 1. This will shift the display to the left.

Hide the axis, and the % Bracket header column; remove all gridlines, zero lines, row/column dividers. Hide the tooltips.

Both the legend and the donut can now be added to a dashboard, and you’ve completed the main challenge. I chose to add the Fiscal Year filter to the display so the user could switch years if they wished.

The bonus challenge – Building the trellis chart

Because of how I’ve built the above, we’ve already done the hard work to handle the 200%+ data. The challenge here now is just to display all the 47 Prefectures for a given year in a 10 x 5 grid – a trellis.

So to build this, I started with the donut chart already built and duplicated the sheet.

When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created

Cols

INT((INDEX()-1)%10)

Take the index of each Prefecture, decrement by 1 and find the remainder when divided by 10. This means the Prefecture with the highest % value at at position (rank) 1 will be positioned in column (1-0)%10 = 0. The Prefecture at position 11 will also be positioned in column (11-1)%10 = 0.

We also need

Rows

INT((INDEX()-1)/10)

Take the index of each Prefecture, decrement by 1 and divide by 10.

Convert both fields to be Discrete.

From the duplicated sheet, remove Top | Bottom Index from Columns and In/Out Top 7 from Rows. Remove Records to Include from Filter. Don’t panic if things look odd!

Add Cols to Columns. Adjust the table calculation to compute by both %Bracket and Prefecture. Specify the sort order to be a custom sort on the field Self-sufficiency ratio for food in calorie base [%] descending.

Now add Rows to Rows and apply the same settings on the table calculation. If all is well, you should have all 47 donuts displaying in the correct order.

Hide the Cols and Rows headings, and you can then add this to another dashboard.

My published viz is here: Top & Bottom 7 | Trellis

Happy vizzin’!

Donna

Can you dynamically zoom into a clustered area?

Donna ColesLeave a comment

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

Modelling the data

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

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

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

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

The data provided consists of 4 files

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

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

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

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

I then renamed each column as

  • Country
  • Year
  • Life Expectancy

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

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

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

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

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

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

Building the Scatter Plot

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

pToday

date parameter defaulted to 16 Jan 2024

I then created a field

Year <= Current Year

[Year] <= YEAR([pToday])

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

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

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

Region

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

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

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

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

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

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

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

pIncomeMin

integer parameter defaulted to 500

pIncomeMax

integer parameter defaulted to 100,000

pLifeExpectancyMin

integer parameter defaulted to 0

pLifeExpectancyMax

integer parameter defaulted to 100

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

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

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

Building the Viz in Tooltip

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

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

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

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

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

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

Building the Legend

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

Name the sheet Regions or similar.

Adding the interactivity

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

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

Filter Region

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

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

Income-MinSelected

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

Income-Max Selected

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

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

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

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

Happy vizzin’!

Donna

Can you create a time-focused Gantt chart?

Donna ColesLeave a comment

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

Building the Marketing Campaigns Gantt chart

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

pHighlightDate

date parameter defaulted to 01 Dec 2023

and

pDays

integer parameter defaulted to 120

On a new sheet, show both of these parameters.

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

To define the width of each mark, we need

Duration

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

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

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

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

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

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

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

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

Window Start

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

Window End

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

and then

Campaigns to Include

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

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

Building the Experiments Gantt Chart

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

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

Duration

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

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

Experiments to Include

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

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

Building the Emails bar chart

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

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

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

Emails to Include

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

and add to the Filter shelf, set to True.

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

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

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

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

Happy vizzin’!

Donna