Tag: Parameter Actions

Designing KPIs for Mobile

Donna ColesLeave a comment

Luke Stanke returned for week 43 of #WOW2020 with a challenge focussed on building KPIs for mobile consumption.

In general this looked to be (and was) less taxing than some from previous weeks, but Luke did throw in some very specific requirements which did prove to be a bit tricksy.

To deliver this solution I built 8 sheets, 1 for each KPI heading and 1 for each bar chart. The dashboard then uses a vertical layout container to arrange the 8 objects in. A filter control on each bar chart determines whether the bar chart should ‘show’ or not. When a particular bar chart is displayed it fills up the space, which makes the display look to ‘expand’. Parameter actions are used to drive the ‘expand/collapse’ functionality.

The areas of focus for this blog are

  • Building the KPI chart
  • Formatting the Bar chart
  • Expand / Collapse function
  • Ensuring the KPI isn’t highlighted on selection
  • Making the display work for mobile

Building the KPI Chart

Because we have text on the left and the right, then I built this as a dual axis chart.

I’m going to build the Sales KPI.

I used MIN(1) on Columns, with Mark Type of bar, and fixed the axis to range from 0 to 1. SUM(Sales) is then added to Label, right aligned and formatted appropriately.

For the 2nd axis, we’re going to use MIN(0) positioned alongside MIN(1) on Columns, and this time, set the Mark Type to Gantt. I type the word ‘SALES’ into the Label field.

We also need to display a + or – icon on this label too. This will rely on a parameter that is going to be set.

Create a string parameter Selected Measure which is just empty.

For the icon, then create

Sales – Icon

IF [Selected Measure] = ‘SALES’ THEN ‘▬’ ELSE ‘✚’ END

I use this site to get the characters I use for these types of things.

Add Sales – Icon to the Label shelf of the MIN(0) axis, and position in front of the ‘SALES’ text.

If you show the Selected Measure parameter on the screen, and enter the word SALES, you’ll see the shape change to ‘-‘. Make this a dual axis chart and synchronise axis, and you should have the basis of the KPI (once all the axis have been hidden of course).

We also need to add an additional field onto the chart that we’ll need to use with the parameter action later.

Sales – String to pass

IF [Selected Measure] <> ‘SALES’ THEN ‘SALES’ ELSE ” END

When the Selected Measure parameter is empty (or contains another value), then this field will contain ‘SALES’ otherwise it’ll be blank.

Add this onto the Detail shelf of the ‘All’ Marks card (ie it needs to exist on both axes).

You essentially need to duplicate this sheet and build instances of the calculated fields for for Profit, Margin (SUM(Profit)/SUM(Sales)), and Customers (COUNTD(Customer ID)).

Formatting the Bar chart

Luke is very specific in his requirements that the axis tick for the bars displayed needs to be centred. This means there needs to be a continuous (green) pill on the date axis.

This very tiny requirement gave me a lot of grief 😦

I initially used MONTH(Order Date) (which will return numbers 1-12), but when I formatted the axis to First Letter, I get a D (for 0) and J (for 13) at each end.

Fixing the axis to start from 1-12 doesn’t work, as this chops off part of the first and last bars.

I tried a variety of mechanisms but to no avail. A chat with my fellow #WOW participant Rosario Gauna, who was also mulling over the issue eventually provided a solution, courtesy of Annabelle Rincon.

We need to use an ‘undocumented’ formatting option of mmmmm, which is the formatting for first letter. However, this doesn’t work with the above. I need to change the axis to use an actual date field (which was one of the mechanisms I’d already tried).

Baseline Date

DATETRUNC(‘month’,MAKEDATE(2019,MONTH([Order Date]), DAY([Order Date])))

This is taking every date in the data set, and ‘baselining’ it to all be on the same year (I chose 2019, but this could be any year you choose). The DATETRUNC is then setting all dates within the same month to be reported as being the 1st of the month.

Adding this to Columns instead as an exact date, and then formatting the axis and changing the Dates option to mmmmm will display the 1st letter

There is also a requirement to just show a label and tick mark for every other month, starting in Feb. You need to edit the axis, and on the Tick Marks tab

  • Set Major Ticked Marks to Fixed
  • Set the Tick Origin to 01 Dec 2018, and the interval to every 2 months (2 months after 1st Dec 2018 is 1st Feb 2019, so the F displays while the J for Jan doesn’t)
  • Set Minor Tick Marks to None

An additional ‘formatting’ setting I used on the bar charts was to fix the vertical axis to a number high enough to ensure the label always displayed after the bar and not in it. What this needs to be set to will vary and is only really noticeable once added to the dashboard, so may require some tweaking to get it correct.

Once again, you’ll need an instance of this bar chart for each of the measures Sales, Profit, Margin & Customers.

Expand / Collapse Function

On the dashboard, you need to use a vertical layout container, and place all the sheets in order underneath each other, so you have KPI, bar, KPI, bar etc.

Each bar chart needs a filter which will determine whether it shows any data or not. Create a calculated field

FILTER : Selected Measure

[Selected Measure]

On the Sales bar chart, make sure the Selected Measure parameter contains the value ‘SALES’, then add the Filter: Selected Measure to the Filter shelf and select the value SALES (it’ll be the only option available)

Then go to the Profit bar chart, and change the Selected Measure parameter to PROFIT. Now add the FILTER : Selected Measure to the Filter shelf and select the value PROFIT. If you switch back to the Sales bar, you’ll find the display is empty. Repeat this for the MARGIN and CUSTOMERS bar charts.

If you return to the dashboard you should find you probably have the 4 KPIs displayed and 1 bar chart showing. The final step is to add parameter actions.

For each KPI sheet, create a parameter action that targets the Selected Measure parameter by passing the relevant <Measure> – String to Pass field.

Ensuring the KPI isn’t highlighted on selection

I’ve used the True = False concept which I’ve applied multiple times to previous challenges.

Create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of teach KPI viz.

Then on the dashboard, add a Filter action for each KPI sheet that goes from the KPI object on the dashboard to the sheet itself, passing the values Source: True = Target: False

Making the display work for mobile

This frustrated me no end. When I click on the Phone layout option (right image below), the layout container just doesn’t behave as it does on the Default view – the space for the bar charts is retained even when there’s no data.

I tried all sorts of combinations of containers to try to resolve this, and just couldn’t get it, and I struggled to find anything online that would help. I published to Tableau Public to test what the result would actually look like on my mobile, in the vain hope it might ‘just work’ but it didn’t 😦

When Sam Epley posted his solution, and I found it worked on mobile, I had to look. and found that you could just ‘delete’ the phone layout.

A couple of gems in this one that I need to store away for future! My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Table Enhancements for #SportsVizSunday and Pro Football Focus

Donna Coles1 Comment

Community month continues for #WOW2020, with this week’s challenge being set by Spencer Baucke.

This week the challenge focuses on 3 techniques for enhancing a tabular display

  • Drill Down / Expand to show the breakdown by Year
  • Custom Header to control sorting
  • Page Control

I managed this challenge, but it wasn’t without help – I did my research using my friend, Google, and found tutorials to help, so this blog is briefer as you’ll need to read the blogs I reference too 🙂

Drill Down / Expand

There was a similar challenge last year which I blogged about here… or rather I referenced Rosario Guana’s excellent blog here.

The slight difference between this and the current challenge is that on expand/drill down, the Season is being shown in a separate column, rather than within the same one as above. The need for ‘data duplication’ which is referenced in Rosario’s blog isn’t required in this case. The key fields I used to resolve this part of the challenge are

pSelected Player ID

An integer parameter, defaulted to 0

pLevel

An integer parameter, defaulted to 2

Max Level

IIF([pLevel]=1,2,1)

If pLevel is 1 then we’ve already ‘drilled down’, so the max level on display is 2, otherwise we’ve yet to drill down, so the max level on display is 1.

DD Level

IIF([Max Level]=2 AND [Player Id]=[pSelected Player ID],2,1)

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then the drill down level is 2, otherwise it’s 1.

Player ID Arrow

IF [pSelected Player ID] = [Player Id] AND [Max Level]= 2 THEN ‘▼’
ELSE ‘►’
END

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the down arrow. I use this site to get the geometric shapes required.

Season Display

IF [pSelected Player ID] = [Player Id] AND [Max Level]=2 THEN STR([Season])
ELSE ”
END

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the Season otherwise it needs to be blank.

Player ID Display

Player ID

This is just a duplicate field.

The fields are then arranged along with the measures as below

Player ID is hidden (it will used for the sort later). DD Level is added to the Detail shelf, so it can be referenced by the parameter actions.

NOTE – I had to ask as it wasn’t obvious to me, but the Carries measure is essentially the number of records or count of the table, so Avg YPC becomes SUM([Yards])/[Carries].

To complete the drill down, when added to the dashboard, the following parameter actions are required

Selected Player

passes the value from the Player ID field into the pSelected Player ID parameter

Set Level

Passes the DD Level field into the pLevel parameter

Custom Header to Control Sorting

I followed Tessellation’s blog post, Ultimate Guide to Tables : Headers pretty much verbatim to build the header sheet. Here’s just a few pointers below on how my solution matches with the blog.

For the Measure Rows field referenced, I used the Position field as a field in the data set that contained at least 3 values :

IF [Position] = ‘FB’ THEN -1
ELSEIF [Position] = ‘H’ THEN 0
ELSEIF [Position] = ‘P’ THEN 1
END

I created parameters

pHeader MeasureNames (which is the equivalent of MN Parameter referenced in the blog).

and pHeader Position which is the equivalent of the Category Parameter mentioned

The parameter actions were then set as

Header – Set Sort Measure

passes Measure Names to the pHeader MeasureNames parameter

and

Header – Set Sort Direction

passes the Position field into the pHeader Position parameter

When defining the other variables referenced in the blog, I had an issue with the Measure1 TF field; my version looks like

IF [pHeader MeasureNames ] = ‘Measure1’
AND [pHeader Position] = [Position]
THEN TRUE
ELSE FALSE
END

For the sorting, I created

Sort Measure Value

CASE [pHeader MeasureNames ]
WHEN ‘Measure1’ THEN [Carries]
WHEN ‘Measure2’ THEN SUM([Yards])
WHEN ‘Measure3’ THEN [Avg YPC]
WHEN ‘Measure4’ THEN SUM([TDs])
END

and then Sort

IF [pHeader Position] = ‘P’
//up arrow selected, sort ascending
THEN -1 * [Sort Measure Value]
ELSE [Sort Measure Value]
END

Note – I chose my up and down arrows to behave different from how they are described in the blog. To me clicking ‘up’ suggests a sort ‘upwards’ ie ascending from small to large.

The Sort field is then used to apply the sort to the Player ID field on the table

Page Control

Again I followed a blog by Tessellation – Ultimate Guide to Tables in Tableau : Pagination

The exception, was at Step 5 Build the page navigator, I simply created a new sheet called ‘Curr Page’. This referenced 2 calculated fields

Current Page No

[pPage No]

where pPage No is the equivalent of the page number parameter referenced in the blog.

Total Pages

FLOOR({COUNTD([Player Id])}/[pRows Per Page])+1

where pRows Per Page is the equivalent of the rows to show parameter referenced in the blog.

The only other slight addition required to this challenge, was to reset the page control to page 1 if the sorting was changed. This was done by creating a new calculated field

First Page No

1

adding this field to the Detail shelf on the header/sort controls sheet, then adding a parameter action

Reset to Page 1

which passes the First Page No to the pPage No parameter

Fingers crossed, I think I’ve covered the main points for this challenge and highlighted where the blogs might differ. My published viz is here.

This has given some great ideas of how to ‘pimp’ a table. I highly recommend you check out some of Tessellation’s other blog posts on tables :

Happy Vizzin’! Stay Safe!

Donna

Can you build a mobile calendar picker?

Donna ColesLeave a comment

It was Natalia Miteva‘s turn to post the #WOW2020 challenge this week. It’s a parameter actions based challenge, inspired by a date range picker viz originally posted by Ludovic Tavernier.

Once again, I found this week tough! I understand the concept of parameter actions, and can use them for the ‘basics’, but the funky stuff people come up with to bend them to their will, is really beyond me.

I started the challenge with good intentions as always, ‘relating’ the Dates data to the Superstore data set matching the Date field to the Order Date field, built out the calendar view, but got stuck trying to figure out how to set the parameter action into an ‘End Date’ parameter, when I was already setting a ‘Start Date’ parameter. After staring at the screen for some time, I figured I’d try to work it all out from Ludovic’s viz instead.

There’s A LOT going on in this challenge, so I’ll do my best to explain.

  • Building the Calendar Picker
  • Building the Next / Previous control
  • Building the Year/Month control
  • Building the KPI & Trend Chart

Building the Calendar Picker

The calendar will only show 1 month, so let’s deal with that to start with.

A parameter will be used to store a date related to the month to show.

pMonthSelected

This is a date parameter that I chose to default to 01 June 2019.

Month Date

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

This is truncating every Date in the dataset to the 1st of the relevant month

Month To Show

[pMonthSelected] = [Month Date]

Add this to the Filter shelf and set to True will limit the data to a single month, June 2019 by default.

To display the days of the month in the ‘grid’ layout, we need to know the day of the week each day falls on (so we know what column to position the day in), and we need to know what week in the year the day falls in, as this will dictate which row to position the day in.

Day of Week (Abbrev)

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

The basic calendar layout can then be built by

  • Month To Show = True on Filter
  • Day of Week (Abbrev) on Columns
  • WEEK(Date) on Rows
  • DAY(Date) on Text

The WEEK(Date) field is then hidden.

We’re going to need to represent each day by a ‘square’ block. I achieve this by adding MIN(1) to Columns, changing the mark type to Bar, fixing the axis from 0-1, then hiding the axis.

So now the basic calendar layout is achieved, we need to figure out how to select a start & end, and colour accordingly.

We’re going to use a single string parameter to store both the selected start & end dates, using a | to act as a delimiter allowing us to identify & so separate the 2 dates.

pSelectedDates

String parameter set to empty string by default

Show the parameter on the viz, and I will attempt to walk through the steps, showing how the values in the parameter influence other fields and what is displayed on the viz.

We need another field, that is going to be used in conjunction with the parameter. I’m going to build up what this field needs to store as we go through, so hoping it will all make sense.

Date Control

IF [pSelectedDates]=”” THEN STR([Date]) END

If the parameter is empty, then set Date Control to be the date.

Let’s add this to the Text so you can see what happens to this field.

So the first action we expect the user to take is to click on a single date displayed. This value stored in the Date Control field will be passed via the parameter action into the pSelectedDates field (I’ll show you how this works later). For now let’s assume the user has clicked on the 5th June, so type 2019-06-05 into the pSelectedDates field.

This is now the start date, and we need the Date Control field to change to reflect this

Date Control

IF [pSelectedDates]=”” THEN STR([Date])
ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date])
ELSE [pSelectedDates]+”|”+STR([Date]) END // else store start & end date

If the Date displayed is before that stored in the parameter, then Date Control will continue just to store the date, otherwise it will append | followed by the date onto the existing parameter value.

So when a date is clicked again, the value of the Date Control will once again be passed to the pSelectedDates parameter.

If the date clicked is earlier than the one already stored, eg let’s say 04 June was clicked, the Date Control value of 04 June is passed to the parameter, and is now the ‘start date’. Type 2019-06-04 into the pSelectedDates field.

But if a later date is selected, say 18th June, the Date Control value of 2019-06-04|2019-06-18 is passed into the pSelectedDates parameter, and we now need another step with our Date Control field :

Date Control

IF [pSelectedDates]=”” THEN STR([Date])
ELSEIF CONTAINS([pSelectedDates],”|”) THEN STR([Date])
ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date])
ELSE [pSelectedDates]+”|”+STR([Date]) END

The 2nd statement is now looking for the | character and if it finds it, we know a start & end dates has been selected, so we need to ‘reset’ this control field to how it was originally – just it’s own date.

Type in 2019-06-04|2019-06-18 into the pSelectedDates field to see this.

Ok, so now we can see how the pSelectedDates field is working with the Date Control field, we now need to define some additional fields to help us identify the start & end of the dates selected, so we can work out what to colour.

Date Selection Start

DATE(LEFT([pSelectedDates],FIND([pSelectedDates],”|”)-1))

This is looking for the position of the | field and taking everything from the first character in the pSelectedDates string up to the character before the |.

Note I originally used DATE(SPLIT([pSelectedDates], “|”,1)), but after extracting the data set, this calculation caused errors. I don’t know why, but the above workaround sufficed.

Date Selected End

DATE(MID([pSelectedDates], FIND([pSelectedDates],”|”)+1,10))

Find the position of the | and take all the characters starting at the position after the | and ending at the character 10 characters later (the date format is 10 characters so this can be easily hardcoded)

COLOUR: Date

IF [Date]= [Date Selection Start] OR [Date] = [Date Selection End] THEN ‘Hot Pink’
ELSEIF [Date] > [Date Selection Start] AND [Date] < [Date Selection End] THEN ‘Pink’
ELSE ‘White’
END

Add this onto the Colour shelf, and adjust accordingly

We can remove the Date Control from Text now, but it needs to remain in the view, so it can be referenced when we build the parameter action. Add it onto Detail shelf instead.

Finally add this viz onto a dashboard, then add the parameter action, setting the Target Parameter to pSelectedDates and the Field to pass as Date Control.

As you play around selecting values on the dashboard, you’ll notice the dates appear ‘greyed out’, as the ‘on click’ action is essentially highlighting what you’ve selected. To stop this from happening create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of the calendar viz.

Then on the dashboard, add a Filter action that goes from the dashboard to the sheet itself, passing the values Source: True = Target: False

Building the Next / Previous Control

We need to store the value of the next month related to the Date unless we’re already in the last month of the data set, in which case we’ll just keep the same month (ie if the month is 01 Nov 2019, next month will be 01 Dec 2019, but if the month is 01 Dec 2019 which is the last month in the data set, the next month will also be 01 Dec 2019. This stops the next control from moving forward when we reach the end.

Next Month

IF [Month Date] = {MAX([Month Date])} THEN [Month Date] ELSE
DATE(DATEADD(‘month’, 1, [Month Date]))
END

Change the mark type to Shape and select an appropriate arrow. If need be reduce the transparency of the shape colour (I chose 50% which changed a black arrow to look like a grey arrow).

On a new sheet, Add Month To Show = True to the Filter shelf, and Next Month to the Detail shelf.

If you recall, right at the start, we created a pMonthSelected parameter, and this will be used to change the month displayed.

Add the viz to the dashboard, and add a parameter action that on select, targets the pMonthSelected parameter, passing in the Next Month field

For the Previous Month control, we need apply similar steps, but using the field below instead.

Prev Month

IF [Month Date] = {MIN([Month Date])} THEN [Month Date] ELSE
DATE(DATEADD(‘month’, -1, [Month Date]))
End

Building the Year/Month control

When building the Year selector, we need to filter the data to just the month name already selected/in view, which is based on the pMonthSelected parameter. Ie if the month selected is 01 June 2019, we want the data related to June 2016, June 2017, June 2018, June 2019

Filter Month

DATENAME(‘month’,[pMonthSelected])=DATENAME(‘month’,[Date])

Add this to the Filter shelf, set to true, then build out the view as below, fixing the axes between 0-1 again, and then hiding. You can see the values of the Month(Date)) displayed, and it’s this field that will be passed via a parameter action. It can be moved to Detail.

COLOUR:Selected Year

YEAR([pMonthSelected])=YEAR([Date])

Add this to the Colour shelf and adjust accordingly.

When added to the dashboard, the associated parameter action required is :

When building the month selector, we need a similar filter to ensure the months shown are all associated to a single year

Filter Year

YEAR([pMonthSelected]) = YEAR([Date])

Add this to the Filter shelf set to True.

The layout of the Months, requires some additional fields to determine which row and column the month should be positioned in.

Month Cols

IF (DATEPART(‘month’,[Date]) %4) =0 THEN 4
ELSE (DATEPART(‘month’,[Date]) %4)
END

DATEPART returns the integer value of the month, ie a number between 1-12, so this calculation is using the modulo (%) function to position based on the remainder when dividing the month number by 4.

Month Row

IF DATEPART(‘month’,[Date]) <=4 THEN 1
ELSEIF DATEPART(‘month’,[Date]) <= 8 THEN 2
ELSE 3 END

Adding these to the view as follows gives us a grid

Note the blue Month(Date) on the Text shelf is showing just the month name ie ‘January’, and has been formatted to it’s abbreviation. The green Month(Date) on the Detail shelf is storing the full month year ie January 2019.

COLOUR:Selected Month

[pMonthSelected]=[Month Date]

Add to the colour shelf and adjust accordingly.

You can then hide the Month Col & Month Row fields from displaying.

When added to the dashboard, the associated parameter action required is

Note You will need to apply the same ‘true=false’ trick described earlier to both these views using filter actions to prevent them from highlighting on selection.

Hiding the year/month selector

On the dashboard add a vertical container, then place the Year Selector sheet and the Month Selector inside.

Remove the chart titles, set the background of the container to white, then set the container to be floating and select the container option to Add Show/Hide Button.

You’ll need to spend some time adjusting the size and the position of the floating container once you have the layout of all the other objects finalised.

Building the KPI & Trend Chart

I’m going to try to be brief with this section. The above is A LOT to take in and write!

Selected Period

This is simply using the Date Selection Start & Date Selection End fields to display in a text field

KPI BANs

We need to be able to filter the data being displayed based on the selection

Dates to Show

[Date]>= [Date Selection Start] AND [Date]<= [Date Selection End]

This is added to the Filter shelf and set to True.

After that we’re just building a simple Text based viz utilising Measure Names & Measure Values as shown below

And finally the trend chart, again needs to be filtered by Dates To Show = True, and is a simple line chart plotting Date against the measures required as below

When added to the dashboard, these 2 sheets should be placed side by side in a horizontal container, both set to fit entire view. The right padding should be set to 0 for the KPI chart and the left padding set to 0 for the trend chart. This will have the effect of the horizontal lines for each chart joining together to make it look like a single chart.

Phew! I’m beat! My published viz is here. Keeping fingers crossed for a challenge that is easier to blog about next week 🙂

Happy vizzin’! Stay Safe!

Donna

Can you build a dynamic date drilling chart?

Donna ColesLeave a comment

Week 19 of #WOW2020 saw Ann provide this challenge, which she declared would be easier than Luke’s from the previous week.

The aim was to start with a chart by week, but on selection drill in to show a by day view, with the slight twist that all days between the first and last selected dates should display, even if the specific week in between wasn’t explicitly selected.

I knew this was going to involve set actions (although there was a clue on the ‘latest challenges‘ page – I tend to pick up the challenges through Twitter, so often bypass this page, but it’s worth a look for extra clues :-))

So on the face of it, the challenge seemed as if it should be ok, but it’s usually only when I start building that things can start to unravel.

One area that Ann hadn’t been explicit about in the requirements, was the behaviour in certain scenarios. I had to revise some of my initial attempts/calculations in order to match Ann’s workbook. This week I’m not going to go into all the wrong turns, but this is the behaviour I observed in Ann’s workbook, and so ultimately tried to replicate.

  1. When at the week level, if multiple points are selected, the days then displayed should start from the first day of the first week selected (which will be a Sunday as we’re working with how the US standardises a week start), and finish on the last day of the last week selected (which will be a Saturday).
  2. When at the week level, if a single point is selected then the 7 days in that week only should be displayed.
  3. When at the day level, if multiple points are selected, the days then displayed start from the first day of the week the first day is in and end on the last day of the week the last selected day is in (so you may get more days than actually selected). eg if the 1st day selected is a Tuesday and the last day selected is a Friday, the ‘drill in’, will start on the previous Sunday, and end on the following Saturday. This isn’t necessarily what you may expect to happen.
  4. Based on the above, when at the day level, a single day is selected, the ‘drill in’ will show the 7 days in the same week. Once you’re down to displaying the 7 days in the same week, clicking on any single day or selecting a couple of days, won’t appear to do anything as the same results are displayed.

Being aware of the above, will explain why some the calculations I end up with look the way they do.

Ok, let’s get on with the build…..

Ann stated that she’d hardcoded to the ‘last 52 weeks’, but essentially it looked like she was displaying data for all of 2019. This wasn’t really something the challenge was testing, so I simply started by adding a data source filter for the year 2019 (right click on data source -> Edit data source filters). I also had to set the date properties of my data source to set a week to start on a Sunday as I’m UK based, so my week’s are defaulted to start on Mondays (again right click on data source -> Date Properties). As a result of my year filter, I didn’t seem to have the same starting/ending dates as Ann, but as already stated, this wasn’t the main aim of the challenge, so I didn’t stress about it.

I decided quite early on that I was going to also use a Parameter Action to decide the level I was at (ie had I drilled in or not). I wasn’t entirely sure when I started how many ‘levels’ I might need, so chose to use an integer parameter for this. I ended up only needing 2 levels, so a boolean could have worked equally as well, or, as I type, I think I could have used this parameter to store the ‘date level’ (day or week) I want to display my dates at, which would have made some of my calculations easier to read. I’m not going to do this though.

Drill Down

Integer parameter set to 0 by default.

Now I want to define a date field that I’m going to use on the axis, that will vary depending on the ‘level’ we’re at.

Date to Plot

IF [Drill Down]>0 THEN DATETRUNC(‘day’, [Order Date]) ELSE
DATETRUNC(‘week’, [Order Date])
END

Note – I used >0 as I wasn’t sure if I’d have levels 0, 1 & 2… as it turns out I just used 0 & 1 in the end.

Add Date to Plot to Columns as a Continuous, Exact Date and Sales to Rows and we’ve got our starting point

Change the Drill Down parameter to 1 and the chart will change to display at the day level

Drilling Down on selection

On selection, we want to add the selected dates into a set, so first up, we need to define that set.

Right click on Date to Plot and Create -> Set

Selected Dates

Select some random dates so we can test with. These will get set properly later based on the Set Action we define on the dashboard.

Based on the dates in the set, we need to determine a min and a max date we can then use to restrict the dates being plotted on the chart.

Min Date

{FIXED:MIN(IF [Selected Dates] OR [Drill Down]=0 THEN DATETRUNC(‘week’,[Date to Plot]) END) }

This either gets the 1st day of the week based on the earliest date in the set, or the 1st day of the week of the whole data set (when we’re at the ‘starting’ level with Drill Down =0).

We also need

Max Date

IF [Drill Down]=0 THEN
{FIXED:MAX(DATEADD(‘day’,-1,[Date to Plot]))}
ELSE
DATEADD(‘week’,1,{FIXED:MAX(IF [Selected Dates] THEN DATETRUNC(‘week’,[Date to Plot]) END)})-1
END

If we’re at the starting level (Drill Down = 0) then we want the last day within the latest week in the data set (this field is used in the title display, so necessary to get this to display right), otherwise we need to get the last day of the week associated to the maximum date in the set.

Now we need to be able to restrict the dates displayed in the chart based on these

Dates to Include

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

Add this to the Filter shelf and set to True.

Your display shouldn’t change, as we’re still at ‘level 0’, even though our set has random dates selected. If you now manually change Drill Down to 1, you should see a change

Setting up the dashboard actions

Ok, now we’ve got the basic idea, let’s get it all working properly with dashboard actions before we sort out all the other bits n bobs.

First up, let’s manually reset everything by setting the Drill Down parameter to 0 and emptying all the values selected in the set.

Add the sheet onto the dashboard, and create the set action, which is set to target the Selected Dates set and to Keep set values if you click on a blank area of the chart after selection.

However, this on it’s own won’t change the display. We need to set the Drill Down parameter to 1 too.

For this we need another field

Set Drill Down Level

1

Add this to the Detail shelf of the chart.

Then back on the dashboard, add a Parameter Action that targets the Drill Down parameter using the value stored in the Set Drill Down Level field

Now if you select the dates in the dashboard, you should get the desired behaviour, and if you select again, you should filter the days selected further (as per the behaviour described at the top of the page).

Resetting the display

The Reset button is actually another sheet.

I created a ‘fake’ bar chart by adding MIN(1) to Rows, and double clicking in the space below the Detail and Tooltip shelves on the Marks card, and typing the text ‘CLEAR SELECTION’. This creates a ‘pill’ without having it defined explicitly as a calculated field, and I added this to the Text shelf, and centred/formatted appropriately. I then set the axis to be fixed from 0 to 1 and hid it.

I set the colour to #a26dc2 and set the text to ‘match mark colour.

We only want this sheet to display, if we’ve ‘drilled down’, so I need

Show Reset

[Drill Down]=1

which is added to the Filter shelf and set to True.

I also need a parameter action off this sheet, to reset the Drill Down to 0 on selection. For this I need another field

Reset

0

which is added to the Detail shelf.

Depending on what you’ve already been playing around with, there’s a chance this sheet may already be empty. Get the dashboard into a state where you’ve drilled down to the day level, then add this sheet, and add another Parameter Action.

Reset is set to run off the ‘button’ sheet only, to target the Drill Down parameter by using the value in the Reset field.

You should now be able to test all this out and get the desired behaviour.

Adding Animations

To get the chart to transition between selections, we need to use the Animations functionality.

On the Format menu, select Animations and adjust the settings as you choose.

Play around and you should have the main features of this challenge now working

Dynamic Title

The title needs to change based on whether you’re at the weekly or daily level. It also needs to show total and average sales. So for all this I need

LABEL: Level

IF [Drill Down]>0 THEN ‘Day’ ELSE ‘Week’ END

LABEL: Subtitle Level

IF [Drill Down]>0 THEN ‘Daily’ ELSE ‘Weekly’ END

LABEL: Instruction

IF [Drill Down] = 0 THEN ‘SELECT WEEKS TO DRILL DOWN TO DAILY VIEW’
ELSE ‘CLEAR SELECTION USING BUTTON’
END

Total Sales

WINDOW_SUM(SUM([Sales]))

Avg Sales

WINDOW_AVG(SUM([Sales]))

All these fields are added to the Detail shelf of the main chart along with Min Date and Max Date, and then the title is edited and formatted accordingly to reference these.

Tooltips

In a similar manner, the tooltips also need adjusting, they just need to refer to the LABEL: Level field

Colour of the Line

Although not explicitly mentioned, the line colour seemed to change from a lighter shade at the weekly level to more intense at the daily level. To achieve this I created

COLOUR

[Drill Down]

and added to the Colour shelf. This gives a colour legend displaying either 1 or 0 depending at what state you are in the interaction. Set colour values accordingly.

Average Line

Add this to the chart simply by selecting Average Line from the Analytics pane and dragging then dropping onto the Table shelf that appears. Format appropriately.

And apart from other basic formatting to remove gridlines/ axis titles etc, that should be it.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Which products are most profitable?

Donna Coles2 Comments

I’m starting to write this blog with a bit of uncertainty today as I’m not ultimately sure where I’m going to end up….

I wasn’t even sure I was going to pen an entry this week…. I found Luke’s challenge tough, and the path I took full of multiple wrong turns, that meant trying to write out a comprehensible ‘how I did it’ quite tricky, as reviewing now I’m questioning ‘what did I do that for…’.

Whilst with perseverance and a bit of inspiration from Rob Saunders, I did manage to post a working solution, I knew I wasn’t overly happy with it, particularly because my expand and collapse functions didn’t behave as I saw others do… I had to click twice to collapse.

Before starting to write, I decided to check out Luke’s workbook which he’d finally published to see if I could understand where I was going wrong.

I’d used both a set action and parameter action in my attempt; Luke had just a parameter action. In trying to understand why I ended up with a set action and see if I could do without it, my solution gradually started unravelling, as various calculated fields needed changing.

So, I think the best way to approach this blog is to rebuild my solution from scratch, using only my existing workbook as a reference, and I will attempt to write and screen shot as I build. This could take some time, and I have no idea how successful I’ll be…. I may well get to a point where I’ve taken a wrong turn again, and everything I’ve written needs to be scrapped… at which point I may just have to say ‘sorry, I tried!’……

Ok, let’s get cracking.

Top N Sub-Categories

At the ‘first’ level of the table, we need to display the top n sub-categories ordered by Profit. Those not in the top n should be displayed under an ‘All Others’ grouping, and always displayed at the bottom. The top n can vary based on the user input.

We need a parameter to define the value of the top n.

SUB-CATEGORIES TO SHOW

This is an integer parameter ranging from 1-10, defaulted to 3. I’ve titled it exactly as displayed on the output, so it’s one less change to make later (hence the capitals).

The easiest way to group the sub-categories into those in the top n, is to use a set. Right click Sub-Category and Create -> Set.

Top N SubCats by Profit

Use the Top tab to define the rules for which Sub-Category to include, referencing the SUB-CATEGORIES TO SHOW parameter and the Profit field as shown below.

SubCat Group

IF [Top N SubCats by Profit] THEN [Sub-Category] ELSE ‘All Others’ END

If the Sub-Category is in the set then the name will display, otherwise it will be grouped under the ‘All Others’.

Add SubCat Group to Rows and change the sort on the pill to sort by Field = Profit desc

This will result in All Others being listed at the top, but to resolve that, drag the Top N SubCats by Profit set onto the Rows an place in front of the SubCat Group.

Let’s get some of the measures we need out onto the canvas.

Margin

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

format to percentage with 0dp.

Add Margin, Profit & Sales onto the sheet.

Top N Products

The next level in the table displays the Top N products per SubCat Group based on their margin. For those not in the Top N, the products should be grouped under ‘All Others’, and listed at the bottom. The Top N is once again defined by a user input via a parameter.

PRODUCTS TO SHOW

Once again an integer parameter ranging from 1-10 but defaulted at 5 this time.

Unfortunately, this time we can’t use a set to define our Top N grouping. This is because the set will only consider the Top N across all Products and will not consider the fact the Products are nested per Sub-Category grouping. So we need to come up with an alternative. I did do a bit of research to find ideas, and found this article by Emma Whyte at The Information Lab to get me started : Showing a Nested Top N with Other in Tableau.

The Top N needs to be based on the order of the Margin, or the rank.

Margin Rank

RANK_UNIQUE([Margin])

Change this to be a Discrete field.

This will give us a unique ‘number’ per row displayed based on the value of Margin. If records have the same Margin value, using RANK_UNIQUE will mean they get a different rank number (as opposed to how other ranking functions work). The table calculation of INDEX() could work just as well. NOTE – there is potential though that when the Margin values are the same for different products, what makes the Top N and what doesn’t may differ, so you might find you get a slightly different list from some of the solutions you see.

Add Margin Rank and Product Name onto the sheet, and edit the table calculation on Margin Rank, to compute by Product Name only

We now want to group the Product Name based on the Top N products.

Product Name Group

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW])
THEN ATTR([Product Name])
ELSE ‘All Others’ END

We also only want to show (PRODUCTS TO SHOW + 1) rows per SubCat Group ie if PRODUCTS TO SHOW = 5, we want to display 6 rows per SubCat Group, where the 6th row displays ‘All Others’. The 6th row also needs to show the Profit, Sales and Margin values associated to all the Products in the ‘All Others’ Product Name Group.

So we need to calculate some new fields that will store a revised value for Proft, Sales and Margin, depending what row we’re working with.

Sales For Others

WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Sales]) END)

If the Product Name isn’t in the Top N, then get it’s Sales value, and then sum all of those rows that meet the same condition.

If you put this onto the sheet, and set the table calculation for each of the nested calculations (Sales for Others & Margin Rank) to be by Product Name only, you’ll see that the value displayed in every row for each SubCat Group is the sum of the values associated to the rows in the ‘All Others’ group.

So now we need a field that’s either going to display the Sales for the 1 product or the sales for the group of products, depending on what row we’re on.

Grouped Sales

IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Sales]) ELSE [Sales For Others] END

Again the table calculation settings need to be set to compute by Product Name only. This is the field we ultimately want to display, so it needs to be formatted accordingly. The Sales and Sales For Others fields can be removed.

Along similar lines, we need

Profit For Others

WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Profit]) END)

Grouped Profit

IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Profit]) ELSE [Profit For Others] END

Margin For Others

IF [Margin Rank] > [PRODUCTS TO SHOW] THEN [Profit For Others]/[Sales For Others] END

Grouped Margin

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW]) THEN [Margin] ELSE [Margin For Others] END

When added to the sheet, once again make sure all the table calculation properties for all the nested calculations are set to compute by Product Name only.

As mentioned above, we only want to show PRODUCTS TO SHOW + 1 rows, so let’s create a field we can filter by :

Show?

[Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1

Add this to the Filter shelf and set to True. Once again the table calculation needs to be set to compute by Product Name only.

Add Totals

We want a grand total and subtotals only at the level of SubCat Group. Add the totals by Analysis -> Totals -> Show Column Grand Totals to get the overall total. Then on the SubCat Group pill, click and add SubTotals

We’ve now got the main components of the table. We now need to get the interactivity working to allow the expand / contract on arrow selection.

Expand / Contract All Others Product Group

First up, we’ll just tidy up our table display

  • Hide the In/Out Top N SubCats By Profit field
  • Hide the Margin Rank field
  • Hide the Product Name field.
  • Remove the Margin field
  • Alias the Grouped Margin, Grouped Profit, Grouped Sales fields
    • Right click and Edit Alias. Name the field ‘ Margin ‘ (note the trailing and leading spaces. You can’t alias just as Margin as a field already exists with that name, but the spaces make it think it’s a new name.

We need an additional field that will store our ‘arrow’ icon. We’re going to revisit this field. For starters

Product Group Header

IF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ”
ELSE ‘►’
END

I use this site to get my icon characters from. Add this field to the sheet after the SubCate Group pill, once again setting the table calculation to compute by Product Name.

In the dashboard, the aim is to click on an arrow associated to a single SubCat Group, which will expand the Product Group Name field to display the actual Product Name (rather than ‘All Others’) with their associated Margin, Sales & Profit values, and also show a ▼ icon.

This will be achieved using Parameter Actions, for which we need a parameter :

Selected Sub Category Group

String parameter defaulted to ”

Display this parameter on the sheet, as we can start to test the interactivity ‘manually’ without the need for the dashboard. What the dashboard action will do is on ‘click’, it will be set to populate the value of this parameter with the associated SubCat Group value. We can then do some checks based off of this and set various fields accordingly. It means we need to revisit some of the fields.

First up let’s set the arrow….

Edit Product Group Header to be

IF [Selected SubCat Group] = MIN([SubCat Group]) THEN
IF [Margin Rank] > [PRODUCTS TO SHOW] THEN ‘▼’ ELSE ” END
ELSEIF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ”
ELSE ‘►’
END

Test this by entering the value of ‘Copiers’ into the Selected SubCat Group parameter. The arrow against ‘All Others’ should change.

We also need to change the value of the Product Name Group to show the actual Product Name on selection, so

edit the Product Name Group

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW])
THEN ATTR([Product Name])
ELSE
IF [Selected SubCat Group] = MIN([SubCat Group])
THEN ATTR([Product Name])
ELSE ‘All Others’ END
END

Again test this out by changing the value in the parameter.

But we need to make more rows show too, so

edit Show?

([Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1) OR (MIN([SubCat Group]) =[Selected SubCat Group])

The values of our measures are still the totals though, so we need to edit these fields to

Grouped Sales

IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Sales]) ELSE [Sales For Others] END

Grouped Profit

IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Profit]) ELSE [Profit For Others] END

Grouped Margin

IF ([Margin Rank] <= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group]=MIN([SubCat Group]))) THEN [Margin] ELSE [Margin For Others] END

However while this works if you play with setting and clearing the parameter on the sheet, it won’t quite fully work if added as a dashboard action, as while the action can set the parameter we can’t ‘clear it’.

We need to ‘tie’ the parameter action to another field

SubCat Group for Reset

IF [Selected SubCat Group] = MIN([SubCat Group]) THEN ”
ELSE MIN([SubCat Group])
END

Add this to the Detail shelf (if you want to see how it changes based on the parameter value, add it to the Rows and test changing the parameter).

It needs to be on the sheet so it can be referenced from the dashboard action.

Adding the Action

Add the sheet to a dashboard, then add Parameter Action as below where the Target Parameter is Selected SubCat Group and the field it references is SubCat Group for Reset

And after all that, you should have a working solution. Phew!

Pretty pleased I got there without taking a detour 🙂 The table just now needs various formatting applied, which I’m going to leave to you to do 🙂 Just tweet me if you’re having problems!

The parameter action may be confusing you a bit – it took a while to really get my head round it, so I’ve tried to explain this a bit more below…

How the parameter action works

On initial load of the sheet, the Selected SubCat Group parameter is blank. So for the SubCat Group = Copiers, the SubCat Group for Reset will also be Copiers as SubCat Group is not the same as Selected SubCat Group parameter. Other fields are also set based on the fact these two fields aren’t the same (like the arrow pointing to the right etc).

When the right arrow is clicked on the dashboard, the value of SubCat Group for Reset is used to populate the Selected SubCat Group parameter. So in this example, Selected SubCat Group will now contain the value Copiers. As the Selected SubCat Group parameter is now the same as the SubCat Group, various fields change their behaviour (like the arrow now points down, and more rows are displayed). But also, the value of the SubCat Group for Reset is also changed; as the SubCat Group is the same as the Selected Sub Cat Group parameter, SubCat Group for Reset now contains a blank string.

So at the point the down arrow is now clicked again on the dashboard, the value of the SubCat Group for Reset is again used to populate the Selected SubCat Group parameter. As SubCat Group for Reset is blank, then the parameter will now be populated with a blank value, and so all the login in the fields will be based on the fact that SubCat Group is not the same as the Selected SubCat Group parameter, and the table will display just as it did on first load.

Hope that helps to demystify what’s going on… it’s certainly helped me!

Thanks for sticking with me if you got this far 🙂

The version of the challenge I built while writing this (my 3rd published version) is here.

Happy vizzin’! Stay Safe!

Donna

What happens if? Can you update sales forecast and targets using only parameters?

Donna Coles6 Comments

So after Ann’s gentle workout for week 6, newly crowed Tableau Zen Master Lorna, hit us with this challenge, and I confess, I struggled. The thought of then having to write this blog about it even brought a little tear to my eye 😦

But here I am, and I will do my best, but I can’t promise I understood everything that went on in this. I truly am amazed at times how some people manage to be so creative and bend Tableau to their will. It really is like #TableauBlackMagic at times!

So I read the challenge through multiple times, played around with Lorna’s published viz, stared at the screen blankly for some time…. I found the University Planning Dashboard viz by Ryan Lowers that Lorna had referenced in the challenge as her inspiration (she’d linked to it from her published viz). I played around with that a bit, although that took a while for me to get my head round too.

I also did a google search and came across Jonathan Drummey‘s blog post : Parameter Actions: Using a parameter as a data source. This provided a workbook and some step by step instructions, so I used this as my starting point. I downloaded the workbook, copied across the fields he suggested and tried to apply his instructions to Lorna’s challenge. But after a couple of hours, it felt as if I was making little progress. I couldn’t figure out whether I needed 2 or 4 parameters to store the ‘list’ data source variables (one each to store the list of selected categories for forecast, the list of selected categories for target, the list of selected forecast values, and the list of selected target values, or one each to store the list of selected categories and forecast values combined, and selected categories and target values combined). Suffice to say I tried all combos, using a dashboard to show me what was being populated on click into all the various fields/parameters I’d built. But it just wasn’t giving me exactly what I needed.

I downloaded the University Planning Dashboard and tried to understand what that was doing. And finally I shrugged my shoulders, and admitted defeat and cracked open Lorna’s solution. When I finally get to this point in a challenge, I try just to ‘have a peak’, and not simply follow verbatim what’s in the solution. I gleaned that I did need only 2 parameters, and that what I had been doing with my attempts with Jonathan’s example was pretty close. It made me feel a bit better with myself.

How things then transpired after that I can’t really recall – it was still a lot of trial and error but I finally got something that gave me the Sales Forecast data and associated select & reset functionality (by this time I’d probably spent 4 hours or so on this over a couple of evenings). Once I’d cracked that, the target was relatively straight forward, so by the time I’d finished on the 2nd day, I had a dashboard that allowed the selections/resets and simply presented the data in a table on screen. I chose to keep that version as part of my published solution, just for future reference (see here). I then finished off the next day, building the main viz.

What follows now, is just an account of the fields etc I used to build my solution. So let’s get going….

Building the Sales Forecast Selector

I’m going to start by focusing on building the left hand side of the viz, setting and resetting the Sales Forecast values for each Category.

We need 2 main parameters to start with:

Forecast Param

An integer parameter defaulted to 70,000. This is the parameter that stores the value of the forecast to set.

Forecast List

A string parameter defaulted to empty. This is the parameter which will ‘build up’ on selection of a category, to store a delimited list of category + forecast values – ie the data source parameter.

Oh, and I also used a 3rd parameter, Delimiter, which is just a string parameter storing a :

The delimiter needs to be a distinct character that mustn’t exist in the fields being used. The Category field nor the Forecast Param field will contain a ‘:’, so that’s fine. But any other unused character would work just as well. Having this field as a parameter isn’t ultimately necessary, but it makes it easy to change the delimiter to use, if the chosen value doesn’t end up being suitable. It was also a field used in Jonathan Drummey’s solution I’d based my initial attempts on.

Now we need to build the viz to work as the category selector.

I simply put Category on the Rows shelf, sorting the pill by SUM(Sales) descending and set the Mark Type to circle. Oh – and I set a Data Source Filter to set the Order Date just to the year 2019.

I also needed the following

  • something to colour the circles based on whether the Category was selected or not
  • something to use to help ‘build up’ the List parameter ‘data source’
  • something to return the forecast value that had been selected against the specific Category

Category Exists in Forecast List

CONTAINS([Forecast List], [Category])

If the Category exists within the Forecast List string of text, this field will return true, and indicates the Category has been ‘selected’. This field is added to the Colour shelf, and the colour needs to be adjusted once parameter action has been applied to distinguish between true & false.

Add to Forecast List

if [Forecast Param]<>0 THEN
[Forecast List] +
[Category] + ‘_’ + STR([Forecast Param]) + [Delimiter]
ELSE ”
END

If the entered Forecast value isn’t 0, then append <Category>_<Forecast Value>: to the Forecast List parameter. Eg if the Sales Forecast value is $50,000 and Technology is selected, then Technology_50000: is added to the existing Forecast List parameter, which has started as blank.

If the Sales Forecast value is then changed to $10,000 say, and Office Supplies is selected, then the Forecast List parameter will become

Technology_50000:Office Supplies_10000:

This Append To Forecast List calculated field is used in conjunction with the Forecast List parameter within a Parameter Action on the dashboard to make all the ‘magic’ happen. The Append To Forecast List field must be in the view to be available to the parameter action, so it is added to the Detail shelf.

When a circle is selected the Append To Forecast List field is used to ‘set’ the Forecast List parameter, subsequently building up a string of Category_Value pairs.

Finally, on hover, the Category and the value of the selected sales forecast at the time must be visible on the Tooltip. To get the value at the point of selection, which isn’t necessarily the latest value visible in the Sales Forecast parameter displayed on screen, the following field is required:

Current FC Value

INT(if contains([Forecast List],[Category]) then
REGEXP_EXTRACT([Forecast List],[Category]+”_(-?\d+)”)
end)

This manages to pull out the number associated with the Category, so in the above example, would return 50000 for Technology and 10000 for Office Supplies.

This field has custom formatting applied : ▲”$”#,##0;▼”$”#,##0 and is added to the Tooltip shelf.

RegEx is a concept I have yet to really crack, so there is no way I’d have come up with the above on my own. I think it’s looking for the named Category followed by Underscore (_) followed by either 1 or no negative sign (-) followed by some numbers, and returns just the numeric part.

Finally, the circles shouldn’t be ‘highlighted’ when selected on the dashboard. To stop this from happening a calculated field of True containing the value True, and a field False containing the value False are required. These are both added to the Detail shelf, and a Filter Action is then required on the dashboard setting True = False. This is a technique that is now becoming a familiar one to use, having been used in earlier #WOW2020 challenges.

So my ‘selection’ sheet looks like

and when added to the dashboard, the parameter action looks like :

with the filter action looking like :

At this point, I’d suggest using a ‘test’ dashboard which contains the selection sheet, displays the Forecast List and Forecast Param, and has the dashboard actions described above, applied to get an idea of what’s going on when a circle is selected, and the values of the Forecast Param changed.

The final part to this set up, is the ‘reset’ button, which when clicked on, empties the Forecast List parameter.

Create a new sheet, change the Mark Type to Text, and on the Text shelf add the string ‘↺’. I simply typed this ‘into’ a pill, but you could create a calculated field to store the ‘image’, which isn’t actually an image, but a special string character, that I got off my favourite ‘go to’ unicode characters website.

You then need a calculated field

Forecast List Reset

”

that just contains an empty string. This is added to the Detail shelf.

Put this sheet on the ‘test’ dashboard, and create another parameter action

This takes the value out of the Forecast List Reset field and sets the Forecast List parameter, subsequently resetting the list to an empty string on click.

Verify this is all working as expected.

Building the Sales Target Selector

Subject to Sales Forecast selector working as expected, then apply exactly the same principles to create the Target selection sheet and associated parameters.

The only slight difference with the fields used in the Target selection is:

Add to Target List

if [Target Param]>0 THEN
[Target List] +
[Category] + ‘_’ + STR([Target Param]) + [Delimiter]
ELSE ”
END

This just applies the addition to the list if the entered target is a +ve number (ie > 0), rather than not 0 as in the forecast selection.

The Target also needs to be displayed on the Tooltip, and this time there is a default target value that should be displayed, even when no selection has been made. For this I created

Target

IF ZN(MAX([Current Target Value])) = 0 THEN
MIN(IF [Category]= ‘Furniture’ THEN 270000
ELSEIF [Category]= ‘Office Supplies’ THEN 260000
ELSEIF [Category]= ‘Technology’ THEN 250000
END)
ELSE MAX([Current Target Value]) END

which was formatted to a currency of 0 decimal places, prefixed by $. This was added to the Tooltip shelf.

At this point, you should now have both the ‘selection sheets’ working on the dashboard, so we can now focus on building the main viz.

Building the Bar Chart

Rather than building the bar chart, I first decided to build a tabular view that simply presented on screen all the bits of data I needed for the bar chart, this being

  • Sales value per Category (simply SUM(Sales))
  • Sales Forecast value per Category (ie Sales + selected Forecast value)
  • Selected Sales Target value per Category (this is the Target field described above)
  • % Difference between Sales & Target
  • % Difference between Sales Forecast & Target

So I created the following additional calculated fields:

Forecast

SUM([Sales]) + MAX([Current FC Value])

formatted to currency prefixed with $ set to 0 dp.

Forecast vs Target Diff

([Forecast]-[Target])/[Target]

custom formatted to ▲0%; ▼0%

Sales vs Target Diff

(SUM([Sales])-[Target])/[Target]

also custom formatted to ▲0%; ▼0%

Adding the table to the ‘test’ dashboard allows you to sense check everything is behaving as expected

Now its just a case of shifting the various pills around to get the desired view. Ensure at least one Sales Forecast Category has been selected, to make it easier to ‘see’ what you’re building.

Lorna stated the target should be displayed as a Gantt mark type, with the sales and the forecast displayed as bars. This means a dual axis chart is required, with sales & forecast on one axis and target on the other.

To get Sales and Forecast onto the same axis, we need to add Category to the Rows (sorted by Sales desc) and Measure Values to the Columns, filtering to only the two measures we need.

Set the Mark Type to bar, and add Measure Names to both the Colour and the Size shelf.

Adjust colours and sizes to suit.

You might have something like


where the measures are ‘stacked’, so the bar is the length of the Sales then the length of the Forecast. We don’t want this, so need to set Stack Marks to Off (Analysis menu -> Stack Marks -> Off).

Add all the necessary fields to the Label shelf and format accordingly (you may need to widen the rows to make the labels show against each row).

Note – in my solution I created some fields to make the opening & closing bracket around the Forecast v Target Diff value only show when a Forecast had been selected, however in writing this blog, I realise it was simpler just to change the formatting of the Forecast v Target Diff to add the brackets around the number. The custom formatting was changed to : (▲0%); (▼0%)

Adjust the Tooltip to suit too.

Now add Target to Columns alongside Measure Values. Set to Dual Axis and Synchronise the axis. Reset the Measure Values mark type back to bar if needed, and set the Target mark type to Gantt.

Remove Measure Names from the Colour and Size shelf of the Target marks card. Untick Show Mark Labels too. Adjust the colour of the mark to suit, and you should pretty much be there now…

Tidy up the final bits of formatting, removing/hiding the various axis, labels, gridlines etc etc.

When this is all put together on the dashboard, you might need to fiddle about a bit with layout containers to get the bar chart lined up with the Selector views.

And with that I’m done! My published version is here, along with the ‘check’ dashboard I used to sense check what was going on, as I’m sure if I ever looked at my solution again, I’d struggle to understand immediately 🙂

Once again, I just want to acknowledge those that manage to create this magic with Tableau. I applaud you!

Happy vizzin’!

Donna

Can you combine relative & custom dates?

Donna Coles2 Comments

Week 4 of #WOW2020 saw one of the new contributors, Sean Miller, provide his first challenge, based on providing a technique to improve the user experience when working with date inputs.

The date inputs allow a user to select from 2 ‘static’ options (Last 14 Days or Last 30 Days), a custom Last N Days option, where the user is then prompted to define how many days….

… along with a Custom Dates option, which when selected, prompts the user to define the start & end dates

Both the N Days parameter and the Start & End dates only appear when the appropriate selection is made. This makes the displayed interface much cleaner (as it’s less cluttered), and, more importantly, makes the action the user is then expected to make, much more obvious.

So let’s get started.

Define the parameters

The first step in this challenge is to define all the parameters needed, these being :

Date Selector

A string parameter just set to contain the value Last 14 Days

This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.

N Days

An integer parameter defaulted to 60

Start Date

A date parameter defaulted to 01 Jan 2019

End Date

Another date parameter defaulted to 31 Dec 2019

Build the Sales over time chart

The Sales over Time trend is simply Order Date as a continuous pill at the Day level, against SUM(Sales). Sales is duplicated as a dual axis, allowing one axis to be set to mark type Area, and the other to Line. The colours of each mark are slightly different, to give the ‘edged’ area look

The requirement stated the ‘Last…’ selections should be anchored to the latest date in the dataset, so we need to determine what this is:

Latest Date

{FIXED:MAX([Order Date])}

To restrict the data displayed, we need something we can filter on:

In Timeframe

CASE [Date Selector]
WHEN ‘Last 14 Days’ THEN [Order Date]>DATEADD(‘day’,-14,[Latest Date])
WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[Latest Date])
WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[N Days],[Latest Date])
ELSE [Order Date]>=[Start Date] AND [Order Date]<=[End Date]
END

In Timeframe can be added to the Filter shelf and set to True.

Based on the defaults selected, at this point, you should be displaying information for the last 14 days.

Expose the parameters onto the display, and test out the rest of the logic by changing the value of the Date Selector (type in one of the other options – eg Last 30 Days, Last N Days, Custom Days).

BANs

The 3 numbers displayed are the measures Sales, Profit and Profit Ratio, where Profit Ratio is a calculated field of

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

formatted to be a percentage of 1 decimal place.

Then

  1. Add Measure Names to Columns
  2. Add Measure Names and Measure Values to the Text shelf
  3. Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
  4. Reorder the columns to match the requirement
  5. Change the formatting of the Measure Names and Measure Values on the Text shelf to set the colour & size of the font to suit, and align middle, centre
  6. Format the display to remove the row lines
  7. Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
  8. Add In Timeframe = True to the Filter shelf to restrict the data.
  9. Format Sales and Profit accordingly.

Measure Selector

I built the measure selection view using the technique I’ve already blogged about related to #WOW2020 Week 1 : Can you sort dimensions with a single click? This is the 3rd week out of 4 so far that I’ve employed this technique, which is great, as it means it’s a concept that I’ve started to remember 🙂 The blog also describes

  1. how to set the parameter action which is needed to alter the Date Selector parameter
  2. how to invoke a filter action using a True and False calculated field to prevent the un-selected measures from ‘fading out’.

By this point, all the sheets should be on the dashboard with everything formatted and positioned where it needs. So now for the final task:

Make the Parameters Hide & Show

This part of the challenge was something totally new to me, so I did what I would usually do, and Googled it.

Hiding Parameters & Filters in Tableau – v2 by Andrew Pick @ The Information Lab

This is the primary post I followed to achieve the result for this challenge, so I’m not going to document it all here, when it’s already written down 🙂 It’s a bit fiddly to get the parameters positioned in exactly the right place. From other posts I found, this technique is also sometimes referred to as ‘parameter popping’.

My published workbook can be found here.

Happy vizzin’ & poppin’

Donna

Can you build a beautiful & dynamic bar chart?

Donna Coles1 Comment

Week 2 of #WOW2020 saw Ann post this challenge asking us to create a dynamic bar chart that changed both the measure being reported and the timeframe being reported over, based on user selections.

The measure needed to be displayed as the bar label, which needed to be formatted differently depending on whether it was Sales ($ 0 dp), Profit Ratio (% 1 dp) or Items Per Order (1 dp). Due to this, the label can’t be a single field, as it’s not yet possible in Tableau Desktop to apply number formatting within a calculated field *sigh*.

A couple of calculated fields are needed for the measures

Profit Ratio

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

and Items Per Order

SUM([Quantity])/COUNTD([Order ID])

The timeframe format displayed also needed to change based on whether the timeframe selected was Last 12 months (MMM’YY eg Apr’19), Last 13 Weeks (Week No) or Last 14 days (mm/dd eg 12/31).

The bar colour needed to reflect the measure selected, and measure selector needed to display a filled or open circle depending on whether the measure was selected or not.

Measure Selector

I realised there were a lot of similarities with the behaviour of the measure selector, and Luke’s challenge from the previous week. I’m not sure if this was intentional or not. As a consequence though, I’m not going to explain how I built the Measure Selector chart, as I utilised exactly the same principles for the Week 1 solution, and I’ve already blogged about it here.

Bar Chart

I built the bar chart on a single sheet, plotting a measure that could change based on selection, against a date field that also changed on selection.

To drive the selection change I needed 2 parameters:

Date Period

A string parameter containing the 3 values available

This parameter was just made available as a drop down for the user to select from on the dashboard.

Selected Measure

This was a string parameter that just stored a piece of text. It was set to the value ‘ Sales ‘ by default, but was changed via a Parameter Action following selection via the Measure Selector chart discussed above.

Note the leading & trailing space in the default value ‘ Sales ‘due to the alias that needs to be set – refer to my previous bog post to understand the relevance

The date field to plot then had to alter based on the Date Period parameter, so the following calculated field was created:

Order Date Truncate

DATE(CASE [Date Period]
WHEN ‘Last 12 Months’ THEN DATETRUNC(‘month’,[Order Date])
WHEN ‘Last 13 Weeks’ THEN DATETRUNC(‘week’, [Order Date])
ELSE DATETRUNC(‘day’, [Order Date])
END
)

This field is essentially storing the date as the 1st of the month, 1st day of the week or specific day level, so when plotted on a chart it can be set to ‘exact date’ and all the orders in the same month/week/day, are all grouped together.

The measure field to plot alters based on the Selected Measure parameter, so the following calculated field is required :

Measure to Show

CASE [Selected Measure]
WHEN ‘ Sales ‘ THEN SUM([Sales])
WHEN ‘ Profit Ratio ‘ THEN [Profit Ratio]
WHEN ‘ Items Per Order ‘ THEN [Items Per Order]
END

Plotting Order Date Truncate on columns as a discrete exact date against Measure to Show on rows with mark type set to Bar, and you can use the parameter to change the number of bars.

But this is showing all the months/weeks/days, so we need to add a filter to restrict to amount of information showing.

Dates to Include

CASE [Date Period]
WHEN ‘Last 12 Months’ THEN [Order Date] >= DATEADD(‘month’,-12,[Today])
WHEN ‘Last 13 Weeks’ THEN [Order Date] >= DATEADD(‘week’,-13,[Today])
ELSE [Order Date] >= DATEADD(‘day’,-14,[Today])
END

This will true or false, and is added to the Filter shelf set to True

Note – [Today] is another parameter that is defaulted to 01 Jan 2020 as per Ann’s requirement.

The field names for the Order Date Truncate isn’t displaying as required though. To fix this, I created

Label:Date

CASE [Date Period]
WHEN ‘Last 12 Months’ THEN LEFT(DATENAME(‘month’, [Order Date Truncate]),3) + ”” + RIGHT(STR(YEAR([Order Date Truncate])),2)
WHEN ‘Last 13 Weeks’ THEN ‘Week ‘ + STR(DATEPART(‘week’, [Order Date Truncate]))
ELSE STR(DATEPART(‘month’,[Order Date Truncate])) + ‘/’ + STR(DATEPART(‘day’, [Order Date Truncate]))
END

which is building the required string to display.

Add this to the Columns shelf next to the Order Date Truncate field, then untick Show Header against the Order Date Truncate field

Next up is the labels to display on top of each bar. This also needs to show something different depending on the measure selected. For this I built 3 calculated fields:

Label: Sales

IF [Selected Measure] = ‘ Sales ‘ THEN [Sales] END

which is formatted to $ with 0 decimal places

Label: Profit Ratio

IF [Selected Measure] = ‘ Profit Ratio ‘ THEN [Profit Ratio] END

which is formatted to % with 1 decimal place, and

Label: Items to Order

IF [Selected Measure] = ‘ Items Per Order ‘ THEN [Items Per Order] END

which is formatted to 1 decimal place

These 3 fields are then added to the Label shelf, and the label text is then edited to ensure all the fields are positioned side by side. Due to the fact only 1 of these fields will ever contain data, it looks as though there is only 1 label showing

Next we need to colour the bars, which is based on Selected Measure which is just added to the Colour shelf. When it’s added though, you only get a single measure in the colour legend at a time, so to set the colours you need to set Selected Measure to be each option of Sales, Profit Ratio, Items to Order (based on what the alias has been set though), either by manually typing into the parameter, or selecting a measure on the dashboard via the Measure Selector chart you built initially.

The tooltip is then set as follows

and once again the Label:xxx measures are all positioned alongside each other as, remember, only one will ever contain any data.

Finally it’s just a case of tidying up the chart – remove the rows & columns, the Measure to Show axis, and the Label:Date label.

The zero line of the rows is set to be a thicker grey line to get the desired display

Once done, the bar chart can be added to the dashboard, and if the Parameter Action on the Measure Selector chart has been set up properly, the bar chart display should change as that chart is interacted with.

My published version of the challenge is here.

Happy vizzin!

Donna

Can you sort dimensions with a single click?

Donna Coles3 Comments

Luke kicked off the 1st workout of 2020 with this challenge – using a ‘header’ sheet to control the sorting on a tabular view below.

Building the table

A couple of measures need creating initially :

Sales / Order

SUM([Sales])/COUNTD([Order ID])

Profit Ratio

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

The table consists of 3 measures aligned side by side, of different mark types : bar, text, bar. Sub-Category exists on the Rows.

The Sales measure is displayed first on the Columns, with mark type set to Bar, and the axis is fixed to start at 0 so there is minimal spacing between the Row label and the start of the bar. An additional calculated field [Profit Ratio]< 0 is added to the colour shelf and adjusted to be red/grey as appropriate.

As the requirement for the text Sales/Order measure was to right align it, adding Sales/Order measure to columns won’t work. Instead use MIN(0) setting the mark type to Gantt Bar, and making the mark type as small as possible, and setting the opacity to 0. Sales/Order is then added as a label which is then aligned middle left, and therefore right-aligning the text on the screen.

The final measure, Profit Ratio is added to the Columns shelf, also as mark type of Bar. Once again the [Profit Ratio]< 0 is added to the colour shelf.

All row/column lines, gridlines, zero lines and axis lines were set to nothing, but the Profit Ratio measure required a zero line to display. Using the zero line setting of the formatting would have meant a zero line would also display on the Sales bar chart, which wasn’t required. Instead I used a constant reference line of 0 on the Profit Ratio axis only.

And finally, the requirement stated that sort controls on the table should be switched off. This setting can be found under the Worksheet menu. All the axis were then hidden.

Building the ‘Header’ sheet (basic)

To start off I’m just going to describe how to construct a basic header table so it can then it interact with the table on the dashboard, and apply the sorting. I’ll revisit this all later, as getting the header to meet the specific requirements Luke stated was something I admit I struggled with.

At it’s simplest, you can build the header as follows:

  • Measure Names on rows and filtered to just the 3 measures we need
  • Measure Names on Text and aligned Middle Centre
  • Measure Values on Detail
  • Measure Names manually sorted in the order we want
  • Show Header unchecked for Measure Names on the rows shelf

Invoking the Sort

The sort is intended to work by clicking on a label on the header sheet, which in turn should sort the data in the table sheet.

To start off, a string parameter is required which is defaulted to the value Sales

A calculated field is also needed to define the sort measure, based on the sort parameter

Sort By

IF [Sort] = ‘Sales’ THEN SUM([Sales])
ELSEIF [Sort] = ‘Profit Ratio’ THEN [Profit Ratio]
ELSEIF [Sort] = ‘Sales / Order’ THEN [Sales / Order]
END

On the Table sheet, the Sub-Category field is then set to sort based on the Sort By field

With the Header sheet and the Table sheet added to a dashboard, a Parameter Action can be created which will set the Sort parameter based on the measure name clicked on :

At this point now, you should have the basics of the challenge – a header sheet causing the data in the table sheet to sort based on the label clicked on.

However, Luke added additional complexity to the Header sheet :

  • add an ▼ to identify the column label clicked on
  • change the font to be darker for the label clicked on
  • ensure the column labels not clicked on, remained ‘visible’ (ie did not appear to fade as per standard behaviour when data is selected in a viz).

Building the Header (Initial attempt)

My initial attempt to meet the stated requirements made use of a dual axis MIN(0) table, where one axis was of mark type square, with the Measure Names & ‘ ▼ ‘ on the label shelf, which was formatted to be a dark font, and set to only show when highlighted. The second axis was a Text mark type, with Measure Names on the text, formatted to a lighter font.

On click on one of the labels, the label associated with the square mark type would be shown, but the other labels ‘faded out’.

Whatever I tried to do to stop them, based on tricks I’ve tried in the past (using a Dummy field and applying a highlight action on the dashboard), didn’t work.

As much as I tried I couldn’t get beyond this, so published this as my solution, and waited for Luke to release his workbook. My version using this method is here.

Building the Header (Luke’s method)

Once Luke made his workbook available, I downloaded it to see what he’d done to keep all the header labels ‘enabled’ on click.

He used a dashboard action filter to set a calculated field ‘True’ to match a calculated field ‘False’. Both these calculated fields had to exist on the Detail shelf of the header sheet. (also note how the dashboard source sheet selects the Header v2 view which targets the Header v2 sheet directly (and not the equivalent view on the dashboard).

This is a technique Luke has blogged about on his own site following a discussion in the Tableau Community : https://www.tessellationtech.io/automatically-deselect-marks/

I have to admit, I had seen this post, and am kicking myself I didn’t remember it to try it out 😦

So…. back to my viz, and I applied Luke’s deselect marks idea to my header sheet, but it didn’t work. As I’d made the arrow show on ‘highlight’, this filter action was undoing the highlight, so preventing my label to show.

So I figured I’d just have to copy what Luke had done… and I started to build out his sheet… 3 columns of Min(0) set to Text with Measure Names on the Text :

All I could display was Min(0)… the only way I could see to get the words Sales, Sales/Orders, Profit Ratio displayed, was to add in Measure Values to the Detail shelf… but then all the words overlapped…

So I messaged Luke, as I just couldn’t understand this black magic… but before I got a response, I had a lightbulb moment….

Aliases!

The MIN(0) in the Measure Names had been aliased, to look like the same name of other measures, but I added a leading and trailing space around them all, as you can’t have duplicated names.

This really is something sneaky! but I was quite chuffed I found it before Luke told me.

To get the display and the formatting right, then required an ‘Arrow’ field for each measure along the lines of :

Arrow for Sales

IF [Sort] = ” Sales “
THEN “▼”
ELSE ” “
END

(note the leading & trailing spaces). This field is added to the Text shelf on the 1st Min(0) marks card. An equivalent field is then required for the other 2 measures, which are added to their respective marks cards.

Sort – Sales

[Sort] = ” Sales “

This boolean field is then added to the Colour shelf of the 1st Min(0) marks card. Again an equivalent is required for the other 2 measures. Setting the colours correctly will require each label to be clicked on the dashboard to get all the true/false permutations to show.

My revised version to meet the requirement is available here. Be aware if you do download, the field naming might not be identical to that above. This is because I have 1 workbook with both solutions in it, so in some cases needed to have duplicated versions of the calculated fields/parameters, to ensure both versions continued to function.

That was certainly an interesting challenge for Week 1 of the new year, and ultimately I was stumped! Hoping this isn’t going to be a theme for the weeks to come 🙂

Happy vizzin’!

Donna

Show me all the casinos within a certain distance from another

Donna Coles1 Comment

It’s getting near #data19, Tableau’s annual customer conference, which once again is being held in Las Vegas. So for #WorkoutWednesday this week, Lorna decided to incorporate this into her challenge, which was also intended to showcase some of the new features introduced into v2019.3 (so you’ll need this version at least to complete the challenge).

For the challenge, Lorna provided a dataset in a .hyper format that simply consisted of the (casino) name, address and it’s associated latitude and longitude values.

The core requirement was to show all the casinos within a user defied radius from another casino, which was selected by user by clicking on the map.

So we need to

  • identify the selected casino
  • determine the lat and long of the selected casino (the start)
  • determine the distance (in miles) between the selected casino and all the other casinos
  • identify the radius
  • restrict the display to just those within the specified radius

Identify the selected casino

This is simply a parameter Selected Casino which is initially hardcoded to have the default of “Mandalay Bay Resort & Casino” (it’s important you get this spelt right using the exact case too).

I then have a boolean calculated field

Is Selected Casino?

name = [Selected Casino]

which is true if the name of the casino in the dataset matches the value stored in the parameter

Lat & Long of selected casino

My aim is to get the latitude and longitude of the selected casino to be stored against each row in the dataset, so I need to use an LoD to do this

Selected Casino Lat

{FIXED : MIN(IIF([Is Selected Casino?], [latitude],NULL))}

Selected Casino Long

{FIXED : MIN(IIF([Is Selected Casino?], [longitude],NULL))}

Determine the distance (in miles) between each casino and the selected one

Here we can make use of the new 2019.3 function DISTANCE (defined here) within a new calculated field :

Distance (miles)

DISTANCE(MAKEPOINT([Selected Casino Lat],[Selected Casino Long]), MAKEPOINT([latitude],[longitude]), ‘miles’)

The first MAKEPOINT function above determines the start point using the information about the selected casino, and the second MAKEPOINT determines the end point, using the casino location information stored on each row.

Identify the radius

The viz requires the data to be restricted just to those within a defined radius, so we need another parameter, How many miles? to capture this value.

In Lorna’s viz, the parameter has a fixed range, which is specified as part of the parameter properties


In order to then restrict the casinos shown to just those within range, we need

Within specified miles?

[Distance (miles)]<= [How many Miles?]

The final thing we also need at this point, is a Distance (metres) field as this is used to colour the marks on the viz, and is also referenced on the tooltip.

Building the map

So now we’re at the point we can start building the map.

My initial observations upon hovering over Lorna’s published solution, was this would need to be a dual axis map. The text on the tooltip differed dependent on whether the mark was the selected one or not. The colour of the marks also differed; for the selected mark, the colour is black, while for the non selected marks the colour varied based on the distance.

So with this in mind, I decided to build another calculated field

Is Selected Lat

IF [Is Selected Casino?] THEN [latitude] END

which would just store a latitude value against the row of the selected casino only, as demonstrated below (I set it to have a geographic role of latitude)


I figured I’d then plot this as a dual axis on the map, but as soon as I added, second latitude pill, my display changed from having a map background, to a standard scatter plot chart 😦

So I started to go down the route of just making it a single axis chart, but the tooltips started to get too complicated, and the colouring just wasn’t working….

So I reverted back to the original plan, but this time, used the same latitude pill for my dual axis, which retained the map background

I now needed to differentiate on each map between the selected casino, and the others, and did this initially by Size

Size (selected)

IF [Is Selected Casino?] THEN 2 ELSE 1 END

Size (non selected)

IF [Is Selected Casino?] THEN 2 ELSE 1 END

These are independent fields which can be adjusted differently, and one is placed on the Size shelf of each marks card and adjusted so on one chart the selected mark is the biggest, and all the other marks are as small as they can be. On the other chart, the selected mark is made as small as possible.

I then started adding the various other features

  • added Distance (metres) to the colour shelf on the non- selected marks card, changing the colour palette to purple, and reversing the range so the lower the distance, the darker the colour
  • changed the mark type to Shape on the selected marks card and added Is Selected Casino? to the shape shelf, changing the shapes accordingly
    • The location marker image I found on flaticon.com (remember to add an attribution on your viz if need be to the icon creator)
    • See this blog for how to get the shape into Tableau
  • Set the tooltip appropriately for each marks card
  • Filtered the view to restrict to the casinos in range

The map background needs to be changed to street view, which is set via the Map -> Map Layers setting in the menu

We can now make the map properly dual axis, and can put on a dashboard.

Changing the selected casino

Now it’s on the dashboard, we can use a parameter action to change the value of the selected casino ‘on click’

Displaying count of casinos within radius

The dashboard shows a summary on selection, which I added as the title of the map sheet

I created a calculated field

Count Casinos

TOTAL(SUM([Number of Records])) – 1

which needs to exclude the selected casino, hence the subtraction. I added this to the Detail shelf, so I could reference it in the sheet title.

Ensuring all the casinos remain ‘visible’ on selection

By default, on selection of a different casino, all the other casinos within range are faded out, which we don’t want to happen

So I used the same ‘dummy’ trick with highlight action that I documented in a previous blog to prevent this from happening


The dashboard just needed a few more tweaks – adding the title (which references the parameters), adding the colour legend and the distance parameter, and I was done. I published my viz.

Except I wasn’t completely happy…

When I increased the number of miles, the location shape became ‘hidden’ beneath the other marks, but if I changed the order of the axis, tiny marks were visible on each casino, which I couldn’t get the colour to match

This stumped me, so I had to check out Lorna’s solution, but I stared for some time and just couldn’t figure out what she’d done. It was Rosario Gauna’s solution where I immediately saw what I had to do, as Rosario had made a comment in the worksheet caption as a reminder.

Frustratingly, this is exactly what I do to remind me of this sneaky trick… the small marks on the ‘selected casino’ mark shelf, were ‘hidden’ by simply right clicking on the Is Selected Casino? shape legend and ‘hiding’ the false values

Doh! I wish there was a way you could see more easily on the sheet in Desktop where someone has done this.

My published viz is here.

Happy vizzin!

Donna