Can you build a mobile calendar picker?

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 add and remove items from a set?

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

How much do these states contribute to the total?

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

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

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

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

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

Creating the State set

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

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

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

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

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

Remove the map features

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

Map Tooltip

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

# Orders

COUNTD([Order ID])

# Customers

COUNTD([Customer ID])

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

Creating the State List

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

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

Adjust the colour of the bar to suit, then

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

Invoke the Set Actions

Add both sheets to a dashboard.

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

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

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

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

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

Automatically deselect states

Create new calculated fields called

True

True

False

False

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

Then back on the dashboard create a Filter action as follows

Building the Bar Charts

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

However Sean added some extra charts to the display.

Sales Chart

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

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

Total Sales

{FIXED:SUM([Sales])}

Sales of Selected States

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

% Sales of Selected States

[Sales of Selected States] / [Total Sales]

Format all these accordingly.

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

Orders Chart

This is pretty much the same as above….

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

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

Total Orders

{FIXED:COUNTD([Order ID])}

Orders of Selected States

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

% Orders of Selected States

[Orders of Selected States] / [Total Orders]

Customers Chart

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

Total Customers

{FIXED:COUNTD([Customer ID])}

Customers in Selected States

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

% Customers of Selected States

[Customers of Selected States] / [Total Customers]

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

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

Happy vizzin’! Stay Safe!

Donna