Monthly Sales vs Targets – the #data22 Vegas Edition

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

Sales Budget “Burndown” Chart

Erica Hughes had a table-calc-tastic challenge for us this week! I was using Tableau before LoDs were invented, so became very familiar with the ‘dark art’ of table calculations, and subconsciously often turn to these first when solving problems. Since the advent of LoDs, this functionality can get forgotten about, so this is a great challenge to help flex those table calc muscles and become a good reference point.

With any table calc challenge, I work out what I need in a tabular form before even attempting any visual, so that’s where we’ll start today.

I also tend to ‘build up’ the calculated fields I need, as this helps me validate the data I’m working with. It means I end up with more calculated fields than absolutely necessary, but it’s a good practice to get in as it eases troubleshooting in the long run.

In this example, we need to treat Sales as if it’s a budget that is then being ‘spent’ over the course of the following months. The first thing we need to define is the total budget

Total Sales

TOTAL(SUM([Sales]))

We then need to understand the cumulative (running sum) of Sales per month.

Running Sum Sales

RUNNING_SUM(SUM([Sales]))

Notethis calculation can be achieved by applying a Quick Table Calculation to the Sales pill once added to a view, but I will need to reference the values in other calculated fields, so created the field directly.

Let’s start building out our table of data to see what’s going on with these calculations.

Add Order Date to Rows and set so its displaying the date in the Month Year format

Then add Total Sales, Sales and Running Sum Sales

The Total Sales column will be the same for every row and match the same value in the last row of the Running Sum Sales column. The value in each Running Sum Sales row is the sum of the Sales value in the same and all preceding rows.

By default the table calculations are working ‘down’ the table which gives the desired result, but I tend to ‘fix’ the field the calculation is computing over, as when we build the viz we won’t be going ‘down’, but ‘across’, so fixing helps ensure we get all our settings just right.

So edit the table calculation of both the Total Sales and the Running Sum Sales fields to compute using Month of Order Date.

With these fields, we can calculate the ‘sales budget’ value being displayed as

Total Less Running Sum Sales

[Total Sales]- [Running Sum Sales]

Pop this on the view and verify the table calculation settings are set as above (this field contains nested calcs, so check each setting). You should be able to verify the value of this column is the result of the 1st column – 3rd column values

But there’s a twist.

The requirements state that “for dates in the future, the sales budget should remain constant”.

For this we need to work out what month ‘today’ is in. For this task, I have hard coded ‘today’ into a parameter called Today and set to 8th March 2022. If this was a ‘real’ production business dashboard, I’d just refer to the function TODAY() directly.

I can then work out

Current Month

DATETRUNC(‘month’, [Today])

which will return 01 March 2022 in this instance.

I then want to identify the record that matches the current month

Is Current Month?

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

which just returns a boolean True/False.

And with this, I can then determine the value from the Total Less Running Sum Sales column that is associated to March 2022, and ‘spread’ that value across every row in the view.

Curr Month: Total Less Running Sum Sales

WINDOW_MAX(IF ATTR([Is Current Month]) THEN [Total Less Running Sum Sales] END)

If the month in the row is the current month, get the required value and ‘spread’ over every other row using WINDOW_MAX. Add this to the view, checking your table calc settings again.

Now we can work out the values needed for the Sales Budget line

Sales Budget

IF MIN(DATETRUNC(‘month’, [Order Date])) > MIN(DATE([Current Month])) THEN [Curr Month: Total Sales Less Running Sum Sales] ELSE [Total Less Running Sum Sales] END

Format this to $, Millions (M), 1dp.

If the month is later than the current month, use the value associated to the current month, otherwise use the Total Less Running Sum Sales value. Note here, the date functions are wrapped within a MIN function as the other fields are table calculations which means they’ve been aggregated, so all other fields referenced need to be aggregated to. The function MAX will have worked just as well.

Phew! we’ve finally got the data we need for the first line :-). As mentioned earlier, this can be achieved by combining some of the logic in the calcs, but I like to be methodical and verify my numbers give me what I expect at each stage.

In order to display the Estimated Budget line, we need to first work out how much of the total sales would be spent each month, if the same amount was spent each month – ie Total Sales divided by number of months.

Size (Count of Months)

SIZE()

I just chose to use the SIZE() table calculation to essentially count the number of rows in my view.

Estimated Budget Constant

//average the sales over the total months to get a constant budget
[Total Sales] / [Size (Count of Months)]

Add these into the view and adjust the table calc settings as before

essentially Total Sales (2,297,201) / Count of Months (48) = Estimated Budget Constant (47,858).

Like before, we need to compute running sum of this estimated budget

Running Sum Est Budget

RUNNING_SUM([Estimated Budget Constant])

and then we can calculate the Estimated Budget

Total Less Running Sum Est Budget

[Total Sales] – [Running Sum Est Budget]

This now gives us the data to plot the 2nd line.

The final calculation we need for the tooltip is the difference between the sales budget and estimated budget

Difference to Estimated

[Sales Budget]- [Total Less Estimated Running Sum]

format this using a custom format of +”$”#,##0,K;-“$”#,##0,K

Now we can build the viz! I tend to keep sheets like above in any workbook as a ‘check sheet’ if I need to do any troubleshooting later on.

So on a new sheet, add Order Date to Rows and set to be a continuous (green) month/year format this time. Add Sales Budget to Columns. Adjust the table calculation so all nested calculations are computing by Order Date.

Add Total Less Estimated Running Sum to Columns (set the table calc settings), then change to dual axis and synchronise axis.

Remove Measure Names from the All Marks card to remove the colours that have been set. Change the Colour of the Sales Budget line to purple, and set the markers to have circles.

From the Analytics tab, drag a Trend Line to the canvas and drop it as a linear trend on the Total Less Estimated Running Sum measure

This will add the ‘dotted’ line.

On the Total Less Estimated Running Sum marks card, set the Opacity of the Colour to 0%, so only the grey dotted trend line is visible.

Edit the trendline and uncheck Show recalculated line for highlighted or selected data points

Add Current Month to the Detail shelf of the All Marks card, and set to the month/year format. Then right click on the Order Date axis and Add Reference Line, setting the values as below

Right click on the reference line, and Format; adjust the alignment and font size & colour, so ‘Future’ is listed at the top.

Add Difference to Estimated onto the Tooltip of the Sales Budget card (adjust those table calc settings). The format the tooltip accordingly.

Add Curr Month: Total Sales Less Running Sum Sales to the Detail shelf of the All Marks Card (adjust those table calc settings). Edit the title of the viz

Finally tidy up the display by removing axis, row and column banding etc, and adjust the Sales Budget axis so it displays every 500,000.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

How many patients were admitted every 15 minutes?

This week, Luke set the challenge which is focussed on manipulating time. Medical admissions over many days is represented in a bar chart which spans a 24 hour period. All admissions needs to be ‘bucketed’ into 15 minute intervals over the 24 hours ie admissions between midnight and 12:14am is counted within the same 12:00am ‘bucket’.

The data for this challenge is embedded within a workbook which you need to download via the challenge page. I did as Luke instructed; downloaded the workbook, deleted all the existing sheets, then re-saved as my own file.

The first step that is required is to ‘baseline’ / normalise the admission dates so they all look to be on the same day.

There’s different ways to do this; on this occasion I used the 3rd method from this Tableau KB, although I simply hardcoded a date of 1 Jan 2023 rather than use TODAY(). It doesn’t matter what this particular date is, its just an arbitrary date.

Baseline Admission Date

DATEADD(‘day’, DATEDIFF(‘day’, [Admission Date], #2023-01-01#), [Admission Date])

Once we’ve got this, we then need to manipulate this date again to ‘group’ into the 15 min interval. This isn’t something I know ‘just to do’, but I know I’ve done it before. So a quick google was needed and I used this blog for the required calculation.

Baseline Admission Date 15 mins

DATETIME((INT(FLOAT([Baseline Admission Date])*96))/96)

Pop these fields out into a table to see how these calculated fields are working

The Baseline Admission Date 15 mins is what we’ll use for the x-axis. The next step is work out the value being plotted Stays per Day.

Now when the challenge was first placed, a couple of the requirements were missing, so there was a bit of head-scratching trying to figure out what numbers were being used to get the values presented.

The following fields need to be added to the Filter shelf:

  • Stay Type = Outpatient
  • Admission Date starting from 30 Aug 2017 00:00

When on the filters shelf, both these should then be added to Context, as the data needs to be filtered before the LOD calc we need to use gets calculated (defined below).

Count Days with 15 min interval

{FIXED [Baseline Admission Date 15 mins]: COUNTD(DATETRUNC(‘day’, [Admission Date]))}

This is counting the distinct days when there was admission within each 15 minute period ie if there were 2 admissions on the same day within the same 15 minute window, the day would only count as 1.

From this we can then compute

Stays per Day

SUM([Number of Records])/SUM([Count Days with 15 min interval])

Now we’ve got the data we need, so we can build the viz.

Add Baseline Admission Date 15 mins to Columns as a green continuous pill, and Stays per Day to Rows. Don’t forget to add Admissions Date and Stay Type to the Filter shelf as mentioned above.

The bars look ‘blocky’. You can manually adjust the size, but you might notice that the widths between isn’t exact – the whitespace looks larger between some bars than others. To resolve this, I created a field to control the size, which is based on the number of 15 minute intervals there are in a 24 hour period – 96.

Size

1/96

Add this to the Size shelf, change the aggregation to MIN, and adjust the size to be Fixed and aligned Centre.

Add another instance of Stays per Day to the Rows shelf. Then make it dual axis and synchronise axis. Change the mark type of the 2nd Stays per Day instance to Gantt and change the colour. Then change the colour of the 1st Stays per Day.

Show mark labels, and set the Label just to show the max value.

Right click on the time axis, and format, and custom format to h:nn am/pm

And essentially, that’s it. There’s formatting to do to remove the secondary axis, column & row banding etc and add tooltips, but the core of the viz is complete.

My published instance is here. Note the time formatting seems to be an issue on Tableau Public. Someone did comment that this was an issue with the MAKETIME function, but I didn’t actually use this function.

Happy vizzin’! Stay Safe!

Donna

How have successful products performed?

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

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

Defining the calculations

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

Count Products

COUNTD([Product Name])

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

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

Profit per Product & Year

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

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

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

Is Profitable per Year?

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

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

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

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

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

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

Profitable Products

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

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

Count Successful Products

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

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

And now we can compute the percentage of successful products

Pct. Successful Products

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

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

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

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

Profit Ratio

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

formatted to % with 1 dp.

Building the viz

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

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

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

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

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

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

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

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

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

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Tableau Coaches’ Favourite Challenges Advent Calendar

Candra set this seasonal fun challenge this week. It may not deliver data visualisation best practice, but as a ‘seasoned’ #WOW participant, this provided a chance to use a technique I rarely use (background images) and learn a brand new technique (colouring a bar chart with a gradient).

Getting set up

To start you’ll need to download all the files Candra provided, which will consist of the data (across 2 sheets) and 3 images.

The bauble and star image will need to be saved into your Tableau Shapes repository – I copied them into a new folder I called Xmas in this directory ..\My Tableau Repository\Shapes

The tree image just needs to be saved somewhere you’ll remember.

Building the Tree Chart

Connect to Sheet1 of the downloaded WOW Advent Calendar excel file.

Drag X to Rows and Y to Columns and disaggregate the marks (Analysis -> uncheck Aggregrate Measures)

You should get the basic layout of where the baubles are positioned. You may also notice that the X & Y values in the dataset were labelled wrong. The x-axis is your horizontal axis, but these have been stored in the Y field, while the vertical y-axis values have been stored in the X field. You may wish to rename these to save you getting confused.

Add Day to the Label shelf, and align middle centre.

We need to distinguish the bauble marks from the star mark. The star mark has a NULL value for the Day field, so we can utilise that

Day Is NULL

ISNULL([Day])

Add this onto the Shape shelf, and adjust the shape images to source the new images you saved into your shapes repository (if you can’t find them try clicking ‘reload shapes’). Adjust the initial size of the images via the slider on the Size shelf.

Now add Day Is Null to the Size shelf too. Edit the size and adjust the mark size range to suit, so the star is larger than the baubles.

Adjust the Label of the bauble so the text is larger and white.

To make the tooltip slightly more readable than that in the provided solution, I created my own custom version

Tooltip

IF [Day is Null] THEN “It’s Christmas Day!”
ELSEIF [Day]=1 THEN STR([Day]) + ” day until Christmas”
ELSE STR([Day]) + ” days until Christmas”
END

Add this to the Tooltip shelf, and adjust so it’s just referencing this field.

Add Link to the Detail shelf – this will be needed for the interactivity later.

Now we’ll add the background image via the Maps -> Add Background Image -> <datasource> menu option. Click to add image, then browse to where you’ve saved the Xmas tree image.

We need to define the min/max coordinates for where the tree image should be positioned, and based on this, we want the horizontal X axis (the Y field) to range from 0-4 and the vertical Y axis (the X field) to range from 0 to 6. Press the Apply button and you see whether things look to be placed correctly before you close all the dialog windows.

Next we need to fix the axis, so that when interacting later, we keep the whole tree visible. The vertical axis should be fixed from 0-7, and the horizontal axis should be fixed from 0 to 4.

Then we need to remove all the axis from displaying (uncheck show header), and all row/column borders and gridlines and axis rulers.

The final step is to remove the map options from displaying when you hover over the chart – Map ->Map Options and uncheck all the options displayed in the dialog

Building the Gradient Coloured Bar Chart

Candra provided a hint in her instructions pointing to the Flerlage Twins blog. A quick search on the site for the keyword ‘gradient’, and I landed on this post from 3 years ago https://www.flerlagetwins.com/2019/02/gradient-colors.html.

Since the process is already documented, I don’t have to write all the steps out myself 🙂 I’ll just point out the fields I used/created, since we’re working with a different data set.

Firstly I created a new data source for this chart, which combined Sheet1 with the Range sheet, and used a relationship calculation of 1 = 1 to combine the data (Relationships didn’t exist when the blog post was written).

The equivalent of the Sales Adjusted field is the existing pre-computed Sheet1(Count) field, so we don’t need any calculated field for this.

Max Segments in my solution is

Max Range

WINDOW_MAX(MAX([Range (Range)]))

Note – Range (Range) is referencing the Range field from the Range table. If you just drag the field into the calculation window, it will automatically present in this way.

Total in my solution is

Total Count

TOTAL(COUNT([Sheet1]))

Size in my solution is

Size

[Total Count]/[Max Range]

and finally Color in my solution is

Colour

([Max Range]-[Index]) * [Size]

Add Challenge Year to Rows (rather than Category), change the mark type to bar if it doesn’t change automatically. Reverse the axis so 2018 is listed at the top, then change the Challenge Year pill to be discrete (blue) rather than green.

If you follow the other blog post through, you should hopefully end up with

You then need to show the labels and adjust the tooltip against the CNT(Sheet1) marks card only.

As before, remove all gridlines borders etc.

Adding the interactivity

Add the 2 sheets to a dashboard. We need to allow the tree to filter on click of the bar chart. I used the ‘use as filter’ option on the context menu of the the bar chart object to quickly set this.

This adds a filter action to the dashboard, but is a quick way of getting it created rather than having to manually set it up via the dashboard -> actions menu.

I did use this menu though to add the URL action to the Tree sheet, which just needs to reference the Link to the Challenge field.

So a relatively short blog this week, since the most complicated section is already written up be Ken Flerlage (thanks Ken!). It was a great fun little challenge, which still allowed for plenty of learning opportunities. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Thanksgiving Day NFL Games

Sean Miller posted this week’s challenge based on the results of the annual NFL games hosted on Thanksgiving Day. It immediately reminded me of a previous #WOW challenge that Lorna posted in 2019 when she visualised Rugby League wins (see my viz here).

This is a table calculations based challenge. I did start using FIXED LoDs to help calculate the summary measures (Total Games and Win %) displayed at the front, but found that as there are 2 years (1975 and 1977) when the Dallas Cowboys did not host a game, I ended up with some pesky NULL values displaying which affected how the running sum area chart displayed.

Defining the calculations

As its a table calc challenge, I’ll build out what I can into a table to start with, to sense check I’m getting the correct numbers.

First up add Home Team, Game Date and Visiting Team to Rows and display Home Score and Visiting Score.

We start by determining the result of the fixture, based on whether it’s a home or away win or a tie. In the lollipop chart home wins are plotted at 1 and away wins at -1, so we’re going to store the result as a numeric value rather than text.

Result

FLOAT(IF [Home Score]>[Visiting Score] THEN 1
ELSEIF [Home Score]<[Visiting Score] THEN -1
ELSE 0 END)

The output is wrapped within a FLOAT, as this will help how the axis displays. Without it, by default Tableau will define the field to be a whole number, and the axis will extend to +/-2 which is too much room. We can’t adjust (fix) the axis to a decimal if the field itself is an integer, and adjusting to +/-1 chops off the displayed marks.

If you add this to the display, it will show 1, 0 -1 as you expect. You’ll notice though that the Axis on the lollipop chart is labelled as Win/Loss. This is achieved by applying a custom format to the field – “Win”;”Loss”;”Tie”

This is a sneaky but effective trick. The information stated before the first semi-colon applies to positive numbers, the info after the first semi-colon applied to negative numbers, and the information after the optional second semi-colon applies to zero.

Unfortunately though, it would appear that, at the point of writing, Tableau Public, isn’t honoring the zero formatting, and is displaying Win rather than Tie. The display works on Desktop though.

The win/loss/tie text is just a formatting feature and affects what is displayed, but the underlying value is still a number.

The Result field will be used to plot the lollipop chart. We now want a field to plot the area chart against. This is a running total of the Result values (ie win =1, win, win = 1+1, win, win, loss = 1+1 -1) and we need a table calculation.

However, as stated above due to a couple of missing years, I had to make an adjustment to ensure the running total displayed as Sean had in his challenge. I created another field

Result Adjusted

IIFNULL(SUM([Result]),0)

If the Result field doesn’t exist, as there is no data, then use 0 instead.

To see what’s going on, we’re going to need a different view of the data where the date field is continuous (green) rather than discrete (blue).

Build the below, and filter just for the first 10 years – you’ll see the gaps where the are no marks in 1975 and 1977 for Dallas

Use the context menu of the green YEAR(Game Date) pill and select the option to Show Missing Values. Marks will now display

Add Result to Label. Each mark is labelled Win or Loss, except the ones for Dallas for 1975 & 1977 as there is no data

Now add Result Adjusted to Label. A 0 value is now displayed against those two marks.

We can now build a running total off of this measure instead

Running Total Wins

RUNNING_SUM(([Result Adjusted]))

Add this to the Label too and verify the table calculation is computing by the Game Date field only. The running total for the 2 ‘missing’ dates is displaying a value which is the same as the previous value (since we’ve added 0 onto the running total). This will give us the flat line in the area chart when we come to build it.

Now back to our table of data, we can focus on the other calculated fields we need….

Total Games

WINDOW_COUNT(COUNTD([Game Date]))

This is a table calculation and is simply counting the number of distinct dates displayed. Add this to the table display we were building to start with, and adjust the table calculation to compute by all fields except Home Team. The total should display the same value for all the rows against each Home Team.

Next we want a field to indicate if the row is a win.

Is Win?

INT([Home Score]>[Visiting Score])

This is taking a boolean of true or false and converting to an INT (1 or 0).

From this we can work out the Win rate

Win %

WINDOW_SUM(SUM([Is Win?]))/[Total Games]

Add up all the Is Win? values associated to the Home Team as a proportion of the Total Games played. Format this field to a percentage with 0 dp. Again, add to the table and adjust the table calc to compute by all fields except Home Team, and verify the same settings applied to both the calculations nested in this calculation

For the All-Time Record, we need to know the number of wins and number of losses. We have a field to help us with the wins, but need an equivalent for the losses

Is Loss?

INT([Home Score]<[Visiting Score])

And from this we can work out

All-Time Record

STR({FIXED [Home Team]: SUM([Is Win?])}) + ‘-‘ +
STR({FIXED [Home Team]: SUM([Is Loss?])})

This is the one field I kept from my LoD based attempt.

The circles on the lollipop chart are coloured based on the difference in the score, so lets’s create that

Score Difference

[Home Score]-[Visiting Score]

And finally we need some fields to help display the tooltips properly. The tooltip indicates whether the result was ‘won’ or ‘lost’ which is different text to the axis labels.

TOOLTIP-Result

IF [Result]=1 THEN ‘won’
ELSEIF [Result]=-1 THEN ‘lost’
ELSE ‘tied’
END

The tooltip also displays the scores, but the scores are always presented as highest score – lowest score and not home score – visiting score. So we need fields to store the right values

TOOLTIPHigher Score

IF [Is Win?]=1 THEN [Home Score] ELSE [Visiting Score] END

TOOLTIP – Lower Score

IF [Is Loss?]=1 THEN [Home Score] ELSE [Visiting Score] END

Pop all these fields out onto the table, so you can validate you’ve got all your calcs right before building the viz.

Building the area chart

Add Home Team to Rows, Game Date (continuous, show missing values) to Columns and Running Total Wins to Rows (ensure table calculation set as required). Change to mark type of Area. You should have 2 horizontal lines from 1974-1975 and 1976-1977 against the Dallas Cowboys row.

Adjust the tooltip, edit the label of the Running Total Wins axis , and remove the label of the Game Date axis.

Building the lollipop chart

Now add Result to Rows directly after the Home Team pill. Change the mark type to circle.

Add Score Difference to the Colour shelf of the circle mark, and adjust the starting colour range to a dark grey. Readjust the colour of the area chart to blue too. Add a border to the area chart too (via the colour shelf).

Add another instance of Result to the Rows shelf, next to the existing one. Set the mark type of this to bar. Reduce the size to the smallest possible, set the colour to grey and remove the border.

Now set this to be dual axis, synchronise the axis, and set the marks of the 2nd Result axis displayed on the right hand side to move marks to back. Uncheck Show Header to remove this axis from displaying.

Add Visiting Team, TOOLTIP-Result, TOOLTIP-Higher Score and TOOLTIP-Lower Score to the Tooltip shelf of both the Result marks cards, and adjust the tooltip on both to

Remove the Column dividers.

Now drag Total Games to Rows and drop next to the Home Team field. Change to be discrete (blue). Verify the number is what you expect and adjust the table calc if need be.

Add All-Time Record and Win % (set to discrete) to the view too. Then format these 4 fields so the text is larger and aligned centrally.

All that’s left now is to add the sheet to a dashboard. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you recreate this difference chart?

Lorna created this challenge for #WOW2021 this week incorporating tips from the Speed Tipping session she and fellow WOW leader Ann Jackson had presented at TC21.

Defining the calculations

The requirements were to ensure there were only 7 calculated fields used, and no date hardcoding (including in the title – a feature I missed to start with). So let’s start by just going through the required calculations.

We need to identify the latest year in the data set

Current Year

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

This uses an LoD (Level of Detail) calculation to identify the maximum date in the whole data set, which is 31st Dec 2021, and then extracts the Year of this ie 2021.

From this, we work out

Previous Year

[Current Year] – 1

Both of these fields return numbers, so automatically sit in the measures section of the left hand data pane (ie under the horizontal line). I want to treat these as dimensions, so I just drag the fields above the line.

We now need to create dedicated fields to store the Sales values for both years

CY Sales

IF YEAR([Order Date])=[Current Year] THEN [Sales] END

PY Sales

IF YEAR([Order Date])=[Previous Year] THEN [Sales] END

and with both of these, we can work out the

Difference

SUM([CY Sales])-SUM([PY Sales])

[TIP] This is custom formatted to △#,##0;▽#,##0.

I googled ‘UTF 8 triangles’ and used this link to find the suitable shapes which I just copied and pasted into the number format field.

We’re going to need to determine whether the difference is positive or not.

Is Loss?

MAX(0,[Difference]) =0

This is another [TIP] making use of the array function. If the Difference is negative, it will return 0 as this is the maximum of the two numbers. I’m not entirely sure if this is more efficient than simply writing Difference<=0, but I wanted to incorporate another of the tips presented.

The final calculation we need is another of the PY Sales field, as we need another distinct Measure Name value to display. I simply chose to duplicate the existing field to have a PY Sales (copy) field.

Building the viz

Add Category to Columns, Segment to Rows and then add CY Sales to Columns, which will create a horizontal bar chart. Then drag PY Sales to the CY Sales axis, and when the ‘two columns’ icon appears, drop the field.

This will automatically change the pills so Measure Values is on Columns and Measure Names is on Rows.

Swap the order of the pills on the Measure Values section on the left hand side, so PY Sales is listed before CY Sales.

Add Measure Names to the Colour shelf and adjust. Increase the width of the rows.

Check the Show Mark Labels option on the Label shelf and adjust alignment to display the text to the left

Increase the Size of the bars to the maximum size, and add a white border (via option on Colour shelf)

Add PY Sales (Copy) to Columns, and change the mark type to Gantt Bar. Remove Measure Names from the Colour shelf of this marks card, as it will automatically have been added. Instead add the Is Loss? field to Colour and adjust.

Add Difference to the Size shelf, then click on Size, and reduce it to as small as possible. Set the border of this mark to Automatic (it should become a little thicker).

Next add the Difference field to the Label shelf, align right and set the font colour to match mark colour.

Now make the chart dual axis, synchronise axis, and set the mark type of the Measure Names mark type back to a bar.

On the All marks card, add CY Sales, PY Sales and Difference to the Tooltip shelf. And add Current Year and Previous Year to the Detail shelf.

Adjust the Tooltip against the All marks card, so it is the same when you hover on all of the marks. And edit the title of the chart, referencing the Current Year and Previous Year fields.

The challenge has a ‘space’ between each Segment, and this is the final TIP I used.

On the Measure Values section on the left below the marks card, type in MIN(NULL). This will initially create a new ‘blank’ row between the bars and the gantt marks, which isn’t where we want the blank row to be.

To resolve this, simply click on the MIN(NULL) text in the chart and drag the text below the PY Sales (copy) text

And now you just need to uncheck Show Header against the Measure Names pill on Rows, and the Measure Values and PY Sales (copy) fields on the Columns. Then remove all row and column borders and gridlines and hide labels for rows and columns.

Hopefully you’ve got the final viz which you can now add to a dashboard. My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you show the top and bottom profit performers for the selected time period?

With #TC21 looming next week, Candra’s set this week’s challenge, based on inspiration from past Tableau Conferences – a simple looking, but effective visualisation for understanding profit performance within some pre-established timeframes.

  • Building the BANs
  • Identifying Top 5 / Bottom 5 / Everything Else
  • Building the Chart and Labelling the Bars
  • Adding the interactivity

Building the BANs

The timeframes we need to report over need to be based on a specific date. In this case it’s the latest date in the data set. If you were using this for a business dashboard, you might be basing it on Today / 1st of the Current Month etc. Rather than hardcode the date I need, I’ve worked out the latest month I want to use by

Max Month

{FIXED:MAX(DATE(DATETRUNC(‘month’,[Order Date])))}

Set all the Order Dates in the data set to be the 1st of the month, then get the maximum of these dates. So as the last date in the data set is 30th Dec 2021, that’s been truncated to 1st Dec 2021 which is then what this field stores.

I then want to capture the profit values for each month, quarter, year into separate fields, so we have

Month

IF DATETRUNC(‘month’, [Order Date])=[Max Month] THEN [Profit] END

This only stores Profit values for rows where the Order Date is also in December

Quarter

IF DATETRUNC(‘quarter’,[Order Date])=DATETRUNC(‘quarter’,[Max Month]) THEN [Profit] END

This only stores Profit values for rows where the Order Date is in the same quarter as December (ie the 4th quarter which is months Oct-Dec).

Year

IF DATETRUNC(‘year’,[Order Date])=DATETRUNC(‘year’,[Max Month]) THEN [Profit] END

This stores Profit data for rows where the Order Date is in the same year.

All these fields are formatted to be $ with 0 dp.

A basic viz can the be built with Measure Names on Columns and Measure Names and Measure Values on Text. The Measure Names heading is then hidden, and the font and table formatting adjusted so the sheet looks as below.

Note – Naming these fields Month, Quarter, Year rather than Monthly Sales, Quarterly Sales etc, makes this display much easier and also helps with the interaction later.

Identifying the Top 5 / Bottom 5 / Everything Else

We need to be able to identify the Sub-Categories which have the best profits, those that have the worst, and the ‘rest’. We’re going to use Sets to help us with this. However the set entries could change depending on whether we’re looking by month, by quarter or by year. So first we need to create a field that is going to store the particular Profit value we need depending on what time period is being selected.

We need a parameter pDatePart to capture the time frame. This is a string field which is just defaulted to the text ‘Month’.

The interactivity later will set this parameter to the different values.

So now we know the ‘selected’ date part, we need to get the appropriate profit value

Value To Plot

CASE [pDatePart]
WHEN ‘Month’ THEN [Month]
WHEN ‘Quarter’ THEN [Quarter]
ELSE [Year]
END

This just uses the values from the 3 measures we created to start with.

So now we can create the sets we need. Right click on Sub-Category > Create > Set and create a set called Top 5 that is based on the Top 5 Value to Plot values

Then create another set in the same way called Bottom 5

With these sets, we can now determine the Sub-Category ‘label’ that will be displayed

Sub-Category Display

IF [Top 5] OR [Bottom 5] THEN [Sub-Category] ELSE ‘Everyone Else’ END

and the grouping that will be used to colour the bars

Sub Cat Group

IF [Top 5] THEN ‘Top 5’
ELSEIF [Bottom 5] THEN ‘Bottom 5’
ELSE ‘Everything Else’
END

Building the Chart & Labelling the Bars

Ok, so now we’ve got the building blocks in place, we can build the chart. You will probably be tempted to build a bar chart (I did to start with), but positioning the labels then became a bit tricksy. When we get to the labels, we’re going to need to use the left and right alignment options. However, when you build a bar chart, if you right align the label, the label will be positioned outside at the end of the bar (even though this seems a little odd with negative values, as it looks to be on the left…).

Right aligned labels

But then we set the labels to be left aligned, the labels appear inside the bar instead, and not outside on the left.

Left aligned labels

So instead, rather than using the bar mark type, we need to build this chart using the gantt mark type, and base the Size on the Value to Plot field.

However, the value being plotted is actually an average value based on the number of Sub-Categories being ‘grouped’ as otherwise the value associated to Everything Else can end up bigger than all the rest. I created the following field

Avg Value To Plot

SUM([Value to Plot])/COUNTD([Sub-Category])

formatted to $ with 0dp.

So now we start building by adding Sub-Category Display to Rows and type in MIN(0) into Columns. Change the mark type to Gantt and add Avg Value To Plot to Size. Add Sub-Cat Group to Colour and adjust accordingly. Sort the Sub-Category Display field by Avg Value To Plot descending.

Now we can’t just label by a single field of the value or the sub-category, as while the ‘automatic’ label alignment option, almost puts the labels in the right positions, there is no way to define an ‘opposite’ to the ‘automatic’ alignment. We need to define some dedicated label fields based on where we want them to display.

Label – Left – Profit

IF [Avg Value To Plot]<0 THEN [Avg Value To Plot]
END

If we’re in the bottom half of the chart, we’re going to display the Profit value on the left side.

Label – Left – Sub Cat

IF [Avg Value To Plot]>=0 THEN ATTR([Sub-Category Display])
END

If we’re in the top half of the chart, we’re going to display the Sub-Category Display on the left side.

Add both these fields to the Label shelf and then adjust the label alignment to be left.

To label the other ends, we need to create two further label fields

Label – RightProfit

IF [Avg Value To Plot]>=0 THEN [Avg Value To Plot]
END

Label – Right – Sub Cat

IF [Avg Value To Plot]<0 THEN ATTR([Sub-Category Display])
END

We then need to create another MIN(0) on Columns (easiest way is to hold down control, then click on the existing MIN(0) field and drag it next to itself to create a duplicate. Then on the 2nd marks card, remove the two Label – Left – xxx fields and add the two Label – Right -xxx fields. Change the alignment to right.

The make the chart Dual Axis and synchronise the axis.

Now you can hide the Sub-Category Display header from showing, hide the axis, remove gridlines etc.

Adding the interactivity

Once the two sheets are on the dashboard, you can add a dashboard parameter action which will on select of the KPI/BAN chart, pass the Measure Name into the pDatePart parameter. When the mark is unselected, the parameter value should stay as it is.

And hopefully, you should now have a working viz. My published version is here.

Enjoy #TC21!

Happy vizzin’! Stay Safe!

Donna

Filtering Table Calcs

It was Sean Miller’s turn this week to set the challenge, which had an added twist – to complete only using the web authoring feature in Tableau Public, something I’d never tried before.

Most challenges use a version of Superstore Sales which I usually already have on my laptop, due to the multiple instances of Tableau Desktop installed, but this time, I had to get the data from data.world. I saved this locally then logged into Tableau Public online and started the process to build a viz online, by clicking the Create a Viz (Beta) option.

When prompted I then chose to upload from computer, the data source file I’d downloaded, and once done I was presented with the online canvas to start building

Unsure as to whether there was any auto save facility online, I decided to save the workbook pretty much immediately, although I was first prompted to navigate to the Data Source tab and create an Extract before I could save.

Now I was ready to start building out the requirements.

I started with the trend chart. The grain of the chart was at the month level, so I first created a specific field to store the Order Date at this particular level

Order Date Month

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

I then added this to the Columns shelf as a continuous (green) exact date, and added Sales to the Rows shelf. I changed the mark type to Area, set the Colour to a blue, dropping the opacity to 50% in an attempt to match Sean’s colouring and adjusted the tooltip.

I then added another instance of Sales to the Rows shelf next to the existing one, changing the mark type of this instance to Line and setting the Colour to a darker blue at 100% opacity. I then used the context menu on this pill to set a Quick Table Calculation of type Moving Average.

By default this sets the average to the 3-month rolling average as per the requirement. I adjusted the tooltip, made the chart dual axis and synchronised the axis, then removed Measure Names from the Colour shelf that had automatically been added.

My next step at this point was to work on the filtering requirement. To help with this, the first thing I did was to Duplicate as crosstab (right click on the chart tab). This created a tabular view of the data so I could work on getting the calculations I need for the filtering. I flipped the columns and rows, so my starting point was as below.

I chose to use parameters to capture the min and max dates that the user selects on the dashboard.

pMinDate

Date parameter defaulted to 01 Jan 1900

And I also created pMaxDate exactly the same way.

I then needed fields to store the relevant dates depending on whether a selection had been made or not

Min Date Selected

IF [pMinDate]=#1900-01-01# THEN {FIXED : MIN([Order Date Month])}
ELSE [pMinDate]
END

The FIXED statement sets the date to the minimum month in the data set, if the parameter is set to its default. The below doed similar to get the maximum date in the data set.

Max Date Selected

IF [pMaxDate]=#1900-01-01# THEN {FIXED : MAX([Order Date Month])}
ELSE [pMaxDate]
END

Using these dates, I then created a field to determine whether the month was within the min & max dates

Is Month Selected?

[Order Date Month]>=[Min Date Selected] AND
[Order Date Month]<= [Max Date Selected]

Pop this field onto the table view, and show the parameters and set them to some other values eg Min Date = 01 Jun 2018 and Max Date = 01 Sept 2018. You’ll also need to adjust the table calc setting of the Moving Average Sales pill to compute by Is Month Selected as well.

You’ll see True is stamped against the rows which match the date range. Now, if you make a note of the Moving Average values against these rows, and then add Is Month Selected? = True to the Filter shelf, you should get just the True rows displaying, BUT the Moving Average values are now different. This is because, with this type of field as a filter, the filter is being applied BEFORE the table calculation is being applied – the table calculation essentially has ‘no knowledge’ of the rest of the rows of data as they have been filtered out. Solving this issue is the crux of this challenge.

Remove Is Month Selected? from the filter shelf. We’re going to create a new field to filter by instead

FILTER

LOOKUP(MIN([Is Month Selected?]),0)

This field uses the LOOKUP table calculation, and is basically looking at the value of the Is Month Selected? field in it’s ‘own row’ (denoted by the 0 parameter).

Pop this onto the table view, and you can see that it’s essentially a duplicate of the Is Month Selected? field.

Now add the FILTER =True field to the Filter shelf instead, and this time the rows will once again filter as required, but the Moving Average values will be the same as those in the unfiltered list, which is what we need.

This works because the field we’re filtering by is a table calculation. When added to the filter shelf, these types of fields are applied at the ‘end’ of the processing; ie the table calculation is computed against all the rows in the data AND THEN the filter gets applied. As a result the Moving Average values are retained.

This is all part of Tableau’s Order Of Operations and is a fundamental concept when working with filters.

Armed with this knowledge, we can now apply the FILTER=True to the Filter shelf of the chart we built.

Now we can start to work on the bar charts, which will show Sales along with Average Monthly Sales. For this 2nd measure, I need to work out the number of months captured between the min & max dates.

No. Months

DATEDIFF(‘month’, [Min Date Selected],[Max Date Selected])+1

And with this I can now create

Avg Monthly Sales

SUM([Sales])/MIN([No. Months])

The bar chart viz can then be quickly created by adding Sub-Category to Rows and both Sales and Avg Monthly Sales to Columns, and sorting by Sales descending. The colours of each bar are adjusted to suit.

The bars also need to react to the dates selected, but we can simply add the Is Month Selected? = True to the Filter shelf for this, as none of the values displayed on this chart are reliant on table calculations.

To remove the gridlines from both charts, select Format > Workbook from the menu, scroll down the Format dialog displayed on the right and set Gridlines to Off

Finally, title both sheets appropriately, and then add to a dashboard. We need to add 3 actions to this dashboard – 2 parameter actions and 1 filter action.

Create a parameter action which will on Select, set the pMinDate parameter by passing the Minimum Order Date Month, and will reset back to 01 Jan 1900 when unselected.

Then create another instance, which sets the pMaxDate parameter instead, passing the Maximum Order Date Month.

Finally, create a Filter Action which on Select of the bar chart, filters the trend chart

And with that, the challenge should be complete. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you use set actions to create a reference date range?

There’s a lot packed into the challenge this week, which was “set” by Lorna Brown and Erica Hughes to test our Set Action skills. How detailed this blog will go, I have yet to decide… I’ve got a couple of hours to get this nailed, so it could get quite brief as we get towards the end 🙂

I’ve got 6 sheets/charts making up this dashboard, so my intention is to summarise each one, and I’ll define the various calculations that are going to be needed as we go.

  • The overall summary table
  • The selected months summary table
  • The trend line
  • The donut chart
  • The top 3 states table
  • The map
  • Adding the interactivity

The overall summary table

This challenge is focused on understanding the Sales per month. Whilst its possible to use the built in aggregation features of a date field, I often prefer to create explicit date fields at the level I require, so it’s easier to reference. Therefore, the first field I created for this challenge was

Order Date To Plot

DATETRUNC(‘month’, [Order Date])

This essentially ‘groups’ every order placed in a month to be tagged with the 1st of the month. I custom formatted this field to MMM yyyy (ie Oct 21).

For the overall summary table, I need to capture the total sales of the whole data set, and I use a Fixed LoD calculation for this.

Total Sales

{FIXED: SUM([Sales])}

This field is formatted to $0.00M

NOTE – I actually named this field <space>Total Sales<space> as I want to display the name of the field (the measure name) in the summary table, but the ‘selected months’ summary table also has a Total Sales measure which is a different calculation (see later). Adding the <spaces> is a sneaky way to get two fields with what appears to be the same name. As this field when displayed will be centred, the <spaces> aren’t noticeable.

We also need to get the monthly average sales for the whole data set

Average Sales by Month

AVG({FIXED [Order Date To Plot]: SUM([Sales])})

Format this to to $0.0K

We can now build the summary table by adding Measure Names to the Filter shelf and selecting these 2 fields. The placing Measure Names on Rows and Measure Names and Measure Values on Text. Reorder the measures as required, hide Measure Names on Rows and format the Text as required.

Change the title of the sheet to Superstore Sales, ensure the tooltip doesn’t display and remove all gridlines / row banding etc.

The selected months summary table

The core requirement of this challenge is to make use of set actions, so we’re obviously going to need a set which will contain the dates (months) the user will select on the chart. This set will be based off of Order Date To Plot. Right click on the field > Create > Set. Name it Order Dates To Plot Set and by default, select all the months between Oct 20 to Jun 21 inclusive.

Later I’ll describe how the values of this set will get updated, but for now, we need to get some information relating to the sales in these selected months.

Firstly, we want the total sales for the months in this set.

Total Sales

IF [Order Date To Plot Set] THEN [Sales] END

The default format for this field is set to $ with 0 dp.

Note – this is the other ‘total sales’ field mentioned earlier. This field name has no leading/trailing spaces.

To get the average, I needed a field just to store each member of the set (ie each selected month)

Selected Dates

IF [Order Date To Plot Set] THEN [Order Date To Plot] END

and with this I can then work out

Average Sales

AVG({FIXED [Selected Dates]: SUM([Total Sales])})

The final measure required for this section is the change within the date range, which is basically comparing the value of sales at the first month in the selection with the sales in the final month selected. We need a few fields to get to this.

Firstly, we want to identify the first and last months

Min Selected Date

{FIXED:MIN(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}

If the date is in the set, then return the date and then take the minimum of all the dates, and store against all the rows in the data. Similarly we have

Max Selected Date

{FIXED:MAX(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}

Putting this info into a table, you can see how the calculations are working. The values for the Min & Max dates are the same across every row.

Next we need to get the Sales at the min & max points, and spread that value across all rows

Sales at Min Date

{FIXED: SUM(IF [Order Date To Plot]=[Min Selected Date] THEN [Sales] END)}

Sales at Max Date

{FIXED: SUM(IF [Order Date To Plot]=[Max Selected Date] THEN [Sales] END)}

Now we can work out the difference

Change within Date Range

([Sales at Max Date]-[Sales at Min Date])/[Sales at Min Date]

format this to a percentage set to 1 dp

Finally, we need to know the number of months in the set, which is displayed in the title of the monthly summary sheet.

Months in Set

{FIXED: COUNTD(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}

If the date is within the set, then capture the date, and the count the distinct set of dates captured.

Make this a discrete field (move from the measures section at the bottom of the data pane to the dimensions section at the top (above the line), and add to the tabular view

Now we can build the summary sheet.

Add Measure Names to the Filter shelf and this time filter by Total Sales, Average Sales and Change within Date Range. Add Measure Names to Rows and Measure Values to Text. Reorder the measures.

Format the Total Sales to be in $K, by selecting the Format option from the context menu of the Total Sales pill on the Measure Values shelf (hover on the pill and click the carrot/down arrow that appears – by formatting this way, we’re changing the display of this field for this sheet only).

Add Min Selected Date and Max Selected Date to the Detail shelf and set to be Exact Date. Format both these fields via the pill context menu to be the ‘March 2001’ format

Also add Months in Set to the Detail shelf.

Adjust the title of the sheet as below

Finally you need to set the background of the worksheet to the relevant purple (I used #8074a8), adjust the colours of all the fonts to white and adjust the size/style of the fonts in the table. Remove all gridlines/row banding etc, and you should have something like below

The Trend Line

By this point we’ve built all the calculated fields we need for this chart. This is a dual axis line chart, as we want the colour of the line for the selected dates to be different from the non selected ones, and we want to display a label for the highest sales in the selected timeframe.

  • Add Order Date to Plot to Columns, and set as a Continuous (green) pill set to Exact Date
  • Add Sales to Rows
  • Add Total Sales to Rows
  • Make the chart dual axis, and synchronise axis.
  • Adjust the colours of the Measure Names colour legend
  • On the Label shelf of the Total Sales marks card, set to label the maximum value only
  • On the All Marks Card, add Min Selected Date and Max Selected Date to the Detail shelf and set to Exact Date.
  • Right click on the Order Date To Plot axis and Add Reference Line
  • Create a reference band that starts at the constant Min Selected Date, ends at the constant Max Selected Date, is bounded by dotted lines and shaded between
  • Hide the Sales and Total Sales axis, format tooltips and adjust the row & column dividers.
  • Change the title and you should get to

The donut chart

Donut charts are 2 different sized pie charts on top of each other, created using a dual axis chart. On the Rows shelf type in MIN(0). Then type the same next to it. This gives you two axis and two marks cards.

We only care about information related to the selected dates for this chart, so we can add Order Date To Plot Set to the Filter shelf, which by default will just restrict the information to the data ‘in’ the set.

Change the mark type of the 1st MIN(0) marks card to Circle and add Sales to the Tooltip shelf. Adjust the size of the charts/mark.

Change the mark type of the 2nd MIN(0) marks card to Pie and add Sales to the Angle shelf. Add State to the Detail shelf. Sort the State field by Sales descending.

Note – the circles might look the same at this point, but if you hover over the bottom one, you should see that it’s segmented by State.

We need some new fields now to help us identify the top ranking states.

Sales Rank

RANK(SUM([Sales]))

This is a table calculation, so it’s best to see how this field will work in a table view – build one out as below, and set the table calculation on the Sales Rank field as shown

We’re now going to ‘group’ the ranks into the top 3 and everything else

Sales Rank Group

IF [Sales Rank]<=3 THEN [Sales Rank] ELSE 10 END

We can now use this Sales Rank Group field to colour the pie chart. On the 2nd MIN(0) marks card, add Sales Rank Group to the Colour shelf. Adjust the table calculation to compute using State as above, then change the field to be Discrete (blue). Adjust the colours to suit.

Now make the chart dual axis, and synchronise the axis. Adjust the size of the 1st MIN(0) circle to be smaller than the pie. If it’s not showing, right click on the right hand axis and move marks to back. Colour the circle white. Adjust tooltips to suit and hide axis, column/row dividers etc. Update the title. You should have

The top 3 states table

  • Add Order Date To Plot Set to Filter
  • Add State to Rows and Sales to Text and sort descending.
  • Add Sales Rank to Filter and set to At Most is 3. This will just show the top 3 states.
  • Add State to Text
  • Add a Percent of Total Quick Table Calculation to the existing Sales field that’s on the Text shelf (via the context menu of the pill)
  • Add another instance of Sales back onto the Text shelf
  • Adjust / format the font size and layout of the fields on the Text shelf
  • Add Sales Rank to the Size shelf and set to be discrete (blue) and set the mark type to be Text. Adjust the size of the marks – it’s likely it’ll need to be reversed and the range adjusted.
  • Hide the State field on Rows, adjust the font colours, remove row banding and row/column dividers. You should end up with…

The map

  • Add Order Date To Plot Set to the Filter shelf
  • Add State to Detail – this should create a map (edit locations to be US if need be – Map -> Edit Locations menu)
  • Add Sales to the Colour shelf
  • Edit the colour range to a suitable purple range ( I set the darkest colour of the range to #6c638f)
  • Adjust the map layers (Map -> Map Layers) so only the option highlighted below is selected.

Adding the interactivity

Add all the sheets to the dashboard, using vertical and horizontal containers to arrange the relevant layout. Then add a Set Action (Dashboard > Actions > Add Action > Change Set Values). The Set Action should be configured as below :

And fingers crossed, you should now be able to select marks on the trend line and see all the other charts, except the initial summary table, all update. My published viz is here.

Happy vizzin’! Stay Safe!

Donna