Tag: MIN(1)

How much do these states contribute to the total?

Donna ColesLeave a comment

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

Can you build a MoM Progress Report?

Donna ColesLeave a comment

Ann set the challenge this week, which for seasonsed #WoW participants like myself, was relatively straightforward. However it encapsulates a couple of concepts that I’ve only learnt from doing #WoW challenges over the years, so if you’re a relatively newbie, this is a great challenge for giving you a firm foundation for the future.

Determining the dates to compare

To start off, Ann stated that we needed to compare Oct 2019 with Nov 2019 but not to hard code the dates. I therefore used a parameter to drive this, which I created by first creating a new calculated field

Order Date MY

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

Against every row, this sets the date to be 1st of the month.

I then created a parameter Order Date MY Parameter by right-clicking on the Order Date MY field and selecting Create -> Parameter. This has the effect of automatically populating the parameter with all the month/year options. I also set the format of the date to be March 2001 (ie month year), and the default value to be November 2019

I then created the following fields :

Current Month

[Order Date MY Parameter]

Previous Month

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

These two fields would ultimately drive the rest of the calculations I needed.

Sales by Sub-Category KPI

Ann is a fan of capital letters, so first up, you’ll need to create a field to store the Sub-Category in uppercase :

Sub-Cat UPPER

UPPER([Sub-Category])

Now for each KPI column, we need to determine

  • Sales for Current Month
  • Sales for Previous Month
  • % difference between the two
  • An indicator whether the % difference is less than 100% or not

Sales Current Month

IF [Order Date MY] = [Current Month] THEN [Sales] END

Sales Previous Month

IF [Order Date MY] = [Previous Month] THEN [Sales] END

% Sales

SUM([Sales Current Month])/SUM([Sales Previous Month])

formatted to a percentage with 0 dp

Sales Indicator

IF [% Sales] < 1 THEN 0 ELSE 1 END

You might initially think to set this to a string of ‘under’ and ‘over’ or similar, but setting to 1 and 0 will be useful later on when we need to calculate the Overall KPI.

Build the Viz

To create the ‘cell block column’ layout, we need to make use of an old friend Min(1) to create an axis, which allows us a bit more flexibility. This is the first of the techniques I was first introduced to via #WoW, and is a valuable concept to have in your arsenal.

You can already see the makings of the column just with these 2 pills. We then need to apply the following

  • Set mark type to be Bar.
  • Add Sales Indicator to Colour shelf, and adjust colours to match
  • Add Current Month, Previous Month, Sales Current Month, Sales Previous Month and % Sales to Tooltip and format tooltip to match the requirement.
  • Edit Axis and fix axis to start at 0 and end at 1, remove the title, and set tick marks to None
  • Duplicate the MIN(1) pill (click the pill, hold down Ctrl and drag to sit next to it). Make this pill dual axis and sync
  • Edit the top axis and change the title to SALES, and again set tick marks to None.

Orders by Sub Category KPI

As with the Sales KPI, we again need to know 4 things for this KPI

  • Number of orders in current month
  • Number of orders in previous month
  • % difference between the two
  • an indicator whether the difference is less than 100% or not

Orders Current Month

COUNTD(IF [Order Date MY] = [Current Month] THEN [Order ID] END)

Orders Previous Month

COUNTD(IF [Order Date MY] = [Previous Month] THEN [Order ID] END)

% Orders

[Orders Current Month]/[Orders Previous Month]

formatted to percentage to 1 dp

Orders Indicator

IF [% Orders] < 1 THEN 0 ELSE 1 END

The viz is then built exactly as above, adding further MIN(1) pills to the Columns shelf.

Units by Sub-Category KPI

Once again 4 fields are required, which are pretty identical to the ones defined for Sales above, but where any reference to Sales is replaced by Quantity.

Then build the column using further MIN(1) pills again.

Overall KPI

The overall KPI provides a % score based on the % of the 3 measures that are on target (ie 100% or more), along with an indicator (red circle), if the overall KPI < 100%.

Overall Score

[Qty Indicator] + [Orders Indicator] +[Sales Indicator]

This is where using 1 & 0 as the indicator value rather than a string value becomes handy. We can simply sum the three outputs together, giving an output of 0, 1,2 or 3.

% Overall Score

[Overall Score]/3

formatting to percentage set to 0 dp.

Overall Indicator

IF [% Overall Score] < 1 THEN ‘●’ END

This is where the second technique that was introduced to me originally by #WoW comes in : using geometric shapes in Text fields.

I use this concept A LOT in my work dashboards. I use this website to copy the shapes from.

Building the Viz

This time, we need to use Min(0.5), and set the Mark type to Text, adding Overall Indicator and % Overall Score to the Text shelf. The text is the formatted so the circle indicator is coloured red.

Using the font colour as a way of ‘colouring shapes’ is another concept I first discovered during #WoW, coupled with the fact that the field only displays a circle based on meeting some conditional logic. So while the Text field looks like a red piece of text (ie the circle) is visible, it won’t in fact always be there.

The axis is again fixed from 0 to 1, so this makes the text centred, and the Toolip is formatted to match requirements.

A dual axis is again required, but this time, the mark of the 2nd axis should be set to be a circle, with the size set to be the smallest possible. The colour should also be adjusted to 0 transparency (basically this mark needs to disappear). There should be no pills on the marks card of the secondary Min(0.5) axis.

And at this point, the viz should be pretty much complete (subject to any further formatting of fonts and row/column/grid lines – you might want to format the column lines to be white to break up the display, and adjust the size of the bars).

My published version is here.

Happy vizzin’!

Donna

Corey’s Table Challenge

Donna ColesLeave a comment

I’m going to attempt to be really brief this week, as I have very little time to get this drafted before I head off on my holibobs for a couple of weeks (so I’ll be off grid for the next couple of #WorkoutWednesdays but will attempt to catch up when I return).

This week’s challenge was set by IronViz 2018 finalist, Corey Jones, and focuses on formatting a table – full challenge details here.

Presenting data in Tableau in an ‘out of the box’ table chart type, does not allow row level formatting in the manner described in this challenge (Tableau is not Excel you know :-))

So you have to be creative, and this is what this challenge is all about.

Corey gives the clue that no more than 3 sheets are allowed, hinting that the above presentation is not all on one view, but at least 2 carefully arranged side by side on a dashboard.

I had 3 views in my dashboard – the shaded table of sub-category and measures, the stacked bar of % of region sales vs rest, and the title.

Now I ended up with 3 including the title, as I used the [Region] field as a quick filter throughout my build, and so to enable the title to change dynamically I needed to create a sheet so I could reference that field. Having looked at Corey’s solution later on, he chose to use a parameter to drive the Region selection. Parameters can be referenced in dashboard titles, so in Corey’s solution, the Sub Category list on the left hand side is also a view, whereas I combined with the measures. The only thing I couldn’t achieve with the combination was being able to left-align the Sub-Category heading, while leaving the other headings (Sales, Profit etc) centre-aligned.

The formatted table

The magic ingredient in building formatted tables is using an axis with labels. In this instance the field MIN(1) is your best friend.

  1. Add [Sub-Category] to rows
  2. Type MIN(1) into columns
  3. Set mark type to bar
  4. Add SUM([Sales]) to the Text shelf and right align
  5. Fix the axis from to start from 0 and ends at 1

and you get…

… something that looks pretty tabular, but you now have more flexibility with formatting styles.

This concept forms the basis for the whole table, but we need to step back quickly, as we need to set up some calculated fields.

Sales, Profit & Quantity are already defined in the dataset, but we also need

Profit Ratio

SUM([Profit]) / SUM(Sales)] formatted as a percentage to 0 decimal places

Total Sales for Year & Category

{FIXED YEAR([Order Date]), [Sub-Category]: SUM([Sales])}

This is a Level of Detail (LoD) calculation that stores the total sales for the year (2018) and subcategory, regardless of the [Region] filter being selected

% Sales for Selected Region

SUM([Sales])/SUM([Total Sales for Year & Category]) formatted as a percentage to 0 decimal places

Note – Technically this field will return % sales if any filter is applied, not just region

Highlight

[% Sales for Selected Region] > ([Highlight Threshold]/100)

This returns true where the % sales is more than the threshold selected by the user by entering a value into the [Highlight Threshold] parameter

The [Highlight] field can now be used to format the ‘table’ by adding to the Colour Shelf, setting true to be grey and false to be white.

Making the text bold

My solution differs from Corey’s. I chose to create the following calculated fields

LABEL : Sales Bold

IF [Highlight] THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is true.

LABEL: Sales Normal

IF NOT([Highlight]) THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is false.

Both these fields are placed side by side on the Label shelf, and with the ‘bold’ field formatted to be bold. As no row will ever have a value set in both fields at the same time, only a single value is displayed

Showing the column title at the top

I did this by creating an additional axis MIN(0), and applying dual axis which I synchronised. I removed all the pills from the MIN(0) marks card, and the [Measure Names] that had been automatically added to the colour shelf on the MIN(1) marks card.

This gave me an axis at the top which I could then edit – I changed the title to Sales, and set the axis tick marks to None for both major & minor ticks marks. On the bottom axis, I also had to edit, in this case setting the title to nothing as well as setting the axis ticks to none.

The height of the axis was also adjusted to make it appear closer and the font formatted accordingly. The Sub-Category was also set to not ‘Show Header’.

To create the other columns, the process is repeated with further dual axis MIN(1) & MIN(0) fields added to the columns, with further bold/normal Label fields also created.

The More Fields to the bottom left lists all the MIN(1) & MIN(0) cards in order.

To get the ‘column’ line to appear between the sub-category and sales columns, I set the transparency of the MIN(0) card associated to the Sales measure back to 100%, and it gave the appearance of a solid line.

Stacked Bar Chart

This is also a dual axis chart, which plots the % Sales for Selected Region field alongside our new friend MIN(1), where the MIN(1) axis is set to just over 1.

  • The Measure Names colours are set to grey (% Sales) and white (MIN(1))
  • The MIN(1) axis is set to the back
  • % Sales for Selected Region is the label on the associated card, and left aligned
  • A new field which is basically 1 – [ % Sales for Selected Region] is added to the label of the MIN(1) card and right aligned.
  • A border is applied to the bars via the Colour shelf

To create the ‘column heading’ a new field is required

LABEL: Bar

[Region] + ‘ vs. All Other Regions’

This is then added to the column shelf to get

so when the Region filter changes, the title changes too.

Both these sheets are then positioned carefully side by side on a dashboard, setting the Fit to Entire View. The padding of the table sheet is adjusted so it has 0 outer padding on the right, whilst the padding of the stacked bar is adjusted so it has 0 outer padding on the left.

And finally, the Region filter is added to the dashboard, set to floating positioned just near to the heading of the stacked bar. It’s size is also adjusted so only the arrow part remains.

There’s a fair few other little formatting bits and bobs to get the text right, remove columns etc, but these are hopefully settings you already know about. Feel free to contact me if you can’t figure something out.

My solution is available here.

Happy vizzin!

I’m off for some sunshine 🙂

Donna