Author: Donna Coles

Can you show the top 10 rank over time for each Olympic country?

Donna ColesLeave a comment

The #WOW2020 week 31 challenge was combined with a #PreppinData challenge and launched at a virtual live event. I was fortunate enough to be able to join this event for the first hour and had the pleasure of meeting up with some #WOW regulars Sean Miller, Kyle Yetter & Tim Beard, along with #WOW challenge setter Lorna Brown, and #PreppinData setters, Jenny Martin and Tom Prowse. I was gutted I couldn’t stay on for the whole session, but work commitments got in the way – bah!

I did complete the #PreppinData challenge first, and the last time the team ran a combined event, I did blog on how I built both challenges, but I’m struggling for time, and the #WOW has got a fair bit going on, so I’m afraid the Prep challenge won’t make the cut this time – sorry #Preppers!

So onto the #WOW challenge. Part of this challenge was to utilise the new Relationships feature in 2020.2, so you need to be on this version to follow along.

Creating the data source

If you’d completed the #PreppinData challenge, you could use your own outputs as the inputs for #WOW challenge. I did that initially, but as I was building I had a few minor discrepancies from the solution, so chose to replace my data sources with the hyper files that are referenced in the Prep challenge, these being

  • Host Countries – 1 row per Olympic Host City (and Country) per Year since 1896 to 2016
  • Country Medals – 1 row per Country attending the Olympics per Year, summarising the number of Bronze, Silver and Gold medals won by that country.
  • Medalists – For every Country, for every Year, there is 1 row per Athlete who won a Medal including the type of Medal (Bronze, Silver, Gold).

In Tableau Desktop, connect to the Host Countries hyper file and drag the ‘table’ named Extract into the data source pane. If you right click on the table, you can the rename it – I chose Host.

Then Add a connection to the Country Medals hyper file, and again drag the ‘table’ named Extract into the data source pane so it connects to the Host table. Set the relationship to be on Year. I renamed the ‘table’ again to be Medals.

Now add another connection to the Medalists hyper file, and drag the ‘table’ named Extract to connect to the Medals table, this time setting the relationship both on Country and Year. I renamed the ‘table’ again to be Medalists.

Building the Bump Chart

As with many challenges, if I can, I build the data I need into a table to start with, so I can check my calculations, so lets get the basics

Note – depending on the order you connected your tables in the data source, some field names that exist in multiple tables will be suffixed with fieldname (Extract x). I won’t refer to the Extract part, but will reference fields I use in this blog by prefixing with the table name instead.

Drag out Host.Year, Host.Host Country, Medals.Country to Rows. Lets show the number of medals of each type each country won, so we can use this to sense check some calculations later : put Medals.Gold, Medals.Silver, Medals.Bronze into the table

The bump chart we need to build is ranked based on the score of each country which in turn is determined by giving 3 points for each gold medal, 2 for a silver and 1 for a bronze, so we need a calculated field

Score

IFNULL(SUM([Bronze]),0) + (IFNULL(SUM([Silver]),0) * 2) + (IFNULL(SUM([Gold]),0) * 3)

We need to rank this score, and I’m going to have a calculated field to store this explicitly

Rank Score

RANK_UNIQUE([Score])

Add these 2 fields to the table, and adjust the table calculation of Rank Score so all fields except Year are ticked

Now we could start building out the bump chart now, and I did when I was creating this and would then flit back and forth between doing something on the chart and checking new calcs. However, to keep the blog a bit easier, we’ll continue building out the table.

So first up, we need another rank field. When building out the bump chart initially, I was doing all sorts of things to show the Top 10 only, but whatever I did, I couldn’t stop the lines from joining up between countries when they didn’t exist in consecutive years eg Greece is 1st in 1896, but doesn’t appear in the Top 10 again until 1904. As 1900 was missed, the lines shouldn’t join, but mine were. I was faffing over this for some time, so eventually caved and checked out Lorna’s solution. She’d resolved this simply with

Top 10 Rank Only

IF [Rank Score] < 11 THEN [Rank Score] ELSE NULL END

ie only show the rank if its in the Top 10. Simples really!

Add this onto the table and check the table calculation setting is as previously.

So for the bones of the bump chart we have the two fields we’re going to plot against – Year and Top 10 Rank Only, but before we do that, let’s get the fields we need that are displayed on the tooltip.

# Countries Per Year

{FIXED [Year] : COUNT([Medals])}

This will give us the number of countries that participated in each year. The COUNT([Medals]) comes from dragging the Medals(Count) that is automatically generated as part of the Medals table into the calculated field dialog- in the new Relationships model, this Count field against each table is essentially the equivalent of Number of Records.

# Medals

IFNULL(SUM([Bronze]),0) + IFNULL(SUM([Silver]),0) + IFNULL(SUM([Gold]),0)

A simple tally of the total number of medals won by each country.

Is Host?

[Host Country]=[Country (Extract2)]

where this is checking Host.Host Country against Medals.Country and returns true if they match.

Hosted | Participated

IF [Is Host?] THEN ‘hosted’ ELSE ‘participated’ END

The text on the tooltip differs slightly dependent on whether the country hosted or not.

COLOUR: Country

IF [Is Host?] THEN [Host Country] END

The stars on the bump chart need to be coloured based on country, but we don’t want the circles coloured too, so this field is necessary.

Let’s get all these into our table… you’ll notice (or you may not), but adding some of these fields causes the Rank Score & Top 10 Rank Only to change, so readjust the tableau calculation so only Year remains unchecked.

Now we have everything to build the core bump chart.

Add Host.Year to Columns, Medals.Country to Detail and then add Top 10 Rank Only on Rows. Change the Mark Type to a Line, and verify the table calculation on the Top 10 Rank Only is set to compute by Country only.

Edit the Top 10 Rank Only axis and set the scale to be reversed

Change the Colour to grey and make the Size smaller.

Then add another instance of Top 10 Rank Only to site alongside the existing one on the Rows.(I tend to click on the existing pill, hold down ctrl and then drag – this will create a duplicate instance and will retain the table calculation settings).

Now make the chart dual axis & synchronise the axis.

Change the mark type of the second axis to be a shape, and add Is Host? to the Shape shelf. Adjust so that false is a filled circle and true is a filled star.

Also add Is Host? to the Size shelf on this marks card, and adjust the sizes so true is bigger than false.

At this point you will need to adjust the table calculation of the 2nd Top 10 Rank Only pill, to compute by both Country and Is Host?.

Add COLOUR: Country to the Colour shelf, and adjust the colours to use the Hue Circle palette. Set the NULL value to the same shade of grey as the line.

You’ll need to adjust the table calculation again of the 2nd Top 10 Rank By County pill, so only Year is unselected.

All the following fields need to be added to the Tooltip of the All marks card.

  • Score
  • # Countries Per Year
  • Hosted | Participated
  • Top 10 Rank Only (setting the table calc to compute by everything except Year)
  • # Medals

Adjust the Tooltip accordingly, then tidy up the formatting of the chart

  • Hide the Top 10 Rank Only axis
  • Rotate the labels of the Year
  • Hide the Year field label
  • Remove all row & column lines and gridlines/zero lines

Medals Viz in Tooltip

The Bump chart has 2 Viz in Tooltips, one showing the count of the different medals won and the other showing the top 10 athletes. Build the Medals chart by

  • Host.Year to Rows
  • Medals.Country to Rows
  • Medals.Bronze to Columns
  • Then drag the Medals.Silver pill to the bottom of the chart where the Bronze axis is, and when you see 2 green columns, drop the pill. This should have the effect of Measure Values automatically being added to Columns, and Measure Names being automatically added to Rows and the Filter shelf.
  • Then add Medals.Gold into the Measure Values pane
  • Reorgansie the pills in the Measure Values pane so they are listed Gold, Silver, Bronze
  • Add Measure Names to Colour and adjust accordingly
  • Show the mark Label

Now hide the Year and the Country columns, and the Value axis at the bottom. Remove all the formatting (the quickest way is to go to the bump chart sheet you should hopefully have formatted already, right click on the tab of the sheet at the bottom, and select Copy Formatting, then go back to the sheet you’re working on, and on the tab, right click & Paste Formatting

If this doesn’t clean everything up enough, just adjust formatting manually.

Finally, set the fit on this sheet to be Entire View. This will squash everything up, but when its referenced from the viz in tooltip, the view will be filtered to the Year and Country. Doing this will remove the ‘this view is too large’ message that may appear on the Viz in Tooltip.

Switch back to the Bump chart and add the sheet to the Tooltip by Insert -> Sheets -> <Select your sheet>. Adjust the maxwidth property to 500 and maxheight property to 100 to make the viz fit better

Top 10 Athletes Viz in Tooltip

Build the initial viz by

  • Host.Year to Rows
  • Medals.Country to Rows
  • Medalists.Athlete to Rows
  • Medalists,Medal to Columns and manually reorder the columns.
  • Medalists.Medalists(Count) to Columns
  • Medalists.Medal to Colour

To work out the Top 10, we need to first work out the score per medal

Medalist Score

CASE [Medal]
WHEN ‘Gold’ THEN 3
WHEN ‘Silver’ THEN 2
WHEN ‘Bronze’ THEN 1
END

then calculate the total score per athlete per games, since an athlete can win more than one medal and appear in multiple games

Total Score per Athlete

{FIXED [Year], [Country (Extract2)], [Athlete] : SUM([Medalist Score])}

where the Country is from the Medals table.

Use this field to sort the Athlete pill

We only want the Top 10 though, so add Athlete to the Filter shelf, and filter by the top 10 of Total Score per Athlete

At this point, things won’t look right, as the filter will be applying over all the data. To get this right, we now need to add the sheet to the Viz in Tooltip, so go back to the Bump chart, and on the tooltip add a reference to this sheet.

Adjust the width and explicitly filter by Host.Year, Medals.Country

If you now return to the Top 10 sheet, an additional filter will have been automatically added to the Filter shelf. Add this filter to context, so it will change to a grey pill.

Now return to the Bump chart and just test out that the chart is presenting correctly when hovering over various fields.

Now return to the Top 10 chart and tidy up all the formatting and hide various fields, and set to Entire View.

The bump chart should now be complete

Building the strip plot

The strip plot is showing a mark for each host with an indicator to show if they finished in the Top 10 or not. Additional information on the tooltip shows the host’s score and medals count. We’ll build this into a table first as below

We ultimately need to show 1 row per year, but the country level data is required to work out the rank. So we’re going to use some more table calculations.

We want to capture the host’s score and medals accrued against all the rows associated with each year.

Score for Host

WINDOW_MAX(IF ATTR([Is Host?]) THEN [Score] END)

Medals for Host

WINDOW_MAX(IF ATTR([Is Host?]) THEN [# Medals] END)

Add these to the table, setting the table calculation to compute over all fields except Year.

We also need to know if the host was in the top 10 or not

Is Host in Top 10?

WINDOW_MAX(IF ATTR([Is Host?]) AND [Rank Score]<=10 THEN 1 ELSE 0 END)

Add this onto the table, and again, sense check the table calculations (this one is nested) to ensure all are computing by all fields except Year

So we have all the bits of data we’ll need, we just need to reduce to 1 row per year.

Index = Size

INDEX() = SIZE()

Add this onto the Filter shelf, and set to true. Check the table calculation again to ensure all fields except Year are set. Recheck the filter is still only selecting true.

This should reduce the data, and just show the last row listed for each country.

For the strip plot, add

  • Host.Year to Columns
  • Medals.Country to Detail
  • Mark Type to Shape
  • Is Host in Top 10? to Size and to Shape. Adjust the table calcs and shape/size accordingly (you’ll need to reverse the size).
  • Host Country to Tooltip
  • Medals for Host to Tooltip (remember table calc)
  • Score for Host to Tooltip (remember table calc)
  • Index = Size to Filter set to true (again remember table calc)
  • Change colour to red
  • Adjust Tooltip accordingly

Finally adjust the formatting, and remove the header.

Top 10 Countries Viz in Tooltip

Add Host.Year, Host Country and Medals.Country to Rows and Score to Columns, then click the Sort button in the menu to sort the Country descending. Add Rank Score to Rows and change to be discrete (blue pill). Move it to sit in front of the Country field

We need to restrict the rows based on whether the Country in is the top 10 or it’s the host country

Is Host or Top 10?

ATTR([Is Host?]) OR [Rank Score]<=10

Add this onto the Filter shelf and set to true; once again make sure the table calc is computing by all fields other than Year (and double check its still set to true after adjusting). If you scroll to the bottom, you should see 11 rows for 2016, as the host, Brazil, finished 12th.

And now we need to colour the bars

COLOUR:Host

IF [Rank Score] > 10 THEN ‘Red’
ELSEIF ATTR([Is Host?]) THEN ‘Blue’
ELSE ‘Grey’
END

Add to the Colour shelf and adjust accordingly. Then add Score and # Medals to the Text shelf and format the label displayed. Finally hide some of the fields and format the chart to remove axis/gridlines etc.

Finally, set the fit to Entire View.

Go to the strip plot chart and add this sheet to the Tooltip, setting the filter to be on Year

And that should be all the components you need to build the dashboard.

My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a drill down using set actions?

Donna ColesLeave a comment

For week 30, #WorkoutWednesday alumni Emma Whyte returned re-posting this challenge which was originally set in Week 41 of 2017 (see here). The idea behind this was to see how the challenge could be achieved using features that have been released since that challenge – in this case set actions.

I’ve been doing the #WorkoutWednesday challenges since they were first introduced, so I completed the original challenge, which is posted here.

Despite it being over 2 1/2 years ago, I had a strong recollection as to what was required to achieve this. So the challenge I set myself, was to recreate without looking at my own solution.

Building out the data

This is one of those challenges where we can build the data out into a table to check the functionality before building the actual viz. I always like to do this where possible, as I find it a good reference to make sure I’m getting the logic & the calculated fields I need right.

Start by adding State & City to Rows and add Sales & Profit via Measure Names on Columns .

As the challenge is to use Set Actions, then naturally, we’re going to need a Set. The Set we need is based on State with the idea being that when there is a State(s) in the set, then the City will display instead.

Selected State

Right click on State and Create -> Set. Select an option in the dialog, eg Alabama say

We will need to show the marks based on State or City depending on whether a State has been selected or not. We need a single field that we will use in the viz that displays the dimension we need to show

Display Value

IF [Selected State] THEN [City] ELSE [State] END

Add this onto the Rows and you’ll see how this is working

We can test the functionality of putting values into and out of the set without the need for the dashboard action at this point, by right-clicking on Selected State and selecting Show Set – the list of set values to select will display (a bit like a filter list).

We need a way to figure out what rows to show – how to identify whether there’s anything selected in the set.

Count States Selected

{FIXED : COUNTD(IF [Selected State] THEN [State] END)}

By being an LOD, this will set the count of the items in the set across all the rows in the data. Add to the sheet so you can see how this works

So we want to show information when either there isn’t anything in the set, or for the rows associated to the Selected States only

Records to Show

[Count States Selected] = 0 OR [Selected State]

Add this to Rows and test out… with no State in the set, all the rows are True

but with a State selected, only the rows associated to that State are True

But we seem to have too many marks showing when there’s nothing in the set….?

That’s fine.. just take City out of the view now, and if you deselect all States you should get the 48 rows we’re going to start with listed, and all are Records to Show = True. The Sales & Profit values will also now be aggregated to the appropriate level.

Building the Viz

Ensure your Selected States set is empty, and build out the scatter plot

  • Profit on Columns
  • Sales on Rows
  • Display Value on Detail & Label
  • Records to Show on Filter set to True
  • Mark Type = Shape set to x

Verify the functionality by clicking a State in the list, and the view should change to show the City.

We need to colour the marks based on Profit

-ve Profit?

SUM([Profit])<0

Add this to the Colour shelf and adjust colours accordingly.

Finally we need to look at how the title/subtitle changes based on which level we’re at.

Title

IF [Count States Selected] = 0 THEN ‘Sales vs. Profit by State’
ELSE ‘Sales vs. Profit for ‘ + [State]
END

Subtitle

IF [Count States Selected] = 0 THEN ‘Select a state to drill down to city level’
ELSE ‘Double-click a city to drill up to state level’
END

Add these onto the Detail shelf, then they’ll be available to reference in the Title of the sheet.

And then adjust the Tooltip, and we’re pretty much ready to go.

Adding the Set Action

Create a dashboard and add the scatter plot sheet to it.

Add a dashboard action to Change Set Values which runs on the Select action, and assigns values to the Selected State. On clearing the selection, values are removed from the set.

And that should pretty much be it. My published version is here. I thoroughly enjoyed the ‘throwback’ to previous challenges, and would like to see this theme continue on occasion.

Happy vizzin’! Stay Safe

Donna

Can you dynamically display a label on a heat map?

Donna Coles4 Comments

Ivett Kovacs returned with the #WOW challenge for this week, providing 2 versions. She provided some hints in the requirements which helped. With a bit of head scratching I managed to deliver an advanced version too. It didn’t exactly match what Ivett had delivered, but I was quite happy with my version. Until my follow #WOW participant Rosario Guana pointed out that the display ‘on click’ of a row or column total didn’t work properly 😦

I’ve spent A LOT of time trying to figure out what’s required but can’t resolve it. I’ve checked out Ivett’s solution and noticed a slight flaw in her logic too, so I feel uncomfortable changing my flaw for another. I’ve also looked at other solutions, and all are showing slightly different behaviour when you click on a column or row cell total. As a result, I’ve decided to blog based on the Intermediate version only. Others have posted blogs about their Advanced versions, so I’m going to save some time today, and reference them instead.

Building the basic heat map

The core of this challenge is going to involve Sets, and we’re going to use Set Actions to identify which cell is being interacted with.

For the intermediate version, we’re interacting based on hover actions.

The heatmap displays a grid of months by years, and we need to build sets based off of these. So we’ll start by creating dedicated calculated fields storing these values

Month Order Date

DATENAME(‘month’,[Order Date])

Year Order Date

YEAR([Order Date])

Add Month Order Date to Columns and Year Order Date to Rows, then drag Quantity onto Colour and change to AVG. You should have your basic heatmap.

From the Analysis -> Totals menu, select to shoe row & column grand totals. They will initially display as white cells, so you need to Edit Colours on the colour legend and select Include Totals/

Let’s sort some formatting out at this point.

Set the mark type to Square

Format the borders by setting the row & column dividers to none, but setting the Grand Total pane & header to be thick, white lines

And change the word Grand Total, by right clicking -> format on each cell and changing the Grand Total label.

Adjust the size of the Month & Year labels and remove header labels from displaying and you have

Labelling the total cells

We need to permanently display the label on the total row/column, and only show the label on the cells ‘on hover’.

We need a way to identify when we’re on a total cell. Ivett gave a hint “What is the Size() of the totals and the cells?“. This is a trick I’ve read about and discussed here. Basically the size() of the grand total row or column will be 1, so this is what we’re going to base some logic on.

Size of Months

SIZE()

Size of Years

SIZE()

Total Label

IF [Size of Years]= 1 OR [Size of Months] = 1 THEN AVG([Quantity]) END

If we’re on a row or column grand total, then show the AVG(Quantity). Add this onto the Label shelf, and edit the table calculation so the Size of Years nested calc is computing by Year Order Date, and the Size of Months nested calc is computing by Month Order Date.

Labelling the central cells

We need to create some sets, as these are going to determine whether we are hovering on a cell or not. The dashboard action will add values to a set on hover.

Right click on Month Order Date and Create Set, selecting an arbitrary month.

Hover Month Set

Do the same with Year Order Date

Hover Year Set

We then need a field to label the cell

Cell Label

IF ATTR([Hover Month Set]) AND ATTR([Hover Year Set]) AND [Size of Months]>1 AND [Size of Years]>1 THEN AVG([Quantity])
END

If something has been selected in the Hover Month Set and something has been selected in the Hover Year Set, and we’re not on a grand total row/column, then show the AVG(Quantity).

Add this to the Label shelf, and edit the table calculation settings as above.

Tooltip

The tooltip displays the Profit Ratio

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

so you’ll need to create this field, add to the tooltip and adjust the display accordingly.

Add the interactivity

Add the above sheet onto a dashboard, then create a dashboard action to Change Set Values, which is set to

  • run on Hover
  • Target the Hover Month Set
  • Assign values to the set when run and remove values from the set when cleared

Repeat to create a set action to target the Hover Year Set.

Test it out – hover your mouse over the cells. If you hover on a total, you should get the values displayed for all the values in the row or column

Advanced Version

As I stated in the intro, I didn’t end up with something I was entirely happy with. While writing this up, I’ve tried to rebuild using ideas from other published solutions, but I still have got anything ‘perfect’.

Ivett’s solution is obviously the closest to what we’re aiming for, since that is the challenge. The minor flaw I found in that though was that in some of the labelling logic there’s a step which compares the ‘average of the selected cell’ with the ‘average of the current cell’, and if they match, display the AVG(Quantity), otherwise show the difference. This logic is basically trying to work out ‘if current cell = selected cell’, but it doesn’t fully work, as there are other cells where the values match – it’s subtle as the displayed values are faded out with the cell highlighting, as I’ve shown in the example below.

Kyle Yetter has blogged his solution here, but if you click on a row or a column total in his solution, the values are all the same, and not showing a difference.

Rosario Guana has blogged her solution here. Rosario has adapted her solution slightly and displays both the average values and the difference ‘on select’, so the logic I was struggling with, isn’t being used in the same way here.

No doubt with a bit more time, I might finally crack this one, but for now, I need to ‘put it to bed’ 🙂

Happy vizzin’! Stay safe!

Donna

Are Sales on track with Goal?

Donna ColesLeave a comment

This week’s #WOW challenge was set by Ann primarily to demonstrate the recent 2020.2 release of relationships in Tableau. This is a change to how the data modelling works when combining data together, in this case actual sales data and sales goal (target) data. Ann provided a ‘tweaked’ version of superstore sales data for the actual sales, alongside a custom ‘goals’ data set which provided a sales goal value for each month per Segment and Category (ie the goals data was not as granular as the actual sales data).

Combining the Data

Obviously, to make use of the new relationships feature, you’ll need at least v2020.2.1 installed.

When you add the 2 data sources to the Data Source pane and add the Orders sheet from the Superstore data and then add the Sheet 1 from the Sales Goals data, you’re prompted to add a relationship if matches can’t automatically be found (ie the field names aren’t identical).

I added relationships for the Segment, Category and Month of Order Date fields, which then provides a ‘noodle’ link between the two sets of data

Note – to add multiple relationships, you have to close the field listing to then see the Add more fields option.

With relationships, Tableau is now smarter at how it manages the queries between the data. I’m not going to go into it at length (as there’s plenty of good Tableau documentation on the subject), but I’ll quickly show the difference between this and a traditional ‘join’.

In the Relationships model, if I show Sales and Goal by Month & Segment, the Goal is based on the sum of the rows associated to that Month and Segment that you can find if you looked at the source spreadsheet directly

But if I was using a traditional join with these data sets :

and then presented the same info as above

The Goal data is way off, as it’s being influenced by all the additional rows of data that have been constructed by the join clause.

If we expand to the Category level in each example above (the level at which the Goal data is stored), the Relationship version still shows the correct value

while the Join version is still incorrect, and only changing to an Average aggregation do we get the figure needed

Joining the data requires more work (calculations / knowledge) to ensure the Goal data is reporting correctly at the various levels, whereas the Relationships model ‘just works’.

Building the Table

When I started building the solution, I actually started with the YoY trend chart, but found I had to revisit it (swap a couple of pills about) as I progressed through, so I’m going to start with the table of data itself.

This turned out to be really quite tricksy… it’s just a table right… looks harmful enough…. but Ann had lobbed a few ‘gotcha’ grenades into the mix, that took a bit of head-scratching!

First up, Ann likes to use capitalisation, so I simply renamed the following fields

  • Category (from Sheet 1 – the Goals data) -> CATEGORY
  • Segment (from Sheet 1 – the Goals data) -> SEGMENT
  • Month of Order Date (from Sheet 1 – the Goals data) -> MONTH
  • Sub-Category (from Orders – the Superstore Sales data) -> SUB-CATEGORY

And I then created a hierarchy called SEGMENT which stored SEGMENT – > CATEGORY -> SUB-CATEGORY.

We also needed to ‘fake’ today so we can show sales to date. The Orders Superstore data set contains data from the start of 2016 to the end of 2019. We needed to pretend ‘today’ was 1st July 2019 and not show any sales beyond the end of the month. So I created the field

Today

#2019-07-01#

and then

ACTUAL SALES

IF DATETRUNC(‘month’,[Order Date])<= [Today] THEN [Sales] END

If we build out the basics of the table

  • SEGMENT hierarchy on Rows
  • MONTH (discrete, exact date, formatted to mmmm yyyy) on Rows
  • Measure Names on Columns and on Filter, filtered to ACTUAL SALES and Goal
  • Measure Values on Text
  • MONTH on Filter, set to exclude 2016

we get

and as we expand the hierarchy to the next level, the ACTUAL SALES and Goal adjusts as expected

but when we reach the 3rd level we see a couple of issues

  1. The Goal is showing the values that were displayed at the CATEGORY level, since it isn’t set at any further level. The requirement we have is to show the value ‘No Goal’ instead once we reach this point.
  2. We also have a set of values with a NULL SUB-CATEGORY. This is because there are dates with goal values in the Goals data set, but we have no Sales data for those dates, so a match at the SUB-CATEGORY level can’t be found. We need to remove these records when at this level, and actually only show records where we have ACTUAL SALES, so records from July 2019 onwards should also be excluded.

At this point, I did end up having a sneak look at Ann’s solution. I had an idea of what I wanted to do, BUT I was worried that I may be missing something related to the new data model… that perhaps there was a feature of relationships that I needed to be exploiting as part of this, rather than go down the table calculation route I was heading towards. After all, part of doing these challenges for me is to make use of new features & functions if I can, so I have a reference workbook for future use. I had a read up on the blog posts related to relationships, but couldn’t spot anything obvious, hence a quick check at Ann’s solution…. nope nothing special required in respect of the data model….so instead, we need some additional calculations to help resolve all this.

What we need is some way to identify when we’re at the lowest SUB-CATEGORY level. We’ll start by counting the number of distinct sub-categories we have

# Sub Cats

ZN(COUNTD([SUB-CATEGORY]))

Wrapped in a ZN will return the value 0 when none exist.

Dropping this into the table of data, we can see the 0s for all the future Goal records, and 1 for the rest of the rows, since we’ve expanded to the SUB-CATEGORY level.

If you collapse up to the CATEGORY level, the #Sub Cats count changes, as each row typically contains more than 1 SUB-CATEGORY.

The term ‘typically’ in the above statement though is the reason we can’t assume we’re at the lowest level if the count is 1 though. It is possible for the sales against a particular CATEGORY in a MONTH to only span a single SUB-CATEGORY (Check out Home Office -> Furniture in Jan 2018). So we also need

Max Sub-Cats in Window

WINDOW_MAX([# Sub-Cats])

Which will count the maximum number of SUB-CATEGORY records for each SEGMENT and CATEGORY. In the case of Furniture Sales in the Home Office in Jan 2018, you can see below the count of sub-categories is 1 but the maximum count in the window is 4.

This shows we’re not at the SUB-CATGEORY level, as when we are, the #Sub Cats = Max Sub Cats in Window (ie 1=1)

So with that knowledge we can now derive an alternative field for the goal

SALES GOAL

IF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window]) THEN 0 ELSE SUM([Goal]) END

Remove the original Goal field from the table and add this instead, setting the table calculation to compute by MONTH and you’ll see the SALES GOAL value change from a value to 0 as you expand down to the SUB-CATEGORY level.

So how do we show ‘NO GOAL’ instead of 0? Well this is a bit of a sneaky formatting trick, and one I couldn’t figure out (this step was actually the last thing I changed before publishing).

In my original SALES GOAL calculation I had set the value to be NULL rather than 0 expecting there to be an option to show the NULL values as something else (ie NO GOAL). But the feature I was looking for wasn’t available (maybe I was mistaken about when it appears.. maybe it’s disappeared in 2020.2… I still need to investigate). Anyhow, my alternative was to create a string field to store the value in, but this would make formatting the number quite cumbersome, and I was sure that wasn’t the case.

So I had to check Ann’s solution. Instead of NULL, she had set the value to be 0, and consequently used the following custom number formatting :

“$”#,##0;-“$”#,##0;”NO GOAL”

where the last bit is how to format 0. Very very sneaky huh!

So we’ve worked out how to identify when we’re at the SUB-CATEGORY level, and managed to display ‘NO GOAL’ when at that level, now we need to filter out some of the rows (but only when at this level).

Records to Show

IF [Max Sub-Cats in Window] = 0 THEN FALSE
ELSEIF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window])
THEN //at lowest level
IF ATTR([MONTH]) < [Today] THEN TRUE
ELSE FALSE
END
ELSEIF [# Sub-Cats] = 0 AND [Max Sub-Cats in Window] = 1 THEN FALSE //still at lowest level but no sales so exclude
ELSE TRUE
END

This took a bit of trial & error to work out, and there may well be something much more simplified.

Adding this to the table, and again setting to compute by MONTH you can see which rows are reporting True and False. When collapsed to the SEGMENT or CATEGORY level, all rows are True, but the values start to vary when at the SUB-CATEGORY level.

Now, we need to work on the fields to help show us the actual & % difference.

RESULT

IF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window]) THEN NULL ELSE SUM([ACTUAL SALES])-([SALES GOAL]) END

The difference should only show if we’re not at the lowest level. This needs to be custom formatted as

↑#,##0;↓#,##0

I use this site to find the arrows needed.

ACTUAL vs GOAL

[RESULT]/SUM([ACTUAL SALES])

will give me the % difference (format to percentage with 0 dp).

And the final piece of data we need (I think) is to compute a RAG status for each row. This is dependent on some parameters

GREEN (WITHIN X%)

Type Float, set to 0.1 but displayed as a percentage

and similarly

RED (ABOVE Y%)

With these we can work out

RAG

IF ABS([ACTUAL vs GOAL]) > [RED (ABOVE Y%)] THEN ‘WAY OFF TRACK’
ELSEIF ABS([ACTUAL vs GOAL]) < [GREEN (WITHIN X%)] THEN ‘ON TRACK’ ELSEIF ATTR([MONTH])>=[Today] THEN ‘NO COMPARISON’
ELSEIF [SALES GOAL]=0 THEN ‘NO COMPARISON’
ELSE ‘OFF TRACK’
END

If you add all these fields to your table then you can check how they all compare/change as you expand/collapse the hierarchy (make sure you set the compute by MONTH on all fields).

So now we’ve got all the building blocks you can create the actual table.

I’d recommend duplicating the above (and keeping as a ‘check data’ sheet). Then remove the fields you don’t need, and add the Records To Keep = True to the Filter shelf. Format the table appropriately, and add the title so you’re left with

Building the Line Chart

As mentioned above, I started with this chart, but ended up having to revisit it as I found I needed to use different pills that got created as I built the table. There is actually 1 additional field needed, which isn’t obvious until you start playing with the dashboard actions later:

GOAL

IF [SALES GOAL] > 0 THEN [SALES GOAL] END

This is needed so that when we filter by SUB-CATEGORY later and there is no goal, we only get a single line displaying the sales values, rather than also having a line plotted at 0.

  • MONTH on Filter, excluding Year 2016
  • MONTH (exact date, continuous) on Columns
  • Measure Values on Rows and Filtered to ACTUAL SALES and GOAL
  • Coloured accordingly by Measure Names

Then add TODAY to Detail and add a Reference Line to the MONTH axis, setting it to TODAY, adjusting the format of the line, and setting the fill above colour to grey.

Format the gridlines, tooltips & axis accordingly to match

Barcode / Strip Chart

For this we’re creating a bar chart using MIN(1), and fixing the axis to range from 0-1 so it fills up the entire space as follows

  • MONTH on Filter excluding Year 2016
  • MONTH as exact date, discrete (blue pill) on Columns
  • MIN(1) on Rows
  • RAG on Colour adjusted accordingly
  • ACTUAL vs GOAL on Tooltip

Then use the ‘type in’ functionality to create a pill labelled ‘vs Goal’ on the Rows

Creating the Dashboard

I used a vertical container to add the components onto the dashboard, as I used blank objects that are formatted with a black background, 0 padding and a height of 4 to create the horizontal line separators.

The interactivity between the table and the other charts is managed via Dashboard Filter actions.

To get these to work properly though, and in such a way that the selected SEGMENT and/or CATEGORY and/or SUB-CATEGORY can be referenced in the title of the line chart, 3 separate dashboard actions are required, with each one specifying the field to filter by, as follows :

Once these are set up, the associated pills will appear on the Filter shelf of the Line and Barcode charts, and are then available for selection when building the title of the line chart

You should now have your completed chart.

My published version is here.

Happy vizzin’! Stay Safe!

Donna

What is the distribution of total orders by customer?

Donna ColesLeave a comment

Luke Stanke set the challenge this week, and posted a ‘sneak peak’ on Twitter before the challenge was formally released

Challenges from Luke can sometimes be on the harder end of the scale, so with the bit of extra time available and only the gif in the tweet as a clue , I had a play to see if I could get close at all. And surprisingly I could, so once it was formally issued, it was just a case of tidying up some of my calcs.

Calculations

First up we need to establish the basic calculations

# Customers

COUNTD([Customer ID])

# Orders Per Customers

{FIXED [Segment],[Customer ID]: COUNTD([Order ID])}

Plotting these out onto a table with Segment we get

Note #Orders Per Customers needs to be set to be a discrete dimension.

So while this shows us a summary of the number of customers, we’ve only got 1 mark showing the summarised count. When plotting the chart we need something else in the view that will generate more marks. This is Customer ID.

At this point in order to help us build up our tabular view to ‘see’ what’s going on, I’ll filter the table to just show Segment = Consumer, and I’ll add Customer ID to Rows

As expected, our # Customers is now showing a count of 1 per row, but the data is now expanded as we have now got a row (ie a mark) per customer for each # Orders Per Customer ‘bucket’. But we still need a handle on the total customers in each ‘bucket’.

Customers Per Order Count

WINDOW_SUM([# Customers])

Adding this to the view and setting the table calc to compute based on Segment & Customer ID, we get the summarised value back again.

But we don’t want to actually show that number of marks; the number of circles to plot on the chart is dependent on a user parameter:

pMarkIndicator

Based on the requirements, if the number of customers is 15 and the user parameter is 5, then 3 circles should be drawn (15 / 5 =3 ), but if the number of customers was 14, only 2 circles should be drawn (14 / 5 = 2.8), ie the number of circles will always be set to the integer of the equal or lesser value (essentially the FLOOR() function). This can also be achieved by

Marks to Plot

INT([# Customers per Order Count]/[pMarkIndicator])

For some reason FLOOR can’t be used in the above as a table calculation is being used, but INT does the job just fine, and adding to the tabular view and adjust the table calculation accordingly we get

ie for the Consumer Segment, 6 customers have made 1 order in total, so based on batching the customers into groups of 5, this means 1 circle should be displayed. Whereas, 30 customers have made 3 orders in total, so 6 circles should be displayed.

But we can’t actually reduce the amount of rows (ie marks) displayed – we either have 1 row (by removing Customer ID) or a row per customer. But that’s fine, we don’t need to.

What we want is something in our data to group each row into the relevant batch size.

First up let’s generate an ID per row for each customer that restarts for each #Orders Per Customer.

Index

INDEX()

Add this as a discrete pill to Rows, and adjust the table calc

Now we have this, we calculate which ‘column’ each customer can sit in, a bit like what we would do if we were building a small multiple table, arranging objects in rows and columns (see here for an example of what I mean).

Cols

[Index]%([Marks to Plot])

This uses the modulo (%) notation which returns the remainder of the division sum. Lets put this on the view

For customers who have only ordered once, and where we’re only going to plot 1 mark, the Cols value is the same (0) for all rows.

Whereas for customers who have ordered twice, and where we want to plot 2 marks, the Cols value is either 0 or 1.

We’ve now got a value we can use to plot on an axis. We’re still going to plot a mark for each Customer ID, but some marks will be plotted in the same position, ie on top of each other, which therefore looks like just one mark.

Let’s show this more graphically, by duplicating the sheet, deleting some pills, moving some around, changing some to continuous, and setting the mark type to circle as below

Change the pMarksIndicator parameter and the number of circles will adjust as required.

So far, so good. We’ve got the right number of marks, it’s just not looking as nice and symmetrical as it should be.

We need to shift the marks to the left. But how far it shifts is dependent on whether we’ve plotted an odd or even number of marks.

If we have an odd number, the middle mark should be plotted at 0. If we have an even number the middle two marks should be plotted at -0.5 and +0.5 respectively. The calculation below will achieve this

Cols Shifted

IF [Marks to Plot]%2 = 0 //we’re even
THEN [Cols] – ([Marks to Plot]/2) + 0.5
ELSE //we’re odd
[Cols] – (([Marks to Plot]-1)/2)
END

To demonstrate this, I’ve added Cols Shifted along side Cols on the viz (this time make sure all the table calculation settings (including the nested calcs) are applied to compute based on Customer ID only which is different from the calcs above)..

Now you can see how it all works, you can remove the Cols and the Segment from the Filter shelf.

And now its just a case of applying the various formatting to clean up the display, and adding to the dashboard.

My published version is here.

Happy vizzin’! Stay Safe!

Donna

How does the company perform in the fiscal year?

Donna ColesLeave a comment

Guest poster Ivett Kovacs was back to set the weekly #WOW2020 challenge this week, and delivered a very ‘relatable’ business challenge – Fiscal Year to Date reporting.

I’ve been quite used to doing this type of reporting within my job, so on the whole I found the core requirements pretty straightforward – there’s just a lot of calculated fields 🙂 The trickiest part I found was getting everything organised on the dashboard and making the budget parameters appear/disappear based on the filter selected – this took an extraordinarily looonnnng time even though I knew the technique – more on that later.

Setting up the Fiscal Year

The data source provided was one curated by Ivett. It contained financial ‘transactions’ against Account Codes from Oct 2018 to May 2020.

The date in the file was initially recognised by Tableau as a string, but simply changing it to a Date datatype in the Data Source pane easily resolved this.

The fiscal year starts on 1st October, so once on a sheet, set this by right-clicking on the Accounting Date field and choosing Default Properties -> Fiscal Year Start -> October

If you add Accounting Date to a sheet and expand from Year -> Month, you’ll see that the Year part is now labelled as FY 2019 and FY 2020, and Q1 starts in October

Just a couple of points to be aware of if you’re not familiar with working with Fiscal Years.

  • A relative date filter for ‘this year’ will recognise your fiscal date setting, so if you did this on this data set, you’d get data from Oct 2019 to May 2020.
  • Any date related functions such as YEAR([Date]) or DATETRUNC(‘year’, [Date]) does not recognise the fiscal year setting. So YEAR(#2019-11-01#) will return 2019 and YEAR(#2020-01-01) will return 2020 even though they are both in the same FY 2020 fiscal year. It does mean at times, depending on what you’re building, you may need to hard-code information that defines the start month of your fiscal year.

Building all the calculated fields

First up we need a couple of measures as stated in the requirements.

Sales

IF STARTSWITH([Account Number],’5′) THEN [Value] END

only counts for Account Number starting with a 5

OPP

IF STARTSWITH([Account Number],’5′) OR STARTSWITH([Account Number],’696′) THEN [Value] END

only counts for Account Numbers starting with a 5 or 696.

Then we need lots of fields to help us get all the various data needed. This is simply going to be a big list 🙂

Current Month

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

finds the maximum date in the dataset and sets to the 1st of the month, in this instance it will be 01 May 2020. This is a more generic approach than harcoding.

FY Start Current Year

#2019-10-01#

I said there might need to be some hardcoding! Ideally, having set a fiscal year, you’d like a function along the lines of DATETRUNC(‘fiscal year’, [Date]) to give you this value. Apart from setting this value in a parameter, I’m not aware of any way you can determine this date without any sort of hardcoding 😦 This KB article tries to provide some suggestions, but you still need to hardcode a value to ‘shift’ to the appropriate month; in this case we’d have to hardcode 10 as October is the 10th month. Feel free to upvote this idea on the Tableau Forums which asks to address this 🙂

Current FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [Sales] END

only return sales values for dates from 01 Oct 2019 to 31 May 2020.

Current FTYD OPP

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [OPP] END

as above but for the OPP measure.

Current Month Prev FY

DATE(DATEADD(‘year’,-1,[Current Month]))

go back 1 year to 1st May 2019.

Prev FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date])<= [Current Month Prev FY] THEN [Sales] END

Only return sales values for dates up to 31 May 2019. Note the data only starts from 01 Oct 2018, which is the start of the previous FY, but if we couldn’t guarantee that, we’d have stored an ‘FY Start Prior Year’ and added an additional clause to the above calculation.

Prev FYTD OPP

IF DATETRUNC(‘month’,[Accounting Date])<=[Current Month Prev FY] THEN [OPP] END

as above for OPP.

Curr vs Prev Sales Diff

SUM([Current FYTD Sales]) – SUM([Prev FYTD Sales])

the actual difference in sales

Curr vs Prev Sales Diff %

[Curr v Prev Sales Diff] / SUM([Prev FYTD Sales])

the % change in sales

Curr vs Prev OPP Diff

SUM([Current FYTD OPP]) – SUM([Prev FYTD OPP])

the actual difference in OPP

Curr vs Prev OPP Diff %

[Curr v Prev OPP Diff] / SUM([Prev FYTD OPP])

the % change in OPP

To deal with the Budgets, we need to create a couple of parameters Budget Sales (M) and Budget OPP (M). Both are integer parameters defaulted to 2,300 and 3000 respectively.

But because these parameters aren’t actually in millions, we need further fields to translate them into the value we really need for comparisons.

Budget Sales Ref Line

[Budget Sales (M)] * 1000000

Budget OPP Ref Line

[Budget OPP (M)] * 1000000

and now we can compute the differences

Curr vs Budget Sales Diff

SUM([Current FYTD Sales]) – MIN([Budget Sales Ref Line])

We need the MIN() function as the same Budget Sales Ref Line value is stored on every row, so SUM() will multiply the value too much. AVG() or MAX() would have worked just as well.

Curr vs Budget Sales Diff %

[Curr v Budget Sales Diff] / MIN([Budget Sales Ref Line])

and again we need to duplicate these for the OPP measure

Curr vs Budget OPP Diff

SUM([Current FYTD OPP]) – MIN([Budget OPP Ref Line])

Curr vs Budget OPP Diff %

[Curr v Budget OPP Diff] / MIN([Budget OPP Ref Line])

Now we’ve got all the core variables we need, we need to determine which one to use as the comparison in the bar chart and the KPI indicator. This is based on a parameter for the user to decide if they’re comparing Actuals against Budget or against Previous Year Actuals.

Compare Filter

I chose to create an integer based parameter with the display altered to show the relevant text. We will be using this parameter in calculated fields and comparing integers rather than strings is much more efficient (and easier to read).

So with the parameter set up, we now need to create a few fields that we’ll use to show the values we want

Sales Diff

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff] ELSE [Curr v Prev Sales Diff] END

This is custom formatted to display as $ in millions with an arrow to show positive or negative : ▲”$”#,##0,,M;▼”$”#,##0,,M

Sales Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff %] ELSE [Curr v Prev Sales Diff %] END

This is formatted to 1 decimal place.

Opp Diff

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff] ELSE [Curr v Prev OPP Diff] END

Same custom formatting as above.

Opp Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff %] ELSE [Curr v Prev OPP Diff % ] END

Sales Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget Sales Ref Line]) ELSE SUM([Prev FYTD Sales]) END

This is used for the line shown on the bar chart, and we’ll need the same for the Opp measure.

Opp Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget OPP Ref Line]) ELSE SUM([Prev FYTD OPP]) END

Finally we need a couple of fields to use to work what colour the bars need to be.

Colour : Sales Diff

IF [Sales Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [Sales Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Colour : OPP Diff

IF [OPP Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [OPP Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Note – my published solution has something slightly longer winded as when I originally built the viz, I created the colour fields before I created the generic Diff/Diff% fields referenced above.

Right! That’s a LOT of calculated fields (I did warn you!). In some cases it may have been possible to combine, but I like creating building blocks to keep things simpler to read.

Sales YoY Trend Line

The basis of this type of chart is pretty much Desktop 101.

  • Month(Accounting Date) on Columns (blue pill)
  • Sales on Rows
  • Year(Accounting Date) on Colour with colours adjusted accordingly.
  • Add the average line and Label the most recent point
  • Format the gridlines/rows etc and axis

The most recent mark is a larger circle. We need another calculated field for this

Current Month Sales

IF DATETRUNC(‘month’,[Accounting Date]) = [Current Month] THEN [Sales] END

This just stores the sales value for the latest month.

Add this field to the Columns and make dual axis and synchronise axis. Adjust the marks back to be a line and a circle and adjust the Sizes to suit.

Duplicate all the above on another sheet for the OPP values instead.

Sales Bar Chart

Simple bar chart

  • Current FYTD Sales on Columns
  • Colour : Sales Diff on Colour
  • Sales Ref Line on Detail
  • Add Sales Ref Line as a reference line and label & format to suit.
  • Label bar and align middle right; format to suit.

The colour legend will only display a single option at a time. You’ll need to set the Compare Filter to compare against budget and show, then adjust, the value of the Budget Sales (M) parameter to values that go beyond the thresholds, to set the other colour options.

Repeat all this again on another sheet for the bar displaying the OPP measure.

Sales KPI

Simply add Sales Diff & Sales Diff % to the Text. Format row/column lines to suit.

Again, repeat for the equivalent OPP measures.

Year Legend

The standard legends aren’t used as these are only square icons, but the challenge shows circles. So a custom legend sheet is created as below

The hidden axis has been fixed from 0.45-1 to push the display to the left more.

% Diff Indicator Legend

Again the standard legend can’t be used this time, as only 1 option ever shows. So this is a custom ‘fake’ legend.

I simply used the Account Number field for this purpose. I filtered a sheet to just 3 different Account Numbers. I then built a similar viz to the above, but using Account Number throughout.

The sneaky trick I used here was to simply ‘alias’ the actual Account Numbers displayed to the text I wanted.

Building the dashboard

Getting all the objects in the right places can be a bit of trial and error. All the objects on my dashboard, apart from the Budget parameters, are tiled using a mix of horizontal and vertical containers, nested within.

Hide/Show the Budget Parameters

This probably took me the most time. It’s using a technique sometimes referred to as Parameter Popping. It uses containers and works similarly to sheet swapping.

I had to build a ‘blank’ sheet for this – a sheet that will show (a blank value) and hide based on the Compare Filter parameter.

I needed another field

Is Prev Value to Compare

[Compare Filter]=1

This returns true if the parameter is set to compare against the Previous Year rather than the Budget.

This is added as a Filter to the blank sheet and set to True, which means the sheet will ‘show’ when comparing to Previous Year, and hide when comparing to Budget.

A floating horizontal container is then added to the sheet, and all the objects – the Budget parameters, some Text boxes to label the parameters and the ‘blank’ sheet are ‘carefully’ added. The blank sheet should be the first object on the left. I say ‘carefully’ as it took a lot of trial and error to make it work. If it is done right, then changing the Compare Filter parameter should make the budget parameters move left & right as the blank sheet shows and then hides.

The floating horizontal container is made wider than the dashboard itself, so the showing of the ‘blank’ sheet, pushes the other objects so far to the right that they’re not on the dashboard at all.

The screen shots below show what it looks like in Desktop

As I said, Parameter Popping can be a bit tricky – I only used it twice, so haven’t yet found a sure fire way to get it right first time. If you google ‘Tableau Parameter Popping’ you’ll find a few links that might help, and/or check out Week 4 of #WOW2020 which also uses it (the other time I’ve used it).

And that’s about it. My published viz is here. Enjoy!

Happy vizzin’! Stay Safe!

Donna

Can you add and remove items from a set?

Donna ColesLeave a comment

Lorna set a fun ‘create your own pizza’ challenge this week to demonstrate the ability to both add and remove items in a set via the use of Set Actions, a feature introduced in v2020.2 (so you’re going to need this to complete the task).

There’s essentially 5 components to this dashboard, which I’ll guide you through

  • The central bar chart
  • The graphical product type selector on the left
  • The list of selected products to the right
  • The actual vs budget bar at the top
  • An indicator of how much you’re over/under budget

Central Bar Chart

The essence of this is a simple Type, Product by Price bar chart, coloured by Type. Manually move the Type field so the ‘Size’ option is at the top.

To indicate if the product is vegetarian or not, we create

Veg Indicator

IF [Vegetarian] = ‘Yes’ THEN ‘●’ ELSE ” END

which we can add to the Row shelf, and format to be coloured green (I use this site to get the shapes).

The tick is used to indicate if the product has been added to the pizza or not. Selected items will be identified by the use of a set, so we need to create one. Right-click Product and Create->Set. Tick a few options.

And similar to the Veg Indicator we can build

Selected Product Indicator

IF [Selected Products] THEN ‘✔’ ELSE ” END

Add this to the Row shelf between Type and Product.

The order of the Products listed will change. I want it to be ordered alphabetically by Product within each Type. The quickest way to resolve this, was to duplicate the Product field, add it to the Rows between the Type and Selected Product Indicator pills, and then hide it.

The final requirement for this chart, is to highlight the selected products in a different shade of the ‘base’ colour.

Add Selected Products to the Detail shelf, then change the … detail icon to the left of the pill to the colour icon. This will mean there are 2 pills on the Colour shelf, and the colour legend will change. Adjust to suit

The chart just then needs formatting to add the Price to the Label of the bar, remove the row/column lines, hide the Type column, hide the field labels, hide the axis, and adjust the width of the columns.

Product Type Selector

Lorna provided the images needed for this, which need to be saved to a new folder in the Shapes directory of your My Tableau Repository. I called my new folder simply Custom.

Then on a new sheet, add Type to Rows, set mark type to Shape and add Type to shape too.

Hide the Type heading and remove the row lines.

Selected Products List

Add Type & Product to Rows and enter MIN(1) onto the Columns. Add Selected Products to the Filter shelf to restrict the list just to those in the set.

Add Type to Colour and Product to Label, changing the font to white and centring. Fix the axis from 0-1 and hide it. Hide the Type & Product columns.

Manually reorder the Type field to be listed Size -> Crust -> Sauce -> Toppings

Actual vs Budget Bar

The budget field can be changed by the user, so we need a parameter, Budget, for this, which is an integer parameter where the display is formatted to £ with 0dp, and defaulted to 15.

Create the bar by adding Price to Columns, Selected Products to Filters and Type to Colour. Reorder the items in the colour legend to get the colours displaying in the correct order with ‘Size’ on the left and ‘Toppings’ on the right.

Add the Budget parameter to the Detail shelf, then add a Reference Line which refers to the Budget. Adjust the label displayed to be custom as shown below, and format the thickness and colour of the line to suit

Format the reference line so the text is displayed at the top right, and edit the font & size of the label displayed.

Then make the row height of the chart taller, but adjust the size of the bar to be narrower. The reference line spans the whole height of the row, so reducing the height of the bar itself provides space for the label to display without overlapping the bar.

Hide the axis and remove gridlines etc.

Finally we need to display the total price of the products selected. We can’t just add Price to Label as there are multiple segments on the bar, and this will display a Price per Type.

Instead we need a new field

Total Price Selected Products

{FIXED [Selected Products]:SUM([Price])}

Since this chart is filtered by Selected Products = True, this returns the total price of all the items in the set (selected). Add this to the Columns shelf and make dual axis & synchronise axis.

Readjust the mark types, so the Price marks card is back to a bar, and the Total Price Selected Products is Text. Remove the Measure Names pill from the Colour shelf of both cards (the dual axis will have automatically added it), and also remove the Type pill from the Colour shelf of the Total Price Selected Products card. Instead, add Total Price Selected Products to the Text shelf of this card.

The text will overlap the bar whether you align left , middle or right. The trick is to add some spaces in front of the text and then align right.

Hide the axis.

Over / Under Budget Indicator

This is just a text display, but a few calculated fields are needed so the text can be coloured differently depending on whether it’s over or under.

First we need to know the difference from budget

Diff from Budget

[Budget]-[Total Price Selected Products]

Text Budget Diff Over

IF [Diff From Budget] < 0 THEN ABS([Diff From Budget]) END

This will just display the difference if the value is negative, but the ABS function will return the number as a +ve. This field should be formatted to £ with 2 dp.

Text Budget Diff Under

IF [Diff From Budget] >= 0 THEN [Diff From Budget] END

This will just display the difference if the value is positive. Format to £, 2 dp.

Only one of these fields will ever hold a value at any time.

Similarly we need fields to show the text to display.

Over Budget Text

IF [Diff From Budget] < 0 THEN ‘Over Budget’ END

Under Budget Text

IF [Diff From Budget] >= 0 THEN ‘Under Budget’ END

On a new sheet, add Selected Products to the Filter shelf, then add all four of the above fields to the Text shelf.

Adjust the order and colour of the fields in the Label editor, setting the fields related to being ‘over budget’ to red and the ‘under budget’ fields to green.

Now we’ve got all the building blocks, we can put it all on a dashboard.

Building the dashboard interactivity

Add all the sheets in their various locations. Most were tiled, except for the Budget parameter and the Over/Under Budget Indicator sheet, which I floated.

Filter the bar chart

Add a dashboard Filter Action to filter from the Type Selector sheet to the Product Bar Chart on select, filtering on the Type field only.

Add / Remove from Set

Add a dashboard Set Action to the Chart sheet that runs from the Menu, and adds the selected item to the Selected Products set.

Create another Set Action on the same chart, which removes values instead.

And that’s the main crux of the challenge. The only addition is ‘nice’ feature to avoid the item selected from being shown as selected (ie fading out all the other items in the chart).

I reverted to the trusty true = false dashboard action which I applied to the Selector chart and the Selected Items chart.

This involves creating 2 new fields True = True and False = False and adding these to the Detail shelf of the relevant chart.

A dashboard Filter action is then added which targets itself using the fields True = False

However, when I tried to add this same feature to the main bar chart itself, the bar chart stopped working as expected. I think there was conflict between there being 2 filter actions on the same chart. I have to admit, this is where I did check Lorna’s solution, as this was a small feature that really bothered me, and I didn’t want to publish without it.

It turns out she simply used a highlight action to resolve this

I vaguely recall using this sometime ago, but the ‘true=false’ concept has become so ingrained as my ‘go to’ method, that I struggled to think of this.

And that should be it. You now have the tools to customise your own pizza 🙂

My published viz is here. Enjoy!

Happy vizzin’! Stay Safe!

Donna

Can you compare a 3-day vs 14-day moving average and describe the latest trend?

Donna ColesLeave a comment

This week for #WOW2020, Ann provided a table calculation feast of a challenge! This certainly is not for the faint-hearted! As well as cracking all the table calcs, the challenge features multiple views, measure swapping, parameters, BANs, filtering, sorting …. it’s got it all going on!

Ann hinted you’d probably want to start with the table, and even if there hadn’t been a table output in the display, this is what I would have done. If you’ve read enough of my blogs, you’ll know I often like to build up a ‘check data’ sheet, which just contains the data I need in tabular form as a quick reference. When working with table calculations this is an absolute must have!

So let’s build out that Check Data table to start with. I have a feeling this is going to be a lengthy blog 🙂

Initial Set up

First up, the requirements stated that the latest date would be 7 June, but I found records with a 8 June date. All the associated info for this date was null though, so I set a data source filter to exclude this. This means I wouldn’t get any issues if I needed to store the max date in a FIXED LoD calculation at any point.

I also found it easier to rename a couple of the measures provided to match the output, so rename PEOPLE_POSITIVE_NEW_CASES_COUNT to New Cases and PEOPLE_POSITIVE_CASES_COUNT to Reported Cases. I’ll refer to these renamed fields going forward.

Building all the Calculated Fields

To build out the table, we’re just going to focus on one State & County, as there’s a lot of data. So add Province State Name = Tennessee and County = Davidson to the Filter shelf.

Add Report Date (discrete exact date – blue pill) and New Cases & Reported to Rows. As you scroll down, you’ll see data starting to come in on 8 March.

We want to create our moving average calculations

3 Day Moving Avg

WINDOW_AVG(SUM([New Cases]), -2, 0)

14 Day Moving Avg

WINDOW_AVG(SUM([New Cases]), -13, 0)

Notice the number of rows to average over is 1 less than you might expect, as the current row is included, so the calculation is saying ‘current row’ and 2 | 13 previous rows.

Add these to the table, and adjust the table calculation so it is explicitly calculating by Report Date. This would have happened automatically, as the calculation would have been computing ‘down’ the table, but it’s best to fix the computation, so it doesn’t matter where the pill gets moved to in the view.

We now need to work out whether there is an increase or not between the 3-day and 14-day average.

Is Increase?

IF [3 Day Moving Avg] > [14 Day Moving Avg] THEN 1 ELSE 0 END

Is Decrease?

IF [3 Day Moving Avg] <= [14 Day Moving Avg] THEN 1 ELSE 0 END

I’m using 1s and 0s as it’s going to help with a later calculation.

NOTE – I’m assuming that if there is ‘no change’ it’ll be recorded as a decrease. This is how I interpreted the requirement, “ …whether it is an increase or a decrease (or no change)” and it wasn’t easy to find any matches anyway.

I also need some text to indicate the increase or decrease

Increase | Decrease

UPPER(IF [Is Increase?]=1 THEN ‘Increase’ ELSE ‘Decrease’ END)

The UPPER is used as that’s part of the tooltip formatting.

Let’s get these onto the view, always making sure the table calculations are set to Report Date.

We need to calculate the number of days that has been reported INCREASE in succession, and the number of days where successive DECREASE has been reported.

So first, let’s identify which rows match the previous row.

Match Prev Value?

LOOKUP([Is Increase?],-1) = [Is Increase?]

If the value of the Is Increase? field in the previous (-1) row is the same as the Is Increase? field in the current row, then this is true, else false.

Add to the view, and verify the table calculation for itself and all nested calculations being referenced, is set to Report Date.

We now have all the information we need to help us work out the number of days in the increase/decrease ‘trend’.

Days in Trend

IF (FIRST()=0) OR(NOT([Match Prev Value?])) THEN 1
ELSEIF [Increase | Decrease] = ‘INCREASE’ THEN ([Is Increase?]+PREVIOUS_VALUE([Is Increase?]))
ELSEIF [Increase | Decrease] = ‘DECREASE’ THEN ([Is Decrease? ]+PREVIOUS_VALUE([Is Decrease? ]))
END

If the row in the table is the very first entry (so there’s nothing previous to compare against), or the row in the table didn’t match it’s predecessor (ie there was a change), then we’re starting a new ‘trend run’, which obviously starts at 1.

Otherwise, if the current row we’re on indicates an increase, then we’ll add the value of the Is Increase? field (which is 1) to the previous value (which is also 1). PREVIOUS_VALUE works recursively though, so it essentially builds up a running sum, which gives our trend.

We ultimately do the same thing using the Is Decrease? column. This is why using 1 & 0s in the earlier calculation help.

Adding into the view, and setting the table calculation correctly, you should get something similar to this…

Finally, there’s one key field we need to add; something to help identify the latest row as we will need it for filtering in the table that’s displayed on the dashboard. Simply applying a standard ‘quick filter’ won’t work, as the table requires we show the 3-day & 14-day moving averages. A ‘quick filter’ to limit the data to the latest date (7th June), will show the wrong values, as the data related to the other days will be filtered out, so the table calc won’t have the information to correctly compute over.

We need to create another table calculation that we can use as a filter, and that due to Tableau’s ‘order of operations’ will apply later in the filtering process than a traditional quick filter.

Max Date

{FIXED : MAX([Report Date])}

The latest date in the whole data set.

Show Data for Latest Date

LOOKUP(MIN([Report Date]),0) = MIN([Max Date])

If the Report Date of the current row is the same as the maximum date in the whole data set, then return true.

We’ve now got all the core data components we need to create the various charts.

In the interest of time (my time in writing this out), I’m going to attempt not to describe the building of all the charts in too much detail, but just call out the useful bits you might need. If you’re attempting this challenge with the table calcs above, I’m assuming you know Tableau enough to not need everything defined to the lowest level.

The whole report is driven off a parameter which the user must enter a State – County combo.

You’ll need a calculated field to store the combo

State – County

[Province State Name] + ‘ – ‘ + [County]

and then create a parameter (State – County Parameter) off of this (right click, Create -> Parameter) which will create a string parameter with all the permutations.

When displaying on the dashboard, set this to be of type Type In

BAN

The BAN is a basic summary of the latest trend for the entered state county.

We need to filter the sheet to the value entered in the parameter

Is Selected State County?

[State – County Parameter] = [State – County]

Add this to the Filter shelf as true, along with the Show Data for Latest Date.

Add the relevant fields to the Text shelf to display the required text. The Report Date needs to be custom formatted to ddd, mmm d to get the Sun, Jun 7 display

Map

For the map, as well as filtering the latest date, we’re also going to need to filter just to the state only (not state & county) as above. So I created

Is Selected State?

LEFT([State – County Parameter], FIND([State – County Parameter],’-‘)-2) = [Province State Name]

This is unpicking the State – County combined string stored in the parameter, to just find the State part and compare to the Province State Name.

Build a filled map based on County and filter to the latest date and the selected state. I set the Map Layers to that below, which seems to match up

You’ll need to set both the Is Selected State County? and Increase|Decrease fields to the Colour shelf.

Bar & Line Chart

You’re going to need a few more calculated fields for this.

Moving Avg Selector

for the user to choose what the line should display. I’ve set it to an intger parameter that displays text

We then need a field to show on the display depending on what’s been selected in the parameter

Moving Avg to Display

If [Moving Avg Selector] = 3 THEN [3 Day Moving Avg] ELSE [14 Day Moving Avg] END

You’ll need a Dual Axis chart plotting New Cases and Moving Avg to Display against Report Date (continuous exact date)

The data only starts from 8th March, so I added Report Date to filter to start from 8th March. 8th March is also added as a constant reference line.

Table

Based on the State / County entered, the table is filtered to show the data for the latest date for all the counties in the state entered. Although not stated in the requirements, the first row is the county selected, with the rest ordered by Reported Cases.

You can get the selected county to the top, by adding Is Selected State County as a hidden field to the Rows, and moving ‘True’ to the top.

And that should be everything you need to build the dashboard, which is pretty much just stacking all the sheets one on top of each other in a single column.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you excel at bar charts?

Donna ColesLeave a comment

When Luke’s #WOW2020 challenge landed this week, I did a little happy dance inside, as I was pretty sure I was going to be able to crack this fairly quickly with minimal head scratching effort. This isn’t because I create these types of charts often, but because this type of side-by-side bar formed the basis of a #WorkoutWednesday challenge way back in 2017, and is a technique I can still recall – I didn’t even have to check my published workbook for a reminder… there’s just some techniques that just ‘stick’.

There may well be other ways to solve this challenge, but the technique I know is based on ‘normalising’ and ‘jittering’ dates, and is one I recall I picked up from Zen Master Jonathan Drummey’s Bars & Lines Blog post and associated workbook which can be downloaded from the blog.

So let’s gets started & hopefully, all will become clear.

Building the chart

In the dataset we’re using we have 4 years worth of orders, and need to plot the value of sales per month, per year in a bar chart which ‘groups’ the yearly bars for the same month together.

We’re going to plot the dates on a continuous axis (green pill), which typically would give a bar chart that looks like this, where Year(Order Date) is also added to the Colour shelf (Note I’ve also adjusted the colours to match the requirements).

We have 1 bar per month per year, but the months are ‘grouped’ in their sequential years.

So the first thing we want to do, is ‘normalise’ the dates as if they all occurred in the same year – any year is fine, I’m going to baseline them all to 2019

Date Normalised

MAKEDATE(2019,MONTH([Order Date]),DAY([Order Date]))

As you can see if plotting Order Date alongside Date Normalised this is simply transposing 03 Jan 2016 to 03 Jan 2019, 03 Jan 2017 would also be transposed to 03 Jan 2019

Replacing Order Date with Date Normalised on our initial chart gives us

But we want the bars side by side.

When using the automatic date hierarchy to plot dates at the month level, what Tableau is doing ‘under the bonnet’, is ‘truncating’ each date to the 1st of the month; so 3rd Jan 2019 and 18th Jan 2019 etc, are both actually plotted at 1st Jan 2019, and so on.

And when a bar chart is used to plot on a date axis, the left hand side of the bar is plotted at the 1st of the month point.

So with all this in mind, what we’re going to is ‘jitter’ the dates for each year to be clustered before the 1st of the month (for 2016 & 2017) and on/after 1st of the month (for 2018 & 2019).

Date Jitter

CASE YEAR([Order Date])
WHEN 2016 THEN DATEADD(‘day’,-9, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2017 THEN DATEADD(‘day’,-4, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2018 THEN DATEADD(‘day’,1, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2019 THEN DATEADD(‘day’,6, DATETRUNC(‘month’, [Date Normalised]))
END

If the year of the original Order Date is 2016, then truncate the normalised (2019) version of the date to the 1st of the month, but subtract 9 days. So if the Order Date is 03 Jan 2016, then transpose it to 03 Jan 2019, then truncate to 1st of month, 01 Jan 2019, then subtract 9 days to 23 Dec 2018

03 Jan 2016 -> 03 Jan 2019 -> 01 Jan 2019 -> 23 Dec 2018

Depending on the year depends on whether dates are subtracted or added, and they have a suitable spacing between. The table below shows the relationship between the Order Date, the Normalised Date and the Date Jitter

Replacing Date Normalised with Date Jitter like for like, gives us the same view though, since it’s automatically rolled up to ‘month’

Change the Date Jitter to Exact Date

and ta-dah! you have your side-by-side chart. Each bar is being plotted at the exact date. If we just filter to look at May for example, we can see this clearer

The bars are obviously thinner than we want, so adjust the Size to be Fixed with a value of 4

To get the Tooltip to display the correct month and year, you need to add Order Date to the Detail shelf and change it to the discrete Month level (blue pill), then format the pill to display as an abbreviated month.

To get the axis to display an abbreviated month name, format that too and set to custom formatting of mmm.

Finally, to get the bottom axis and axis ticks to be darker, adjust the Columns Axis Ruler and Axis Ticks to be a solid dark line, then edit the axis, and delete the axis title

Your bar chart should be complete

Building the Legend

F0r the legend, I simply created a very simple existence chart using the circle mark type as below

And that’s it (once added to a dashboard of course!). The date jittering is a useful technique to be aware of – I’m pretty sure I’ve used it in other challenges too, but not necessarily for a side by side bar chart.

My published viz is here.

Note, my Date Normalised & Date Jitter fields in my published viz aren’t exactly as detailed above, because I decided to jitter then normalise originally, which meant I had to make an additional adjustment in my normalisation calculation. It’s only when typing this blog out and essentially rebuilding as I go, that I realised it was so much simpler the other way round!

Happy vizzin’! Stay safe!

Donna

Profitability Spotlight – is your budget recovered?

Donna ColesLeave a comment

For week 22 of #WOW2020, Ivett Kovacs set her first challenge as a guest poster.

At first glance it looked like it would be tricky, and it was! There were many head-scratching moments as I made my way through this, and I couldn’t complete it all without having to look at Ivett’s solution – more on that later.

Understanding the data

First up, a word on the data provided, as the requirements weren’t as clear as I’d have liked.

Ivett provided a small custom data set to build this challenge on

It contains 2 rows for each Sub-Category. The value to be plotted for Review is evident on the challenge – it’s an average. But the aggregation for Revenue & Budget isn’t that obvious, as there is no direct indication on a label or in the tooltip. Should the values be summed or averaged, or a combination of both?

The Profit value in the tooltip is the only clue you have. The Profit value for Tables is -$60, the value for Chairs is $50. From examining the values in the data, the assumption is Profit = AVG(Revenue) – AVG(Budget), so the values to plot for Budget and Revenue need to be averaged.

The assumption is this data set is a combination of a two datasets; one containing the Revenue & Budget per Sub-Category

the other containing the customer reviews

So when it comes to the viz, we’re looking to plot based on the following data

Understanding what numbers I need to be aiming for when doing these challenges is crucial to me to ensure I’m heading down the right path 🙂

Duplicating the Data

The key thing to realise about this chart is that the right hand line is a fake axis; ie not an axis at all, just a vertical line located so that it looks like it is an axis.

What we actually need to do is build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate. This means we need 3 rows of data per Sub-Category, 1 row to represent each point being plotted.

So for this we need to Union the data twice, so there are 3 instances of the data – drag another instance of the table into the data pane and drop when the Union option appears. Do this twice.

The union duplicates the rows of data, and each set is identifiable by an automatically generated Table Name field containing values WW, WW1,WW2

Defining the points of the triangle

As I said above, we need to build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate.

At point 1, we’re going to plot our Review value. At point 2, we’re going to plot our Revenue value, and Budget will be at point 3.

From the diagram above, you can see our x coordinates are one of two numbers, either 0 or x1 (ie the x coordinate for the Revenue & Budget points is the same).

The y coordinates vary per measure, and need to be ‘normalised’ to a common scale, as otherwise, the Revenue & Budget figures would be plotted significantly higher than the Review values (you might find this blog by Zen master Jonathan Drummey useful at this point).

When normalising, you’re typically looking to convert your values to a scale from 0-1; this means the values aren’t plotted at their absolute values, but are still plotted in the same relative order to each other ie lowest value at the bottom, highest at the top.

When normalising a set of values from 0 to 1, your lowest value would typically be at the 0 position , with your highest value at the 1 position. To calculate where your value would sit on this scale, you need to know 3 numbers; the value to convert, the maximum value in the range of values to plot and the minimum value in in the range of values to plot. The normalised value is then :

(Current value – min value) / (max value – min value)

ie the difference between your value and the lowest as a proportion of the difference between the whole range.

However, in this instance, I chose to simplify the normalisation, and my method only works due to the values in the example data provided.

Side Note I consider Revenue & Budget to be values that are directly related to each other ie if Budget = Revenue I’d expect them to be plotted at the same point. I therefore chose to normalise these values across the combined range. This gave me different results from the solution, where Budget and Revenue were normalised independently of each other.

The maximum average review value is 5, the maximum value for budget and revenue combined is 100. As 100 is exactly 20 times bigger than 5, I simply chose to normalise the revenue/budget values to be on a scale of 0-5 instead, rather than normalising all values (Review, Budget & Revenue) to be on a 0-1 scale.

First up, we want to identify a position for each point

Path

IF [Table Name] = ‘WW’ THEN 1
ELSEIF [Table Name] = ‘WW1’ THEN 2
ELSE 3
END

Then we’ll create the x coordinate for each point

x

IF [Path] = 1 THEN 0
ELSE 20 END

I’ve just chosen an arbitrary value to plot the 2nd & 3rd points at – could easily have been 1.

Then we’ll create the y coordinate for each point, which is where the normalisation comes in

y

IF MIN([Path]) = 1 THEN AVG([Review])
ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20
ELSEIF MIN([Path]) = 3 THEN AVG([Budget])/20
END

Dividing by 20, normalises the values to a scale of 0-5 as discussed above.

Let’s put these values all out in a table, so we can see what’s going on

Building the Polygon Viz

You can see we have 3 points per Sub-Category, so we can plot the x & y measures on a sheet as follows :

  • Add Min(x) to Rows
  • Add y to Columns
  • Add Sub-Category to Detail
  • Add Table Name (or Path) to Detail
  • Change Mark Type to Polygon
  • Change Colour to #666666 with 30% opacity

Tooltips

We need 2 new calculated fields for the Tooltip

Profit

AVG([Revenue]) – AVG([Budget])

formatted to $ with 0 dp

Margin

[Profit]/AVG([Budget])

formatted to % with 1 dp.

Note this will differ from the solution, where I think Ivett inadvertently used SUM(Budget) rather than AVG.

Add these to Tooltip field and adjust text accordinly.

Colouring the Line

To make the line, I created a second instance of y

y2

IF MIN([Path]) = 1 THEN AVG([Review])
ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20
END

which just plots 2 points rather than 3.

Add this to Columns next to y, make dual axis and synchronise axis. Things might have disappeared – you need to remove Measure Names from both marks cards. Change the mark type of the y2 card to Line.

You won’t see much difference at this point (or you shouldn’t). We need a field to define the colour

Colour : Line

IF [Profit] < 0 THEN ‘Non-Profitable’ ELSE ‘Profitable’ END

Add this to the Colour shelf of the y2 marks card and adjust to suit.

Labelling the line

On the y2 marks card, add Review (set to AVG) to Label shelf, and move Sub-Category to Label shelf. Set Label to only label Start of Line. Adjust format/layout of Label to suit.

Set the format of Review to Number Standard – this is a format little used, but will display a whole number or a decimal. I discovered this through an Andy Kriebel WoW from a long time ago and is a real gem!

Finalising the viz

To tidy up and get the viz looking like the solution

  • Format to remove all gridlines
  • Hide the y2 axis
  • Hide the ‘4 nulls’ indicator if you have it
  • Edit the y axis
    • Fix the axis from 0.5 to 5.25
    • Add a title
    • Set axis ticks to none
  • On the y2 marks card, edit the Colour shelf and change the markers to show to All (this gives the circles on each end of the line)
  • Change the Min(x) pill to be FLOAT(Min(x)) using the ‘type in pill’ function
  • Edit the x axis to be Fixed from -0.3 to 19.9
    • cutting off the end of the axis makes the end circle disappear. This is very sneaky, and I had to see the solution for this!
  • Hide the x axis
  • Remove the row divider lines

So we’ve now got the core viz – hooray! But we’re not quite done – boo!

Expand /Collapse

Ivett set the viz to expand to show a column by Sub-Category on click.

This is another requirement I couldn’t get however hard I tried. I duplicated my viz and created a version with Sub-Category on Columns and tried to use Parameter Actions to set a parameter that would control which viz would display using a sheet swap techinque. However when I added the necessary field to the Detail shelf, all the polygons disappeared and I don’t understand why…. I therefore published my first instance of this challenge with a parameter the user controlled to decide which view to show. This is here.

So I had to look at Ivett’s solution to see how she had achieved this, and it involved sets.

Right click on Sub-Category and Create – > Set

Selected Sub-Cats

Don’t select any values at this point.

Create a field

Expand

IF [Selected Sub-Cats] THEN [Sub-Category]
ELSE ‘Click to Expand’
END

If there are values in the set, then the set values will be returned, otherwise the text ‘Click to Expand’ will display.

Add this to the Columns shelf, and ‘Hide field labels for columns‘ so the text ‘Expand’ doesn’t show.

A dashboard action will ultimately drive the behaviour, but we can test the display by editing the set and selecting all values.

The view should expand as expected, showing a column per Sub-Category

But the name of the Sub-Category is still displayed on the label, and in the example this isn’t the case. To fix this I created another calculated field

Label: SubCat

IF [Selected Sub-Cats] THEN ” ELSE [Sub-Category] END

which I added to the Label shelf, and adjusted the display to suit.

‘Reset’ the view to show the collapsed version by re-editing the set and removing all values.

How to Read this chart legend

I simply duplicated the main viz, and filtered by Sub-Category = Chairs.

I removed the label, and then used the Annotate Point functionality to add the relevant labels against the points.

Profitable Legend

The Profitable / Non-Profitable legend makes use of our trusted friend MIN(0), with pills arranged as below.

Applying the Set Action

Create a dashboard to the size specified, and just use floating objects to position the text and various sheets.

To drive the expand / collapse function, create a Set Action on the main viz as below, that targets the Selected Sub-Cats set.

Revenue / Budget Label

This is just managed using Text objects on the dashboard, carefully positioned in the right locations. You might need to add additional objects to get the layout required.

The background of the whole dashboard is set to light grey and the sheets and objects need to be set to the same grey or white to get the same presentation.

So fingers crossed, you should have a complete solution now.

My final version (after I peeked at Ivett’s) is here.

Happy Vizzin’! Stay safe!

Donna