Can you visualise time in a unique way?

Donna ColesLeave a comment

Lorna’s challenge this week focused on how to present time data in a different way. Often visualisations involving time, use line charts to show haw a variable has changed over time. And while these are perfectly valid and serve a very necessary purpose, there are times when other insights can be gained by thinking more creatively. This whitepaper by Andy Cotgreave from Tableau, discusses other ideas too, if you want to explore further.

Anyway, back to the challenge. While not mentioned in the requirements, the Workout Wednesday ‘latest’ page, gave a clue to Lorna’s approach…

..sheet swapping. So this was already in my mind as I started to understand the requirements further. Some did complete the challenge within a single sheet, but my solution guide will involve sheet swapping.

Joining the data

Unlike most challenges, this one involved combining Superstore data with some order time data that Lorna had published. So the first thing necessary was to join the two data sets together, which was achieved with a simple inner join on Order ID

Building the Small Multiple layout

The overall display was a 3 x 4 table of months vs quarters. Each row was identified as a date quarter, which was simply QUARTER([Order Date]). For the Columns I needed to group the months of the years into the values of 1,2 or 3 which required an additional calculation:

Month Column

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

% (modulo) gives the remainder of a number when divided by x, so January which is month 1, divided by 3 results in 1; February (2) divided by 3 is 2, which March (3) divided by 3 is 0. But I want March in column 3, hence the logic above.

Plotting Month Column vs QUARTER([Order Date]) and placing the month name on Text you can see the basic layout we’re aiming for:

Determining Hour of Order

The y-axis on the chart is the hour of the order. The Time of Order is sourced from the additional spreadsheet data source we joined to above, and is stored in a string field in the format hh:mm:ss

I chose to get the hour portion of this field by using a bit of string manipulation:

Hour of Order

INT(TRIM( SPLIT( [Time of Order], “:”, 1 ) ))

This is looking at the Time of Order field, finding the 1st occurrence of a colon (:), and splitting off everything up to it. Right-clicking on the Time of Order field and selecting Transform->Custom Split, allows you to define this without writing the calculation directly

However, this will create a string field storing ’05’, or ’16’ etc in a field automatically named [Time of Order – Split 1].

TRIM( SPLIT( [Time of Order], “:”, 1 ) )

However, we need a integer field, so editing and wrapping the above automatically generated calculation with INT(), will change the data type, and ’05’ will become 5. The field is also renamed.

Orders by Day chart

Having known I was going to tackle this using Sheet Swapping, the first chart I decided to tackle was the version by Day.

For each month (each small multiple), a chart of Hour of Order by Day of Month is being displayed, and it’s a dual axis chart, where one axis is being used to present the orange circles when each order was placed, and the other the grey bar showing the range of hours in a day the orders were placed over.

Based on the small multiple display we showed earlier, we need to add Hour of Order to rows and DAY([Order Date]) set as a discrete blue pill to columns.

We now need to

  • reverse the Hour of Order axis
  • change the axis label to Hours
  • fix the axis to display every 5 hours
  • change the mark type to circle

Filter to latest year

Rather than hardcoding to the latest year of 2019, the latest year can be determined by using the Top 1 functionality of the Filter dialog. This means if the data source did change to include 2020, the chart would automatically show the data related to 2020 instead.

Colour & Size by number of Orders

A unique order is identified by Order ID, and it is possible that an order can contain more than 1 row of data, so Number of Records can’t be used. COUNTD([Order ID]) gives the require result. You can either create a dedicated calculated field to store this calculation, or you add Order ID to the Colour shelf, then change to use the Count (Distinct) aggregation

Change the colour range to use the Orange scheme. Then add COUNTD([Order ID]) to the Size shelf as well, either by reproducing the same steps above, or simply duplicating the field already on the Colour shelf by holding down Ctrl as well as clicking on CNTD([Order ID)] on the Colour shelf, and dragging onto the Size shelf.

Adding Month([Order Date]) to the Detail shelf, and the Tooltip can then be created:

<DAY(Order Date)>, <MONTH(Order Date)>

At <Hour of Order> there were <CNTD(Order ID)> orders

Range of Order Hours

I chose to use a different method from Lorna’s solution to present the range of hours in day, utilising a Gantt view instead which is a technique I’ve used many times in the past.

For this I need to identify 3 things : the maximum hour in each day an order was placed, the minimum hour in each day an order was placed, and the difference between the two.

Max Hour Per Day of Month

{FIXED [Order Date]: MAX([Hour of Order])}

Min Hour Per Day of Month

{FIXED [Order Date]: MIN([Hour of Order])}

Time Range Days

[Max Hour Per Day of Month]-[Min Hour Per Day of Month]

On my secondary axis, I then plotted Max Hour Per Day of Month as a Gantt mark type which was then Sized by -1*MIN([Time Range Days])

Setting it to be Dual Axis, synchronising the axis, ensuring the Gantt marks are ‘moved to the back‘, then hiding the 2nd axis, and you have the final chart layout.

Add Trend Line

Click the Analytics tab on the right hand side, and drag Trend Line onto the chart, dropping onto the Linear : Hour of Order lozenge (hard to screen shot this). Then format the trend line to be a dotted, grey line.

Then it’s just a case of tidying up everything; You may need to adjust the size of the marks, remove the text from the tooltip for the Gantt mark, format the font sizes, remove grid lines etc, and get rid of the unwanted headers.

Name this sheet By Day or similar.

Orders by Weekday chart

Start by duplicating the by Day chart you’ve just created, and change the DAY([Order Date]) field in the columns to WEEKDAY([Order Date]).

You’ll notice the Gantt chart is now not working properly, as it’s not spanning the range in all cases (you may need to adjust the size of the marks to see this properly).

This is because the Gantt is based at the day level and now we’re at the weekday level. We need to create some equivalent fields to work at the weekday level :

Max Hour per Weekday per month

{FIXED DATEPART(‘weekday’,[Order Date]), MONTH([Order Date]), YEAR([Order Date]): MAX([Hour of Order])}

Min Hour per Weekday per month

{FIXED DATEPART(‘weekday’,[Order Date]), MONTH([Order Date]), YEAR([Order Date]): MIN([Hour of Order])}

These fields are finding the min/max hour for each weekday, month, year combination.

Time Range Weekdays

[Max Hour PerWeekday of Month]-[Min Hour PerWeekday of Month]

It’s then a case of plotting the Gantt using the Max Hour per Weekday per month field and Size-ing by MIN([Time Range Weekdays]) * -1, following the information detailed above if need be.

Format the Weekday axis

If everything’s been changed correctly, then the Weekday version of the chart should be pretty much there, the only thing that needs tweaking is the axis which is displaying the full name of the days of the week. Right click the axis, and select format and under the Dates section, you can change to ‘Abbreviation’.

Right-click again and choose Rotate Label to get the dates displaying the right way round


and you’ve got your Weekday view – name the sheet by Weekday or similar

Sheet Swapping

On a dashboard, add a container (either vertical or horizontal, it doesn’t matter), and add both the charts to it, hiding the titles of both. Ensure both are set to Fit Entire View, but don’t worry about sizing them any further.

Create a parameter, Time Selector, which is a string listing 2 options: Days & Weeks

Then create another calculated field

Time Selected is Days

[Time Selector] = ‘Days’

which will return true if the parameter is ‘Days’ and false otherwise (ie Weeks is selected).

On the By Day sheet, choose to show the parameter control, and select the option to be Days.

Then add the Time Selected is Days field to the Filter shelf. The only option available for selection will be True. Tick this.

Then switch to the By Weekday sheet and again show the Time Selector parameter. Select the Weeks option.

Then once again, add the Time Selected is Days field to the Filter shelf. The only option available this time, will be False. Tick this.

If you switch back to the By Day sheet, you’ll find that it is now blank. Change the parameter to Days, and the chart will show, but the By Weekday sheet will now be blank.

If you go back to the dashboard, only one of the charts should now be showing. Show the parameter control, and when you change it, you should find the charts being replaced.

It should now just be a case of finalising the look of the dashboard – adding the title, formatting the parameter input and re-positioning it.

My published version of the viz is here.

Happy vizzin!

Donna

Can you build a beautiful & dynamic bar chart?

Donna ColesLeave a 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 Coles1 Comment

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

Sales Performance Dashboard

Donna ColesLeave a comment

For Lorna’s final #WorkoutWednesday challenge of 2019, she asked us to recreate a dashboard, where the focus on the challenge was branding, rather than anything too difficult.

That was a relief, as I needed to squeeze both completing the challenge and writing this blog into the limited free time I have in the run up to Christmas, where most of my time is focused on preparing for the big day, catching up with friends, and recovering from my work leaving do (having left my job of the last 20 years…).

This write up is therefore going to be brief, and I’ll just point out the bits I think will be of most use…

Plotting the circles on a horizontal line

The Year filters and the Sub-category by month circle charts are both arranged with a horizontal line running through the circles. This is achieved by plotting MIN(0) on the y-axis, and formatting the zero line to be a solid coloured line.

Aligning the charts on the dashboard

The dashboard consists of 3 charts displaying Sales by date; The Sales by Day area chart at the top, the Sales by Month bar chart above Sales by Month by Sub-Category circle chart.

All 3 charts stretch to the end of the space available width wise, even when filtered by year, and the bar chart and circle chart line up vertically, so the months are in line.

To get this to work, the date fields need to be discrete (blue) rather than continuous (green).

When continuous, additional ‘padding’ at the start and end of each axis is added. Also for a circle, the mark being plotted is the centre of the circle, but for a bar chart, the mark plotted is the top left corner of the bar, so the bar appears off centre, as demonstrated by the synchronised dual-axis view below.

This goes away when the date is made discrete (blue).

Padding

The Sales by Day area chart and the Sales Performance Title (text box) butt up against each other with no white-space between. This is achieved by reducing the outer padding on the objects to 0.

Highlighting

The 3 charts at the bottom of the screen; the Sales by Month bar chart, the Sales by Month & Sub-Category circle chart and the Sales by Sub-Category bar chart, all interact with each other on hover, regardless which chart you hover on.

This is managed via a Highlight dashboard action, where only the 3 charts of interest are selected as both the source and the target sheets

Branding & adjusting the colour palette

The colour scheme I chose to recreate this challenge in differed from Lorna’s solution. I selected a range of browns from the colour palettes I already have installed, but felt the darkest brown was just too strong. So I adjusted the end colour of the palette by clicking on the colour square displayed at the end of the range.

This opens the colour palette dialog, where I could select a lighter shade

which changes the palette being used to ‘Custom Sequential’

To ensure I always got the same range on each chart, I made a note of the colour hex number I selected : #83514a, which I could then just type in, each time I wanted to adjust the palette.

My published version of the challenge is here.

Hopefully you might find something helpful in this post 🙂

Merry Xmas y’all!

Happy vizzin’!

Donna

Can you build a retention heat map with a marginal histogram?

Donna ColesLeave a comment

Week 50 of #WorkoutWednesday saw Curtis deliver a heatmap based challenge with a few twists.

The challenge consisted of the following main components :

  • Identify a ‘cohort’ for each customer based on first week of sale
  • Understand the % of customers in each cohort making purchases per week for the following x weeks, where x was driven by a user defined input.
  • Build a heat map for weeks 1 to x-1, but only include ‘full’ weeks
  • Build a bar chat for week x
  • Display a summary BAN for volume of week x returning customers as a proportion of the customers in the cohorts displayed.

Identify cohort

Each customer needed to be put in a cohort based on the week of their first order, which uses a FIXED LoD (level of detail) calculation.

Cohort

DATE({FIXED [customer_id]: MIN(DATETRUNC(‘week’, [order_week]))})

% of Customers

The number of unique customers making a purchase is a simple calculation

Customer Count

COUNTD([customer_id])

and plotting these fields alongside order_week, we start to see where we’re heading

But we need to index each order_week in relation to the cohort: For the cohort dated 01 Jan, week 1 is order_week 08 Jan, but for the cohort dated 08 Jan, week 1 is order_week 15 Jan.

Week Index

DATEDIFF(‘week’,[Cohort],[order_week])

We also need against each row, the number of customers in the cohort, which is equivalent to the number of customers in week 0.

New Customers

{FIXED [Cohort]: COUNTD(IF ([order_week])=([Cohort]) THEN [customer_id] END)}

This is saying for each cohort, count the number of distinct customers when the order week is the same as the cohort week.

Now we’ve got that, we can work out the % of returning customers each week:

% of Customers

[Customer Count]/SUM([New Customers])

Putting all these onto a data table, we can see all the figures are making sense

But we want to limit the number of weeks, based on the user input, so we need a parameter, that is limited to range between 10 and 26 weeks. Note how the display format of the parameter is set.

Time Period

To restrict the weeks, we need another calculated field we can filter by

FILTER:Weeks Index to Display

[Week Index]> 0 AND [Week Index]<[Time Period]

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

This verifies we’ve got the numbers we need to start to build the heat map.

Heat Map

Duplicating the data sheet I’ve created above, then moving the pills around, we get the ‘bones’ of what we’re after, but you can see towards the bottom of the cohort list, that we have missing entries, where there aren’t enough weeks for the cohort week being considered. We don’t want these rows to display, so need to filter the data someway.

I approached this by determining the number of unique customers at week x, then seeing whether there was any or not :

Count Customers at Time Period Param

{FIXED [Cohort]: COUNTD(IF ([Week Index])=([Time Period]) THEN [customer_id] END)}

FILTER: Complete Cohorts

[Count Customers at Time Period Param]>0

Adding the Count Customers at Time Period Param field to the view, you can see that for a 10 week time period, from the cohort dated 29th October onwards, the count of customers is 0, so these are the rows I’m going to filter out, by adding the Filter : Complete Cohorts field to the filter shelf, and setting to True.

So lets turn this into the heat map display:

  • Remove the Count Customers at Time Period Param field
  • Move % of Customers onto the colour shelf
  • Move order_week and Customer Count onto the Tooltip shelf
  • Change the Sort of the Cohort to descending
  • Add New Customers as a discrete field next to Cohort
  • Change the colour palette to use Vidris
  • Change the formatting of the Cohort and order_week fields to suit
  • Set the tooltips
  • Apply other formatting changes to adjust font size, alignment, row/column lines etc

Bar Chart

For the bar chart, we only want the data for week x, so we need to filter the data just to this week again

FILTER : Time Period

[Week Index]=[Time Period]

The bar is basically plotting Cohort against % of Customers, but we need some additonal wizardy to get the desired display.

The requirement states the bar chart needs to be hardcoded from 0-50%, and so we need the light grey bars behind each bar to ‘fill up’ to 50%.

We do this using a synchronised dual axis, with the secondary axis set to MIN(0.5), coloured light grey, and the secondary axis ‘pushed to the back’. Adjust the size of the bars to suit.

But we need to label the end of the secondary axis with the % of Customers, and the label needs to be displayed to the right of the bar.

As we haven’t fixed the axis at all, adding % of Customers to the label shelf of the secondary axis, and reducing the font size and setting to be right- aligned, we get what we need. Then just add all the relevant fields to both marks cards that are needed for the tooltip, and hide both axes and the Cohort field.

But the chart does need to be labelled with Week x. So this requires a calculated field

LABEL: Week Index

‘Week ‘ + STR([Week Index])

which is then added to the Columns shelf and left aligned, and Hide Field Labels for Columns

Other formatting of the chart is applied to ensure no row/column borders or gridlines are being displayed.

Summary BAN

The summary BAN is simply % of Customers displayed as Text but filtered by FILTER: Time Period = True, with the formatting of the text adjusted to suit.

Putting it all together

When adding it all to the dashboard, the heat map and the bar chart need to be sited side by side and both set to Fit Entire View so the rows for each cohort line up. The colour legend is then sited below the heat map, and I then used blank objects either side to make the legend line up with the heat map.

The line at the top of the dashboard is created by using a vertical container object, where a blank object within is then set to height 0 with a black background. Working with containers can be a bit fiddly at times.

I found this a really enjoyable challenge – thanks Curtis!

My published viz is here.

Happy vizzin’

Donna

Can you swap States?

Donna ColesLeave a comment

Week 49 of #WorkoutWednesday2019 was Luke’s last challenge of the year, so following a poll he posted a ‘notably tough’ challenge.

On the face of it, it didn’t look too bad…. I figured it would involve a trellis chart for the small multiples, set actions (for the state selection), and something table calculation related to crack the ranking. Hovering over the state label for each small multiple, I also figured some dual axis was probably at play. The bit that actually looked most tricky to me initially, was displaying the States just as their shapes.

Single State viz

I decided to build the single state viz first. I created a set (Selected State) based off the [State] field, and selected California as the state ‘in’ the set.

I then started to build the viz simply by double-clicking on State (which automatically adds State to the detail shelf, and the automatically generated Long & Lat fields to the rows & columns. I added the Selected State set to the filter shelf, which immediately restricted the data to California, and I changed the mark type to filled Map.

To isolate the display just to the State itself, I figured would be something to do with the various Map Layer options available (menu Map -> Map Layers); I wasn’t sure exactly what but found by unchecking every pre-selected option, I got a ‘clean’ display.

Changing the opacity of the mark colour to 0 and setting the border to red gave me the desired display.

Maybe the State shape wasn’t going to be as much of an issue as I thought….?

The cities needed to be displayed as circular marks, so I knew this would need a dual axis to make this work. I duplicated the Latitude field (hold ctrl as you click and drag the field) and did the following :

  • changed the mark type of the duplicated field to circle
  • added City to the Detail shelf
  • added Sales to the Size shelf
  • changed the colour of the mark to blue, upped the opacity to 50% and removed the shape border

I then made the chart dual axis and increased the size of the circles to suit.

Finally I added State and Sales to the Label shelf of the Map marks card and adjusted the Label formatting to suit.

Ranking

So the requirement was to show the top 25 states in a ‘grid’ or ‘trellis’ format ordered by the state with the most sales.

However there was a subtlety to this:

  • the grid should always show 25 states
  • the selected state should not display in the grid
  • the overall rank of the state should display, so if the 3rd largest state is selected, the displayed ranking would be 1, 2, 4, 5, 6… up to 26; 3 would be omitted from the list.

The best way to explain how I tackled this, is to show the info in a tabular format.

Firstly, create a table of Sales by State sorting the State by Sales desc

Apply a Quick Table Calculation of Rank to the SUM(Sales) pill. Then edit the table calculation, setting the rank to be unique and fixing the Compute Using to apply over State.

Add the Selected State pill alongside State, and as California is still our selected state, you see we now have two ‘1’s. This is because we fixed the table calculation to State, so its now being applied for each ‘In/Out Selected State’

This is the table calculation we want to use to filter our data to get the ‘top 25’ fields. With ‘California’ selected as being ‘in the set’ and therefore the selected state, we need the next 25 states to the be ones showing in the grid. This being from New York to Oklahoma.

Holding down ctrl and then dragging the Sum(Sales) pill to the filter shelf (ie duplicating the pill), you can set it to show at most 25

However, the ‘rank’ displayed against each row, isn’t the rank we want to show on the viz. New York is the 2nd largest state, so should be labelled no 2, not no 1 as shown above.

We need another version of the Sales rank. Add Sales back into the chart, and again apply a Quick Table Calculation of Rank.

The 2nd rank is now showing values 1 – 26, and if you edit the table calc, you’ll see it automatically has set itself to ‘table down’ which is actually being applied to both State and the In/Out Selected State. Alter the table calc to be unique and fix it to apply to State & In/Out Selected State, which will ensure the values remain the same regardless as to how you move pills around.

This second rank is what is used to display the ‘overall rank.

Finally, we’ve still actually got 26 states shown, when we only want the 25 states ‘out’ of the set displayed. We simply apply the sneaky trick to ‘hide’ the In (click on the ‘In’, right-click and select Hide).

Change the set value to Ohio for example, and re-show the hidden data (click on In/Out Selected Set pill and Show hidden data). You’ll see Ohio is 8th in the overall rank, but is ‘in’ the set so ranked 1 in the ‘top 25’ filter rank.

When I come to build the map trellis later, it is these table calcs and techniques I will have applied.

Trellis Chart

In this instance we have a fixed number of states to display (25), to show in a 5×5 grid; 5 rows and 5 columns. Each of the 25 states we have needs to be assigned a row number and a column number.

Let’s go back to the tabular display to help with this. With the display just showing the 25 States ‘out’ of the set (by hiding the ‘In’), let’s add INDEX() to the view. INDEX() is a table calculation most often used to number rows. INDEX() is set to compute over the State only (so the numbers 1-25 are listed). Note this is giving the same information as the Sales ranking discussed above, and we could reference the same field, but INDEX() is more generic and referenced in many trellis chart solutions, so let’s stick with that.

What we’re looking to achieve, is the first 5 rows listed, to appear in the 1st row, across 5 columns. Rows 6-10 would be in the 2nd row etc etc. I need to build

Cols

FLOAT(INT((INDEX()-1)%5))

This takes the Index value and subtracts 1, and returns the remainder when divided by 5 (%5=modulus of 5). Storing the final output as a float will become clearer later.

Rows

FLOAT(INT((INDEX()-1)/5))

This takes the Index value and subtracts 1, and returns the integer part of the value when divided by 5. Storing the final output as a float will become clearer later.

Adding these on to the table, and again setting the table calculation to compute by State only, you get

and if we shuffle the pills around to create the rows & columns, and keep just the pills we need we get

But it’s all upside-down : we need California top left, not bottom left. We can fix this by editing the Rows axis, and reversing it.

Now by simply changing the mark type to Map, we can get the shape states to show – it’s like magic! You’ll need to increase the size to see them properly.

Side note – this was actually quite a revelation; it took some time for me to get to this, having unsuccessfully had views with Lat & Long displayed (as that’s what a map chart always needs right?), resulting in the state shapes being positioned all over the place. Writing this blog and reproducing steps as I type, has made things seem much simpler, than when I was tackling the challenge initially!

As you can see, things aren’t perfect yet, but we’re on the right track. The axis need editing to extend them. Rows is set from -0.5 to 5, and Cols from -0.5 to 4.5 (this is why we needed to set the field to be FLOATs).

The colour of the mark also needs adjusting to match what we did when building the single state viz.

The label positioning isn’t also right, even if you change the alignment, so move the State from the Text to the Detail shelf and don’t show any labels. Then create an additional axis on the rows by duplicating the Rows field to exist alongside, then ‘type’ into the second instance and change to Rows+0.5

Make this dual axis, synchronise and change mark type to Text. Make sure the opacity on the colour is increased back to 100% and adjust the size of the text.

Now it’s just a case of tidying this view to match the requirements; adding additional fields to the Text shelf, removing axis, row/column lines and gridlines etc.

Once done, both the views can be added to a dashboard, and the ‘select state’ interactivity is achieved using a Set Action dashboard action.

And that’s it. As stated I did have some struggles when building initially, but as most things, it’s down to the path you happen to follow. If I’d initially built based on the order I’ve authored this blog, and the tabular views I’ve built to demonstrate techniques, I wouldn’t have had any problem. But it’s all valuable learning experiences and adds to my understanding!

My version of the viz is published here.

Happy vizzin!

Donna

Can you build a bar chart that automatically combines small contributions?

Donna ColesLeave a comment

Yes you can! By following this guide 🙂

If you attended / watched Ann & Laura’s Speed Tip Session at TC this year, then you’ll have seen how to complete the core part of this challenge. I watched the session after I’d completed it, and found I’d approached it slightly differently from Ann. I’ll walk through what I did.

So the core requirement of this challenge was to

  1. Identify % of Sales per State
  2. Group those with a value less than a threshold defined by the user, into an ‘Other’ pot, and colour the ‘Other’ pot differently.
  3. Sort the display based on % Sales descending, except the ‘Other’ pot should always be at the bottom
  4. Label each bar with State/’Other’ pot and % sales, positioning the label above the bar, and colour accordingly
  5. Add tooltips for each bar, coloured accordingly
  6. Incorporate a summary of the number of states in and out of the group within the chart title (since it all needed to be created on a single sheet).

As I often do with these challenges, I played around with Ann’s published solution; changing the parameter and seeing how the viz altered, and hovering my mouse over various points, to see if it would give me a clue as to how it had been built – in this case I was mainly interested in the labels.

What I found from doing this was there was another mark at the 0 point of the axis; it looked like a gantt bar mark. This suggested dual axis would be involved.

So armed with that information, let’s crack on…

Identify % Sales Per State

Total Sales

{FIXED:SUM([Sales])}

This simply stores the sum of all the sales against each row in the data source

% Sales Per State

{FIXED [State]:SUM([Sales]) / SUM([Total Sales])}

The sum of the sales per State is determined as a proportion of all sales

Group those with a value less than a threshold

A [Threshold Percent] parameter was created to store the user defined variable (defaulted to 3%), and then this was used to determine how the State should be grouped for display

State Grouping

IF [% Sales Per State] >= [Threshold Percent] THEN [State]
ELSE ‘All Other States’
END

Sort the display based on % Sales descending

Whilst the States needed to be sorted with the largest % first, the ‘Other’ pot always had to display at the end. To do this I created a new calculated field

State Order

IF [State Grouping] = ‘All Other States’ THEN 1 ELSE 0 END

With the basic components now defined, we can start to build the chart

The [State Order] field placed at the front, meant that the States are listed first, and forces ‘All Other States’ to the bottom.

Setting the Sort on the [State Grouping] field to be sorted based on the [% Sales Per State] desc, gives the required result:

Colouring based on [State Order] gives the split required too.

Label the Bar

As stated above, a dual axis is needed for this, utilising our old friend, MIN(0) on the columns. The Mark Type of MIN(0) is set to Gantt Bar, with the [State Grouping] and [% Sales Per State] added to the Label, with the font adjusted to Match Mark Colour, and aligned top right. The Colour of the Gantt Bar mark is also set to 0 transparency so it looks invisible.

To make the label appear when the axis is ‘dualled’ and synchronised, requires a bit of ‘jiggery pokery’ with the row height and bar size. I found this to be very much trial and error, even publishing a couple of times, as while it looked overlapped on my laptop screen, the view did render ok on public.

Note – what I found with this solution is that changing the parameter could make the labels overlap again, which wasn’t ideal. Ann’s solution also suffered from this. I’ve been trying to figure out an alternative, but haven’t got there yet.

Tooltips

The tooltips on the bars look straightfoward, but the State ‘label’ is coloured based on the bars, so requires a couple of calculated fields

Label : State

IF [State Order]=0 THEN [State] END

Label : Other States

IF [State Order] = 1 THEN ‘All Other States’ END

Both these fields are added to the Tooltip side by side and coloured accordingly. As only one will ever be true per row, only one will display :

Count of States for chart title

To count the states based on which group they were in, required a couple of table calcs :

Count States Listed

WINDOW_SUM(IF ATTR([State Order]) = 0 THEN COUNTD([State]) END)

Count States Grouped

WINDOW_SUM(IF ATTR([State Order]) = 1 THEN COUNTD([State]) END)

These were added to the Detail shelf, so could then be referenced in the title of the Viz. The table calc needed to be applied per State Grouping

Then it was just a case of hiding various pills, and formatting to remove row lines and gridlines. A reference line for the selected parameter was also added.

My published version is here. The main difference to Ann’s solution is that she used Sets to group the States together.

Happy vizzin’

Donna