Can you calculate weekday run rate?

This week’s #WoW2020 challenge was set by Meera Umasankar who once again was tackling the concept of ‘run rate’, but this time with an added twist – only consider the working days (ie the typical Mon-Fri weekdays), rather than every day in the month, the assumption being, this ‘business’ does not trade on weekends. Following on from last week’s challenge, Meera also chose to include a bit of blending to combine actual orders against the plan/target. For this Meera provided a custom dataset which just included a sheet of Actuals by Region for each day in April 2020 up to 23rd April, and Plan by Region for the whole month of April.

As per usual I started by putting together a table of data with the core numbers I was going to need per region : MTD value, Run Rate value, Plan value.

Building the key data fields

Whilst Meera had provided data just for April up to April 23rd, I decided to build this in a way as if the data could change.

Today

{FIXED: MAX([Date])}

This stores the maximum date from the Actuals data source – ie 23 April 2020.

Current Month Only

[Date]>=DATETRUNC(‘month’, [Today]) AND [Date] <= [Today]

When true, this will just consider the records in the Actuals data source that are dated between 1st April & 23 April. As it happens, due to the data provided, this will be everything, but in a typical business situation, you’re actuals would probably contain previous months data too.

MTD

IF [Current Month Only] THEN [Sales] END

Only stores the sales for the month we want to report on.

To get the plan we need to blend to the Plan data source. As the data in the Actual data source is per day, and the Plan is per month, we need to blend the data at the month level. Whilst this can be set in other ways, I like to be explicit when using blending, so in my Actuals data source I created

BLEND – Date

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

This stores the 1st day of the month (1st April 2020) against every row of data.

In the Plan data I created a similar field, which is just essentially a duplicate field of the existing Date field, but by having the same name, it allows the blend joins to be automatically picked up.

Blend – Date

[Date]

Ok, let’s get these 2 measures on a table, to sense check we have the right figures so far :

  • Add Region to Rows (from the Actuals data)
  • Add MTD to Text
  • Add Plan to Text (from the Plan data)
  • Ensure the blend join links on both Region and BLEND – Date are clicked (due to the minimal data we have, the blend on Region only will work, but it’s good practice to include the date blend too if the Plan data contained different months).
  • Apply formatting as required to the MTD & Plan numbers

Calculating the Run Rate

Meera defines the Run Rate as being the value of Sales expected to be received in the whole month (the end of month position/forecast), based on the rate of sales so far in the month. So we’re looking to work out average sales made per day, then extrapolate that across the number of days in the month.

However, the twist in this challenge, is to only give consideration to the number of weekdays (ie working days).

As with many things, I chose to use my best friend ‘Google’ to see if it would throw up anything that may help this requirement, and it did, very quickly. There is an existing Tableau KB article that describes exactly how to work out the number of weekdays between 2 dates. You can find it here.

To work out the Run Rate I need

  • to work out the average Sales per weekday so far
  • multiply that by number of weekdays in the month

So I need to work out the number of working days between 1st April and 23rd April, and also the number of working days between 1st April & 30th April. I need a fair few calculated fields for all this, which I’ll build up rather than combine altogether.

Start of Current Month

DATETRUNC(‘month’,[Today])

simply truncates to 1st of month.

End of Current Month

DATEADD(‘day’, -1, DATEADD(‘month’, 1, [Start of Current Month]))

Adds 1 month onto start of month, then takes off 1 day to get the last day in the month

Following the steps in the article, I need to adjust these dates if they happen to fall on a weekend.

Start of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [Start of Current Month]) = 1
THEN DATEADD(‘day’, 1, [Start of Current Month])
ELSEIF DATEPART(‘weekday’, [Start of Current Month]) = 7
THEN DATEADD(‘day’, 2, [Start of Current Month])
ELSE [Start of Current Month]
END

If the Start of Current Month lands on a Saturday or a Sunday, the start is shifted forward to the following Monday.

End of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [End of Current Month]) = 1
THEN DATEADD(‘day’, -2, [End of Current Month])
ELSEIF DATEPART(‘weekday’, [End of Current Month]) = 7
THEN DATEADD(‘day’, -1, [End of Current Month])
ELSE [End of Current Month]
END

If the End of Current Month lands on a Saturday or Sunday, the end is shifted back to the previous Friday.

#Weekdays in Month

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)]) + 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)])))

This is working out the number of days between the adjusted start & end dates, then adding 1 to this number. It then works out the number of weeks between the adjusted start & end dates, multiples by 2 (since in every week there are 2 weekend days), and then this number is subtracted from the first.

We then need to repeat this to work out the working days from start to today.

Today (shift to weekday)

IF DATEPART(‘weekday’, [Today]) = 1
THEN DATEADD(‘day’, -2, [Today])
ELSEIF DATEPART(‘weekday’, [Today]) = 7
THEN DATEADD(‘day’, -1, [Today])
ELSE [Today]
END

This is our end date, so the date is once again shifted back to the previous Friday if it happens to be a Saturday or Sunday.

# Weekdays from start to Today

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])+ 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])))

Now we have these values, we can work out

Run Rate

(SUM([MTD])/[# Weekdays from Start to Today]) * [# Weekdays in Month]

Format this and add to check table

Building the chart

The left hand side of the chart is all text, but to present it as required, we need a fake axis.

  • From the Actuals data source, add Region to Rows
  • In the Columns shelf type in MIN(0) to create the fake axis.
  • Change the Mark Type to Text
  • Add Region, MTD, Run Rate from the Actuals data source to Text shelf
  • Add Plan from the Plan data source to the Text shelf (don’t forget to check the blend links)
  • Make each row bigger if everything all seems a bit squashed.

We’ll come back to formatting these fields later. Let’s now get the bar displayed & target displayed. This is a dual axis combining a bar and a gantt chart. Add to the chart as follows

  • Add Run Rate to Columns
  • Change the Mark Type of this measure to Bar
  • Remove all fields apart from Run Rate from the Label shelf of this card
  • Change Alignment of the Label to be left aligned
  • Add Plan to Columns (check the blend links)
  • Change Mark Type to be Gantt and remove fields from Label shelf of this card
  • Set to Dual Axis and Synchronise Axis.
  • Remove Measure Names from Colour shelf of the Bar and Gantt marks cards
  • Change Colour of the Gantt Bar (Plan) to black and add a black border to make it a bit thicker
  • Turn off Tooltips on All marks cards.

Indicating if Plan isn’t going to be met

The bar chart should be red if the Run Rate is less than Plan. The Run Rate on the Text side should also be displayed in red too if it doesn’t meet and black otherwise. We’re going to need some additional fields for this.

Run Rate < Plan

[Run Rate] < SUM([Plan (2020_04_22_WW17_Sales Projection)].[Plan])

Add this to the Colour shelf on the Bar marks card, and adjust the True/False colours accordingly

We can’t conditionally format an individual field in a Text display, so we need to create 2 further instances of the Run Rate field, where only one will ever display.

Run Rate < Plan (red)

If [Run Rate < Plan] THEN [Run Rate] END

Run Rate > Plan (black)

If NOT([Run Rate < Plan]) THEN [Run Rate] END

Format these accordingly, then add to the Text shelf of the Text marks card. Remove the original Run Rate field. You should still only have 1 run rate value displayed per row.

Now we can tidy up the display of this text. Ensure the Run Rate < Plan (red) and Run Rate > Plan (black) fields are on the same line of text with no spaces between, then colour the fonts to match the requirements

Finally, remove axis/row headers, tidy up gridlines etc, and adjust the width of the bars to suit.

Title Sheet

As the title needs to include the date, and to ensure it would be dynamic, I created a simple text sheet to display the title, and set the worksheet background to a light grey.

I then added both sheets to a dashboard, with both set to ‘Fit Entire View’, and titles hidden.

To get the Phone Layout display, I then selected the Phone option, clicked the padlock to Edit layout, and set to Fit all, and made adjustments to suit. The issue you might have though is that while things all look a bit squashed on your laptop display, it actually will render ok when published. This can unfortunately be a bit of trial & error.

My published viz is here.

Happy vizzin’ and stay safe!

Donna

Can you show the adjusted target and missing pipeline?

For #WOW2020 Week 16, Lorna set a slightly different challenge that involved data blending. Blending is a technique in Tableau used to combine data from different data sources. You can read more about it here.

Lorna’s scenario is quite a common one – you have a data source which stores some ‘actual’ data (that in a typical scenario is likely to change as you move through the year), along with a more static data source, storing plan/budget/target data for each month. This is typically created at the start of the year and rarely changes. Comparing actuals to target is a very common business requirement.

Once again, I’m going to tackle this challenge but working out all the numbers I need for each month in a tabular format, before I go onto build the viz.

Building out the data

For this challenge we have 2 data sources, the pipeline data containing multiple years and the target data just containing data for 2020. So the first this we need to do is add a filter for Closed Date from the Pipeline Data source to be the Year 2020.

The data has been specially crafted as if it’s at a particular point in time in April, in my case at the point of building it was 15 April 2020. If this was being built for a real life scenario, we’d want to be reporting based off the Today() function. To simulate this, I created a calculated field to hardcode my ‘today’ date, but if I was doing this ‘for real’, I’d have set it to TODAY().

Today

#2020-04-15#

I need to be able to report the Pipeline Data that is at Stage=Closed Won separately from data that is still in the pipeline (hasn’t been closed as won or lost). I’ll use some calculated fields for this

Closed Won

ZN(IF Stage = ‘Closed Won’ THEN [Sales] END)

Note – the ZN will display as 0 if there is no Sales.

Pipeline

IF [Stage]= ‘Negotiating’ OR [Stage] = ‘Proposing’ THEN [Sales] END

Let’s start to build the table out:

  • Month of Closed Date on Rows
  • Closed Won and Pipeline on Cols (as Measure Values)
  • Year of Closed Date = 2020 on Filter Shelf

Let’s now add in the target from the Target Data source. This will be a blend. When we blend we need to define how to ‘join’ the data sources together. I prefer to make it obvious what fields I am blending on, so although I can use existing fields and define a rule, I prefer created explicit calculated fields so it’s clear.

The Target Data contains a record for each month, dated as per the 1st of each month. In the Target Data, create a new field

BLEND – Month

[Date]

The in the Pipeline Data, create a field named exactly the same

BLEND – Month

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

but in this case we’re truncating the Closed Date to the 1st of each month, and ensuring it too is a Date rather than Datetime data type, so the fields can match.

Now add Target from the Target Data onto the table. If you get a warning message, click ok, then click the ‘link’ symbol that is currently greyed out against the Blend – Month field in the Target Data.

The ‘link’ symbol will go red and indicate that the data is being ‘joined’ on this field. The Target values in the table will now match the values if you check the data source excel file directly, and the Target pill in the Measure Values will show a ‘database’ symbol with an ‘orange tick’ which indicates it’s from a secondary data source. The data sources listed in the Data pane (top left) will also be coloured blue (primary) and orange (secondary), which indicates data blending is being used.

We now need to start working out how much off the YTD target we are so far, so we then work out how much pipeline is potentially missing from each future month.

So first up, how much has been closed won so far this year (only considering complete months). Ie how much has been won in Jan, Feb & March?

YTD Closed

WINDOW_SUM(SUM(IF [Closed Date] < DATETRUNC(‘month’, [Today]) THEN [Closed Won] END))

If the Closed Date is before the 1st of the current month (ie April in this example), get the Closed Won value already computed, but SUM all the values we have for all the months.

Add this onto the table, and you can see the total of the Closed Won values for Jan, Feb & Mar is listed against every month.

The table calc has automatically computed ‘table down’, but I’m, going to explicitly set it, as I know I’m going to move the fields around later, and I don’t want that value to change based on where it gets moved to,

Right click on the YTD Closed pill -> Edit Table Calculation and check Month of Closed Date

We need to work out how much we should have closed in the first 3 months of the year too. So in the Target Data, create a similar calculated field

YTD Target

WINDOW_SUM(SUM(IF [Date] < DATETRUNC(‘month’, [Today]) THEN Target END))

(Note – a Today calculated field also hardcoded to 15th April 2020 needs to be added to this data source too).

Add this field into the table too, and again, set the table calculation to be explicitly set against Month of Closed Date.

In order to work out how much missing pipeline to add to each month, we need to figure out how far we’re currently ‘off’, and then distribute this value across the remaining months.

I’m doing all this in steps, so I can sense check the calcs as I go. We can work out how much we’re off by creating a new field in the Pipeline Data

Missed Sales Value

[Monthly Target (2020_04_15_WW16_Sales Pipeline)].[YTD Target] – [YTD Closed]

Basically this is YTD Target YTD Closed, but when you refer to a field from the secondary data source, the field will be prefixed by the data source name.

Add this to the table, and again verify the table calc is set explicitly.

As this is a field based on other table calcs, you will see them listed as Nested Calculations, and you need to verify each one listed is set appropriately.

To work out how many months in the year are remaining that we need to distribute the above value over, we need

Remaining Months

12 – (DATEPART(‘month’, [Today]) -1)

As Today is in April, which is month 4, then the remaining months is 12 – (4-1) = 9.

An now we can work out how much needs to be added per month

Distributed Missed Sales Value

[Missed Sales Value] / [Remaining Months]

Pop this onto the table, and verify the table calc again.

Now we have this, we can work out what the Target needs to be adjusted to for each of the remaining months to make up the shortfall, which is basically adding the monthly shortfall above to the existing Target for the month (but only for the current and future months).

Adjusted Target

IF MIN([Closed Date]) >= DATETRUNC(‘month’, [Today]) THEN SUM([Monthly Target (2020_04_15_WW16_Sales Pipeline)].[Target]) + [Distributed Missed Sales Value] END

Note – we wrap Closed Date in a MIN function as we’re working with aggregated fields, so the Date needs to be aggregated too. MAX would work just the same.

Finally we need to work out what the shortfall is in the existing Pipeline to meet the Adjusted Target (if there is any).

For the months beyond the current month, this is simply the difference between the Pipeline value and the Adjusted Target (but only if the Pipeline is less than the Adjusted Target). For the current month though, it’s the difference between the Pipeline + Closed Won values and the Adjusted Target.

Missing Pipeline

IF ZN(SUM([Pipeline])) = 0 THEN NULL

ELSEIF DATETRUNC(‘month’, MIN([Closed Date])) = DATETRUNC(‘month’,[Today]) THEN
//it’s current month, so need to consider what’s closed & what’s remaining
IF (SUM([Closed Won]) + SUM([Pipeline])) < [Adjusted Target]
THEN ZN([Adjusted Target] – (SUM([Closed Won]) + SUM([Pipeline])))
END

ELSEIF SUM([Pipeline]) < [Adjusted Target] THEN ZN([Adjusted Target] – SUM([Pipeline]))

ELSE 0
END

Add this onto the table

And we’ve now got all the pieces we need to start to build the viz. Name this sheet Check Data or similar. We want this as our reference sheet to make sure our figures remain correct.

Building the Bar Chart

Firstly, duplicate the table viz, and remove the fields we don’t need in the final display (YTD Closed, YTD Target, Missed Sales Value, Distributed Missed Sales Value).

Now move the pills as follows :

  • Closed Date from Rows to Columns
  • Measure Values from Text to Rows
  • Measure Names from Rows to Colour shelf
  • Change Mark Type to Bar

Now move Adjusted Target and Target to the Detail shelf.

Adjust the colours of the remaining measures to suit, and reorder, so that the bars a stacked with Closed Won on the bottom and Missing Pipeline on the top.

Before we deal with the target lines, we’re going to sort the Tooltip out. It’s quite tricky… it might be there’s a better way, but I had to create a few custom calculated fields to get the display required.

Creating the Tooltip

For the first 3 months, the tooltip just needs to display the Closed Won value, but from April onwards, we need to display values for Closed Won, Pipeline & Missing Pipeline, even if the values are 0. Also the first 3 months just show the Target, but the remaining months need the Adjusted Target too. These values are displayed with | symbols in between along with labels, which should only show if relevant.

Firstly, we need to make sure all the measure values displayed, are accessible regardless as to which bar we hover over. So all of the 3 measures (Closed Won, Pipeline & Missing Pipeline) need to be added to the Tooltip. This is done by holding down Ctrl as you drag each pill from the Measure Values area onto the Tooltip shelf. This has the effect of duplicating the pill, and retaining any table calc settings that have been applied.

We only want the text ‘| Adjusted Target :’ to display if there is an Adjusted Target value :

Tooltip : Adjusted Target

IF [Adjusted Target] > 0 THEN ‘ | Adjusted Target : ‘
END

Add this to the Tooltip shelf.

We only want the text ‘| Pipeline :’ to display if there is a Pipeline value

Tooltip : Pipeline

IF [Pipeline] > 0 THEN ‘ | Pipeline : ‘
END

Add this to the Tooltip shelf.

And we only want the text ‘| Missing Pipeline:’ to display if we’re in the current or future months.

Tooltip : Missing Pipeline

IF DATETRUNC(‘month’, [Closed Date]) >= DATETRUNC(‘month’,[Today]) THEN ‘ | Missing Pipeline : ‘ END

Add this to the Tooltip shelf.

Now modify the Tooltip so the various pills are referenced and formatted as required

Finally adjust the Month axis, to set the months to be displayed as abbreviated values.

Adding the Target lines

At first glance, you might think the two target lines are both reference lines. However, if you hover over the tooltip of the Target (the solid line), you’ll see you have the same tooltip as the bars. Whilst there is some ability to control the tooltip of a reference line now, you can’t reference all the pills this tooltip requires.

So the Target is actually a dual axis mark. The Adjust Target however, is a reference line.

To get the Target to display, hold ctrl & drag the Target pill from the Detail shelf to the Rows shelf (to duplicate the pill), next to Measure Values.

On the Target marks card,

  • Remove Measure Names from the Colour shelf
  • Change the Mark Type to Gantt
  • Change the Colour to black, and add a black border too (to make the mark thicker)
  • Make the chart Dual Axis and Synchronise Axis
  • Uncheck Show Header on the Target axis

If you hover over the Gantt mark/Target line, you should have the same tooltip as when you hover over the bar.

The Adjusted Target is a reference line. To add this, right click on the left hand axis and Add Reference Line. Adjust settings as follows :

  • Scope – per cell
  • Value – Adjusted Target
  • Label – None
  • Tooltip – Custom, set to ‘Adjusted Target (Dashed) :’ then add Value from the selector
  • Change the Line to be black and dashed

Both target lines should now be displayed. It’s just now a case of applying some formatting to remove gridlines, row & column lines, adjust font sizes and remove axis title and column titles.

Building the legend

The dashboard displays a custom colour legend. As always there are multiple ways to do this. I chose to ‘fake it’ using aliases and some values associated to a completely different and unused dimension in the data.

Duplicate the Opportunity Name dimension. I just left it as Opportunity Name (copy). On a new sheet, add Oppotunity Name (copy) to the Filter shelf, and select 5 values only.

Then right click on Opportunity Name (copy) and select Aliases. For each of the values you selected in the filter, set an alias based on the legend names to display

Then build the legend as follows

  • Add Opportunity Name (copy) to Columns
  • Type in MIN(1) to the Columns shelf to create a fake axis
  • Add Opportunity Name (copy) to the Text shelf
  • Add Opportunity Name (copy) to the Colour shelf
  • Fix the axis of Min(1) to start at 0 and end at 1
  • Reorder the displayed values to suit.
  • Format to remove all rows/column lines and hide the headers.
  • Format the Label to be centred and size font
  • Clear the tooltip.

Note – I chose to copy the Opportunity Name pill just to make sure I didn’t inadvertently break anything, and to easily revert if things didn’t go to plan :-).

Now the 2 sheets can be placed on the dashboard along with a suitable title.

One final tip – to prevent the user from inadvertently clicking on the legend viz when on the dashboard, add a floating blank image and position over the top of the legend.

My published viz is here.

Happy vizzin’ & stay safe!

Donna

Can you create a chart with a dynamic week start?

This week Ann set the challenge and suggested she had ‘distilled it down a bit’ so it wouldn’t take as long to solve….hmmmm… not sure if that really worked out for me…. I certainly managed to crack through it to begin with, but the requirement to ‘show the data when there were missing values’ seemed to end up being trickier than I thought it would be, and the trying to display a date on the tooltip for those days…. well, that was just so freakin’ hard! Given this was ‘distilled down’, I thought I must have been missing something pretty obvious to be struggling so much. I played round with so many different table calcs to try to get this to work, but ended up admitting defeat and peaked at Ann’s solution.

The missing dates bit… that ended up being a bit odd. I rebuilt my chart multiple times, starting with a table of data as a ‘check’ and then carefully converting that into the required Viz. Eventually it just seemed to work, which left me very puzzled, as I was pretty certain what I ended up with, I’d had previously… I can only assume there is something about the order of the steps I took that may have caused the problem.

As I write this blog out, I’ll be rebuilding as I go, to generate the various screen shots, so fingers crossed, if you follow step by step, you’ll get a working solution 🙂

Right let’s get onto it…

Building out the data we need

As I’ve done before, I’m going to build up the fields I need into a tabular format, so I can ratify I’ve got everything correct before I build up the viz.

First off, we need a couple of parameters to drive the inputs for the chart :

Week Ending On

A date parameter set to 24th Oct 2019, that allows all values

Include X Prior Weeks

an integer parameter set to 10 by default

From the requirements, we need to display data for the 7 days of the week up to the Week Ending On date, along with data for each of the 7 days in the previous number of weeks as stated in the Include X Prior Weeks parameter.

So first up we need to work out when the ‘latest’ week starts

Start of Selected Week

DATEADD(‘day’,-6,[1a.Week Ending On])

Then I want to group all the Order Dates into their respective weeks, where each week needs to start on the same day of the week as the Start of Selected Week field. eg if Start of Selected Week is Friday 18th Oct, then I want all the dates of the previous x weeks to be grouped to the Friday of the week they’re in. So all the dates from Friday 11th Oct to Thurs 17th Oct are grouped as Fri 11th Oct, dates from Friday 4th Oct to Thurs 10th Oct are grouped as Fri 4th Oct, and so on.

Now the ideal way I think I should be able to do this is use a DATETRUNC function passing in the name of the day of the week, the week should start, which is based on day associated to the Start of Selected Week field. So I wanted to write something like this:

DATETRUNC(‘week’, [Order Date],DATENAME(‘weekday’,[Start of Selected Week]))

but Tableau doesn’t like the fact the 3rd parameter is a calculation rather than simply the text ‘Friday’.

So instead I had to build up the logic as follows

Order Date Week

CASE DATEPART(‘weekday’, [Start of Selected Week])
WHEN 1 THEN DATETRUNC(‘week’, [Order Date], ‘sunday’)
WHEN 2 THEN DATETRUNC(‘week’, [Order Date], ‘monday’)
WHEN 3 THEN DATETRUNC(‘week’, [Order Date], ‘tuesday’)
WHEN 4 THEN DATETRUNC(‘week’, [Order Date], ‘wednesday’)
WHEN 5 THEN DATETRUNC(‘week’, [Order Date], ‘thursday’)
WHEN 6 THEN DATETRUNC(‘week’, [Order Date], ‘friday’)
WHEN 7 THEN DATETRUNC(‘week’, [Order Date], ‘saturday’)
END

This Order Date Week is essentially the field that represents each line on the final viz.

I also need a field that I can used to represent the x-axis on the final viz, which is labelled based on the days of the week, but (as per the challenge), is starting at a different day.

To do this, I chose to ‘baseline’ all the dates in each week, to be aligned to the ‘current’ week. What I mean by this is that I want to create a field whereby if the Order Date is a Friday, the field will store the date of the Friday of the ‘latest’ week, in this case Friday 18th Oct. Any Order Date on a Saturday will be ‘aligned’ to Saturday 19th Oct and so on.

Order Date Baseline

DATE(DATEADD(‘day’, 7 * DATEDIFF(‘week’,[Order Date Week],[Start of Selected Week]), [Order Date]))

This finds the number of weeks between the week the order date is in and the start of the ‘latest’ week, which is then multiplied by 7 to convert to a days difference, which is then added onto the Order Date itself.

So from below, you can see both Fri 13th Sept & Fri 20th Sept map to 18th Oct; Tue 17th Sept and Tue 24th Sept both map to 22nd Oct etc.

I now need another field to restrict the dates being displayed to those within the timeframe dictated by the Include X Prior Weeks parameter.

Dates to Include

[Order Date]>=DATEADD(‘week’,-1 * [Include X Prior Weeks], [Start of Selected Week])
AND [Order Date] <= [Week Ending On]

So let’s start building the check table of data as follows:

  • Order Date Week on Rows as discrete, exact date
  • Order Date Baseline on Columns as discrete, exact date
  • Dates To Include on Filter shelf, set to True
  • Sales on Text

You can see we have some gaps where there are no Sales, we need these to display 0, which we do using

Inc Null Sales

IFNULL(LOOKUP(SUM([Sales]),0),0)

This is a simple table calc that is ‘looking up’ its own value, and if it’s null, sets itself to 0 otherwise uses it’s own value.

So let’s put this on the Text shelf instead

We’re also going to need a field to define the ‘latest week’, as this is coloured differently on the viz

Is Latest Week

[Order Date Week]=[Start of Selected Week]

Bring this into the table

We’ve now got the core things we need to build the majority of the chart.

Building the chart

Firstly, duplicate the table above, then move the pills round as follows:

  • Move Is Latest Week to Colour shelf, and adjust colours accordingly
  • Move Order Date Week to Detail shelf
  • Move Inc Null Sales to Columns shelf
  • Swap the Colour Legend so True is first (makes the latest line to the front)
  • Format the Order Date Baseline field to custom formatting of dddd which will just the display the day of the week rather than the full date (sneaky huh?)

Then tidy up the formatting to remove gridlines, get the axis in the right format, change the font of the axis labels etc.

And that’s the main viz… now we need to sort the tooltips.

Tooltips

On the tooltip we need to display

  • The order date
  • The sales value or ‘no sales’ if there are no sales
  • The start and end day of the week

Let’s start backwards.

Day of Week Start

DATENAME(‘weekday’,[Start of Selected Week])

Day of Week End

DATENAME(‘weekday’, [Week Ending On])

Put these on the Detail shelf (as we’re going to need them for the title too).

Add the Sales onto Tooltip and format it to $ with 0dp. This will display the Sales value when there is one, and ‘nothing’ if there are no sales (as opposed to the 0 that the Inc Null Sales field will show.

To get the ‘no sales’ text we need

Tooltip: No Sales

IF IFNULL(LOOKUP(SUM([Sales]),0),0)=0 THEN ‘no sales’ END

Note – this is what I did and is in my published solution, but I could have just checked whether Inc Null Sales = 0 – doh!

Add this onto the Tooltip and position directly adjacent to the SUM([Sales]) field, so either one will be displayed (they can never both display).

Right, now we need to get the date.

The problem we have is that like the no sales, if there’s no orders on a date there’s no date to display either, but unlike the ‘no sales’ value, we haven’t got a single default value like 0 to use. Instead we need to work out what the missing date should be.

I tried various things for this, but finally had to look at Ann’s solution to get this, which is :

Tooltip: Order Date

IFNULL(ATTR([Order Date]),
IFNULL(DATEADD(‘day’,1,LOOKUP(ATTR([Order Date]),-1)),DATEADD(‘day’,-1,LOOKUP(ATTR([Order Date]),1))))

Urgh! Horrid right!… so what is this saying…

  • If the current order date doesn’t exist, then
  • lookup the previous order date and add 1 day to it, but if that is also null then
  • lookup the next order date and take 1 day off it

So this is only working on the assumption that there are not 2 days in a row with no orders.

Add this to the Tooltip and format accordingly to get the layout required.

Finally amend the sheet title to match the title displayed. The title can reference the Day of Week Start and Day of Week End fields along with the Week Ending On and the Include X Prior Weeks parameters.

When you then add onto the dashboard, make the parameters floating and position them top right.

Hopefully, you’ve ended up with a working solution. I know I approached mine slightly differently from Ann’s, so it’s worth checking hers out too.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

How does the Sales Pipeline look?

Luke posted the challenge this week to build a Sales funnel using bar charts and table calculations.

The data provided was structured as follows

Each row identified a record in the pipeline along with its value. The last_stage field indicated what stage in the process the record was currently at. The value and last_stage are the only two bits of data needed for this challenge, along with the knowledge that the process moves through the stages in the following order :

  1. Prospect
  2. Lead
  3. Qualified
  4. Opportunity
  5. Negotiations
  6. Closed

which you can tell from the diagram anyway.

The challenge is that the value of any record currently at stage 2 (lead) or above, also needs to be incorporated into the total value of the previous stages.

When tackling these types of challenges involving table calculations, I start by creating a table view of all the data I need. So let’s get cracking…

Building up the data required

First up, I need a field to help drive the order of the stages, so I created

last_stage Order

CASE [last_stage]
WHEN ‘Prospect’ THEN 1
WHEN ‘Lead’ THEN 2
WHEN ‘Qualified’ THEN 3
WHEN ‘Opportunity’ THEN 4
WHEN ‘Negotiations’ THEN 5
ELSE 6
END

Add this to a view along with the value

This is essentially the data needed to build the Current Status column in the output.

Next we need to work out the Overall Funnel values. As Luke stated in the requirements, this is going to involve table calculations. What we want is a running sum but one that starts at the bottom at stage 6, and goes ‘up’ the table. But there is no Table Up option in Tableau, so we need to be creative.

We’ll still need a running sum though, so create this

Running Sum

RUNNING_SUM(SUM([value]))

Add this to the view, it will automatically be applied Table Down, which is what we need at this point – each row is the sum of the previous rows above it.

We also need the overall total captured against each row in the table.

Window Sum

WINDOW_SUM(SUM([value]))

Now we’re in a position to work out our ‘cumulative’ value or Overall Funnel, which is a calculation involving all 3 fields

Cumulative Value

[Window Sum]- ([Running Sum] – SUM([value]))

Note – for the values that will be displayed, I’ve applied formatting of $ to 0 dp.

Now onto the final Percent to Close calculation which is the value of records at stage closed, as a proportion of the cumulative value. So we need to get the closed value stored against every row

Closed Value

{FIXED : SUM(IF [last_stage order] = 6 THEN [value] END)}

so then we can determine

% To Close

SUM([Closed Value]) / [Cumulative Value]

which is formatted to percentage to 0 dp

So now we have all the data we need to build the viz. I like to save this sheet for future reference, and to double check as we start moving pills around to build the viz.

Building the viz

Start by duplicating the table sheet, and remove the calculations, so only the 3 necessary (Sales, Cumulative Value & % To Close) are listed.

Then move these 3 onto the Columns shelf, and in doing so, the viz should reformat as a bar chart automatically.

Set the colour of the bars to the dark green (#00646d) and tick the Show mark labels option on the Label shelf (you may need to expand the width of the rows to get the label to show.

The first 2 columns are displaying what we need, but the final one needs to show the bars ‘filling up’ to 100%. We need a dual axis for this.

Type into the Columns shelf Min(1) to create a fourth column. Uncheck show mark labels for this column only.

The set this field to be Dual axis, and synchronise the axis. It’s likely the marks will all change to circles, so reset the ‘All’ marks card back to bar. On the Min axis, right click and select Move marks to back, to ensure the % Closed values are sitting on top.

Now change the colour of the Min(1) field to light green (#7cadb2), untick Show Header against last_stage order & SUM([Value]) to hide the axis. Also right click on last_stage in the view, and select Hide field labels for rows.

Finally adjust the size of the labels displayed to 8pt. On the % To Close marks card, change the label alignment to left, and set the font colour to white. Uncheck Show Tooltips for all marks.

And you should now have the main display.

Oh, adjust the Size of the bars to suit.

When added to the dashboard, add a horizontal container above the viz, and use text boxes to display the column titles.

My published viz is here.

Happy vizzin’!

Donna

Do you want to build a simple Sales dashboard?

This week’s challenge was the first challenge to be set by guest author Meera Umasnakar, and is mainly a formatting/layout challenge.

The requirement was to show some month to date / year to date metrics in comparison to the previous month to date, and also work out where the current month might finish.

I took up the challenge on Weds 25th March, and the challenge was dated ‘as at 24th March’. There was nothing in the requirements to indicate whether this was ‘hardcoded’ to this date, or whether it happened to be this date based on the time I viewed (ie it was yesterday, the last full day). In the interest of being ‘flexible’ I therefore chose to design my solution more dynamically. I based my challenge on viewing the data up to ‘yesterday’, where ‘yesterday’ is yesterday’s date in 2019. So if today is 27 March 2020, then the dashboard will be based up to 26 March 2019. Hope that’s clear.

As a result of this, there’s a fair few date calculations involved, so let’s crack on.

Setting up the calculations

I like to build up my calculations so they’re easier to read, rather than nest everything. First up we need

Current Year

{MAX(YEAR([Order Date]))}

The maximum year in the data set (which happens to be 2019 for the data source I’m connected to).

Yesterday

MAKEDATE([Current Year],MONTH(TODAY()),DAY(TODAY()))-1

Fake the date based on today’s date (see discussion above).

Current Month

DATETRUNC(‘month’, [Yesterday])

returns yesterday’s date to be 1st of the month eg 26th March 2019 becomes 1st March 2019

Current MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Sales] END

If the order date is between the 1st of the month and yesterday, then capture the Sales value.

We can repeat for profit

Current MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Profit] END

Current YTD Sales

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Sales] END

If the order date is within the latest year, but also less than today, then capture the Sales value.

Again repeat for profit

Current YTD Profit

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Profit] END

Format all of these to $ with 1 decimal place.

To work out the numbers we need for the previous month, we need some further dates

Yesterday Previous Month

DATEADD(‘month’,-1,[Yesterday])

Just takes 1 month off of yesterday’s date.

Previous Month

DATETRUNC(‘month’, [Yesterday Previous Month])

gets back to the 1st of the previous month’s date.

Previous MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Sales] END

If the order date is within the previous month, but only up to the equivalent day of the month (previous month to date), then capture the Sales value.

Previous MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Profit] END

Format both of these to $ and 1 decimal place

MoM Sales

SUM([Current MTD Sales])-SUM([Previous MTD Sales])

Month on Month Sales, is just the difference between the two variables.

Similarly for profit

MoM Profit

SUM([Current MTD Profit])-SUM([Previous MTD Profit])

Both these need custom formatting applied

▲”$”#,##0,.0K;▼”$”#,##0,.0K

To get this, use the formatting to set to $ and 1 decimal place, then once set, change the format to ‘custom formatting’, which will display the ‘formatting code’. Then add the ▲▼ symbols. I use this site to get the characters I need.

The final calculation we need to work out is what’s been referred to as the ‘run rate’. This is basically trying to show what the final month sales/profit will be, based on the current rate. This means taking the current MTD Sales/Profit, dividing it by the number days this has been computed over to get an average sales/profit per day. Then this number is multiplied by the number of days in the month. Got it?

So we need to start by working out….

Days in Current MTD

DAY([Yesterday])

This just returns the number of the day.

Days in Current Full Month

DATEDIFF(‘day’, DATETRUNC(‘month’, [Yesterday]), DATEADD(‘month’,1,DATETRUNC(‘month’, [Yesterday])))

This is counting the number of days between the 1st of the month (based on yesterday), and the 1st of the next month.

Run Rate Sales

(SUM([Current MTD Sales])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Run Rate Profit

(SUM([Current MTD Profit])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Format these to $ and 1 decimal place.

This gives us all of the core calculations we need to build the KPIs.

Building the KPI card

This is done on one sheet, and is simply utilising the Text mark type.

By typing in, create a pill MIN(0) on the Rows shelf, and another one right next to it. Change the mark type to Text. This gives you 2 ‘cards’ you can now use. Add the all Sales related calculated fields to the Text shelf of the first card, and the Profit fields to the second card, and just format the font/layout accordingly. Then remove the axis headers, gridlines etc etc.

Building the Sales YoY Bar Chart

To ensure we only have the dates for the last 2 years, up to the current point in time, we need some additional fields

Previous Year

[Current Year]-1

Dates To Include

YEAR([Order Date])>= [Previous Year] AND [Order Date]<= [Yesterday]

Add Dates To Include to the Filter shelf and set to True.

Now build the viz by

  • Sales on Rows
  • Order Date on Columns, set to the Month level only (discrete blue pill).
  • Mark Type = bar
  • Order Date on Colour, set to Year level. Adjust colours to suit
  • Order Date on Size, set to Year level. Adjust size to suit so the latest year is narrower.
  • Set Stack Marks = Off (Analysis -> Stack Marks). This will stop the bars for each year from sitting on top of each other.
  • Format the Order Date axis, so the Month is displayed as 1st letter only.
  • Format the Sales axis so the value is displayed to K with no decimal places.
  • Due to the above, the format of the Sales value on the tooltip is likely to change too. If this happens, duplicate the Sales field, rename it to Tooltip – Sales or similar and format to $ with 0 dp. Add this to the Tooltip shelf.
  • You’ll need to do similar to get a month field for the tooltip. Create a calculated field Tooltip-Month = DATETRUNC(‘month’,[Order Date]) and custom format this to mmm yy. Add this to the Tooltip shelf.

Repeat the same steps to build the YoY Profit chart.

Date Sheet

The final dashboard indicates the date of the report. As my dashboard is dynamic and changes based on the current date, I couldn’t hardcode this. So I built the date to display on another sheet. This means I have 1 more sheet than stated in the challenge.

I simply added Yesterday to the Text shelf and referenced it

Building the Dashboard

Here we get into a bit of container fun! As I did last week, I’ll try to just step through the order you need to place the objects on the dashboard…

  • Add a Text object to store the title.
  • Beneath it, add the Date sheet.
  • To the right of both of these, add another Text object to store the sheet information text which will be displayed top right.
  • Add a Horizontal container beneath all of the above. Set the background of this container to light grey, and Inner Padding to 10 all round
  • Add the KPI sheet into the container and remove the title. Adjust the height of the objects to suit.
  • Now add a Vertical container to the right of the KPI chart. Adjust width to suit
  • Add the Sales YoY chart to the vertical container. Set the background of this object to white, so the title background isn’t grey.
  • Add the Profit YoY sheet below the Sales one. Again set the background of this to white.
  • Remove the container on the right hand side that contains the legends.
  • Add a floating blank object to the sheet. Set the background of this to light grey, and then adjust the positioning and height and width so it’s splitting your KPI card.
  • Finally if you haven’t already, edit the title and summary text appropriately.

And that should be it! My published viz is here.

Happy vizzin’!

Donna

Can you show missing selected periods and autosize bars?

Newly wed Lorna Brown (previously known as Lorna Eden 😉 ) returned for this week’s #WOW challenge.

The requirement was to create a bar chart showing number of orders per subcategory per day/week/month over a user defined number of years. The chart is coloured based on Profit. The bars also need to change size based on what date part was being displayed Lorna already hinted they’d be parameters and set actions involved.

Building the chart

First off let’s create the parameters we need to drive the dates we need to include in the viz.

Select Period

A string parameter listing Daily, Weekly, Monthly. The trick here is to store the datepart of day, week, month as the value, while displaying the required text for selection. Default to Weekly.

Doing this means later we can refer directly to the parameter when we need some date manipulation.

Number of Years

A range integer parameter starting from 1 to 4, defaulting to 2

With these, we can define the Date field that we need to plot on our axis

Date to Plot

DATE(DATETRUNC([Select Period],[Order Date],’Sunday’))

This has the effect of truncating every order date to the 1st day of the relevant month or week or just to the day, so for example if ‘month’ is selected all the orders placed in May 2019 will be grouped together under 01 May 2019 etc.

Dates to Include

YEAR([Order Date])>={MAX(Year([Order Date]))}-([Number of Years]-1)

{MAX(Year([Order Date]))} is a shortened notation for the level of detail calculation (LoD)

{FIXED : MAX(Year([Order Date]))}

which basically returns the highest year in the data set, which in this case is 2019. If the [Number of Years] parameter is set to 1 for example, we would expect all of 2019 to display, hence we need to subtract 1 in the formula so we get all orders in 2019.

Adding this to the Filter shelf and set to true will limit the orders to the dates in the years required.

Finally we need the measure

Number of Orders

COUNTD([Order ID])

Right, with those parameters set, we can now build the basic bar chart. For now we’ll just restrict the data to a single Sub-Category directly – we’ll look to adjust this later.

  • Add Dates to Include = True to Filter
  • Add Sub-Category = Tables to Filter
  • Add Date to Plot as a continuous exact date to Rows (green pill)
  • Add Number of Orders to Columns
  • Add Profit to Colour
  • Set Mark Type to Bar
  • Adjust the Tooltip to match
  • Show the Select Period & Number of Years parameters
  • Adjust the Sheet Title to reference the Select Period parameter

Sizing the bars

You’ll see the bars all look a bit overlapped. You might be tempted to adjust the bar size by moving the slider to reduce the overlap, which may well work, but as you change the Select Period parameter you’ll find that what you’re doing is setting the bar width to set width that is the same whether you have 24 marks displayed (monthly for 2 years) or 156 marks (daily for 2 years). The requirement is for the bar to adjust in width, so it’s wider when there’s less marks.

To do this, we need a calculated field

Size

CASE [Select Period]
WHEN ‘day’ THEN 1
WHEN ‘week’ THEN 5
WHEN ‘month’ THEN 10
END

These are just arbitrary values I chose, and you can play around with the values to suit, but the key is you’re choosing a range of numbers with the smallest for ‘day’ and the largest for ‘month’.

Add this field to the Size shelf. and change it to be a Continuous Dimension ie a green pill of just Size rather than SUM([Size]).

Then click on the Size shelf and change from Manual to Fixed, and set the alignment to Centre.

Changing the Select Period parameter you’ll see the bars adjust their width from being very narrow for Daily, wider for Weekly, and wider again for Monthly.

Sub Category Selector

This is based on techniques that have cropped up in a few #WOWs this year.

We’re going to be using Set Actions for this bit, so to start we need a Set.

Right click on Sub-Category -> Create Set. Name the set Selected Sub-Category and just select a single value, Tables for now.

Now on a new sheet, add MIN(0.0) to Columns (type directly in) and Sub-Category to Rows.

  • Change the Mark Type to Shape
  • Add Sub-Category to Label
  • Add Selected Sub Category set to Shape, and adjust the shape and colour to suit
  • Create a calculated field called True and another called False, each containing the value True and False respectively – add these to the Detail shelf.
  • Edit the Axis to be Fixed to start at -0.07 to 1. This is to shift everything to the left.
  • Turn off Tooltips
  • Format to remove all column, row, zero & grid lines & axis rulers.
  • Uncheck Show Header on Sub-Category and MIN(0.0)

Building the dashboard

First up, now we’ve got a Set to store the Selected Sub-Category, remove, the Sub-Category field from the Filter shelf of the bar chart. Add the Selected Sub-Category set to the Filter shelf instead.

Now create a new dashboard sheet and add the bar chart to it.

By default, the Profit colour legend and parameters will be displayed in a vertical layout container to the right of the bar chart.

Remove the colour legend, and position the Select Period parameter above the Number of Years.

Then add the Selector sheet between the two parameters, and remove any additional legends that get added.

Add a dashboard action Deselect against the Selector sheet to stop the un-selected Sub-Categories from fading out.

Add a further dashboard action Select Sub Cat against the Selector sheet to set the value of the Selected Sub-Category set on selection. The dashboard action should be set flagged to Run on single select only, so multiple values can’t be chosen.

Change the vertical layout container to be floating, then adjust the height and set the background colour to white.

Use the Add Show/Hide Button option on the Layout container to enable the collapsible container functionality.

You’ll just need to move things around a bit, adjust the sizes to suit, but that should be pretty much it.

My published version is here.

Keep safe & Happy vizzin’!

Donna

Can you build smart ranked lists?

It was Ann’s turn this week to post the weekly #WOW challenge. There’s a fair bit going on here, so let’s get cracking.

Building the main chart

There’s essentially 3 instances of this chart. I’ll walk through the steps to create the Sales version. All the fields just need to be duplicated to build the Orders & Quantity versions.

First up we need a parameter to store the date the user selects. This needs to be a date parameter that allows all dates and is set to 8th May 2019 by default: Order Date Parameter

Based on this parameter value, we need to work out the day of the week of the parameter date, the date 12 weeks ago, and then filter all the dates to just include the dates that match the day of the week. So we need

Day of Week

UPPER(DATENAME(‘weekday’,[Order Date Parameter],’Monday’))

(the UPPER is necessary for the display Ann has stated).

Dates to Include

[Order Date]>=DATEADD(‘day’,-84,[Order Date Parameter])
AND [Order Date]<= [Order Date Parameter]

This identifies the dates in the 12 week period we’re concerned with.

I played around with ‘week’ and ‘day’, as I noticed when playing with Ann’s published solution that sometimes there were 12 dates displayed, other times there were 13, but this is just down to how the number of days in a month fall, and whether there’s actually orders on the days.

Weekdays to Include

[Day of Week] = UPPER(DATENAME(‘weekday’,[Order Date],’Monday’))

This identifies all the dates that are on the same day of the week as the Order Date Parameter.

Add both Dates to Include and Weekdays to Include to the Filters shelf and set both to True.

Add Order Date to Rows and set to be a discrete exact date. Add Sales to Text. Sort Order Date by Sales DESC

The colouring of the cells is based on 4 conditions

  • being the max value
  • being above the average value
  • being the min value
  • being below the average value

I used table calcs to work this out, giving each condition a numeric value

Colour:Sales

IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN 1
ELSEIF SUM([Sales]) = WINDOW_MIN(SUM([Sales])) THEN 4
ELSEIF SUM([Sales]) >= WINDOW_AVG(SUM([Sales])) THEN 2
ELSE 3
END

Add this to the Colour shelf and change it to be a continuous (green) pill, which will enable you to select a ‘range’ colour palette rather than a discrete one. Temperature Diverging won’t be available for selection unless the pill is green; on selection, the colours will automatically be set as per the requirement. Change the mark type to Square.

We also need to identify an above & below average split so create

Sales Header

UPPER(IF [COLOUR:Sales]<=2 THEN ‘Above
Average’
ELSE ‘Below
Average’
END)

Note the carriage return/line break, which is necessary to force the text across 2 lines.

Add this to the Rows shelf in front of Order Date, and format to rotate label

Finally we need to show a triangle indicator against the selected date.

Selected Date

IF [Order Date]=[Order Date Parameter] THEN ‘►’ ELSE ” END

I use this site to source the shapes I need.

Add this to Rows between Sales Header and Order Date

Format to remove all column & row lines, then add row banding set to the appropriate level, and a mid grey colour

Finally Hide Field Labels for Rows, format the font of the date and set the tooltip.

Now we need to set the title to include the rank of the selected date.

Selected Date Sales Rank

IF ATTR([Order Date])=[Order Date Parameter] THEN RANK_UNIQUE(SUM([Sales]))END

Add this to the Detail shelf, and the field will then be available to reference when you edit the title of the sheet

Name this sheet Sales Rank or similar.

You can now repeat the steps to build versions for Orders (COUNTD(Order ID)) and Quantities (SUM(Quantity)).

Dynamic Title

To build the title that will be displayed on the dashboard, create a new sheet, and add Order Date Parameter and Day of Week to the Text shelf. Then format the text to suit

Building the Dashboard

The ‘extra’ requirement Ann added to this challenge, was to display a ‘grey shadow’ beneath each of the rank tables. This is done using containers, setting background colours and applying padding. When building this took a bit of trial & error. Hopefully in documenting I’ll get the steps in the right order…. fingers crossed…

On a new dashboard, set the background colour to a pale grey.

Add a vertical container.

Add the Title sheet into the container, and remove the sheet title

Add a blank object into the container, beneath the Title sheet.

Add another blank object into the container, between the Title and the blank, set the background of this object to dark grey, reduce the padding to 0 and the edit the height to 2.

This will give the impression of a ‘line’ on the dashboard

Now add a horizontal container beneath the ‘line’ and the blank object at the bottom. You may need to adjust the heights of the objects

Set the outer padding of this object to 5.

Add a blank object into this horizontal container. Blank objects help when organising objects when working with containers, and will be removed later.

Add another horizontal container into this container next to the blank object. Set the background to a dark gray and set the outer padding to left 10, top 5, right 5, bottom 0.

Into this dark grey layout container add the Sales Rank sheet. Set the backgroud of this object to white, and the outer padding as left 0, top 0, right 0, bottom 4. Make sure the sales rank sheet is set to Fit Entire View.

Add another horizontal container to the right of the Sales Rank sheet, between that and the blank object. Set the background to the dark grey, and outer padding to left 5, top 5, right 5, bottom 0.

Add the Orders Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.

Add another horizontal container, this time between the Order Rank sheet and the blank object. Set the background to dark grey, and outer padding to left 5, top 5, right 10, bottom 0.

Add the Qty Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.

Now delete the blank object to the right, and delete the blank object at the bottom. Also delete the container in the right hand panel that has been automatically added and contains all the legends etc.

Set the dashboard to the required 700 x 450 size.

Select the ‘outer’ horizontal container that has all the charts in it, and Distribute Contents Evenly

You may need to adjust the widths of the columns within the ranking charts to get everything displayed in the right way.

But fingers crossed, you should have the desired display.

Calendar icon date selector

The final requirement, is to show the date selected on click of a calendar icon. This is managed using a floating container to store the Order Date Parameter, and using the Add Show/Hide Button option of the container menu.

Select Edit Button and under Item Hidden choose the calendar icon you can get off the site Ann provided a link for.

You’ll just then have to adjust the position of the container with the parameter and the button to suit.

Phew! all done. My published viz is here.

Note – I did find after publishing on Tableau Public, I had some erroneous horizontal white lines displaying across my ranking charts. I’m putting this down to an issue with rendering on Public, as I can’t see anything causing this, and it’s not visible on Desktop.

Happy vizzin’!

Donna

Can you use spatial BUFFER() functions in two ways?

Week 10 of #WOW2020 was set by guest challenger Sean Miller, who chose to demonstrate a ‘hot off the press’ feature released in v2020.1 (so having this version is a prerequisite to completing this challenge).

I was excited to see this as I don’t use maps often in my day job, and I love being able to have the opportunity to try the new stuff.

Sean provided references to two blog posts, which are a must read as they will definitely help guide you through the challenge, and explain in more detail what’s going on ‘under the bonnet’. I’m not therefore going to repeat any of this.

Sean provided 2 versions for the challenge with supporting datasets.

Intermediate challenge – Can you isolate pubs within 500m of a hotel?

For this we are provided with a set of hotels in London and a set of pubs. The requirement is to only include on the display the pubs which are within a 500m radius (ie buffer) of each hotel.

Join the data

The provided data consisted of a sheet of Pubs with a Lat & Lon field, and a sheet of Hotels with a LAT & LON field

These 2 data sets need to be Inner Joined together as

(Pubs data) MAKEPOINT([Lat],[Lon])

INTERSECTS

(Hotels data) BUFFER(MAKEPOINT([LAT],[LON]),500,’m’)

In the join clause window, you have the option to Edit Join Calculation which lets you type the calculation you need

Mapping the Hotels

Whilst the join has been made, we will need the ‘buffer’ calculation to display on the viz, so create

Buffer Hotel

BUFFER(MAKEPOINT([LAT],[LON]),500,”m”)

Then double click the Latitude (generated) and Longitude (generated) fields which will automatically display a map on screen.

Add Buffer Hotel to the Detail shelf and you’ll get the following (and the mark type will change to Map)

The circles look to be representing each hotel, but if you hover over one circle, all get selected. Add Hotel Name to Detail to allow individual selection.

Add Number of Records to the Label shelf, and format to suit.

Change the Colour of the mark to be pale orange and adjust the Opacity to suit.

Set the map background by choosing Map -> Map Layers from the menu and selecting Streets from the background style section

Mapping the Pubs

As with the hotel, we’re going to need the Pub Location spatial point to display on the viz, so create

Pub Location

MAKEPOINT([Lat],[Lon])

Duplicate/drag another instance of Latitude (generated) onto the Rows shelf.

On the second marks card, remove all the fields, and change the mark type to circle, then add Pub Location onto the Detail shelf, along with Pub Name.

You might be struggling to see the marks, but they are there – change the colour to grey, add a white border and adjust the size… found them?

The Tooltip on the pub marks, displays the distance from the hotel to the pub, so create

Distance

DISTANCE(MAKEPOINT([Lat],[Lon]), MAKEPOINT([LAT],[LON]), ‘m’)

which is the distance in metres from the Pub Location to the Hotel Location (I could have used my Pub Location field and created a Hotel Location field to put into this calculated field.

Add Distance to the Tooltip field for the pub marks, and adjust to match.

Now make dual axis

Hotel List – Viz in Tooltip

On hover over the hotel buffer circle, a full list of the pubs in range is displayed. This a managed using another sheet and the Viz in Tooltip functionality.

Create a basic table with Hotel Name, Pub Name on Rows and Distance on Text. Type in the word ‘Distance’ into the Columns to make a ‘fake’ column label.

Hide Hotel Name from displaying by unchecking Show Header on the field, then Hide Field Labels for Rows and Hide Field Labels for Columns. Format to remove the column divider

Name the sheet Pubs or similar

On the Tooltip of the hotels buffer marks, adjust the initial text required, then insert the sheet by Insert -> Sheets -> <select sheet>

This will insert text as below

At the point it says ‘<All Fields>’, delete the text, then Insert -> Hotel Name

Now, if you hover over the buffer circle on the map, the list of pubs associated to just that hotel should display.

Note – when adding the sheets into the viz in tooltip, or changing the fields to filter by, always use the insert & select options rather than just typing in, as I find it doesn’t always work otherwise….may be just me though….

Phew! That’s the intermediate challenge completed (well once you’ve tidied and added to a dashboard of course.

onto the next….

Jedi Challenge – Can you find the pubs closest to a chosen hotel?

Sean provided a separate pre-combined dataset for this, as the display needs to show all the pubs, regardless of which hotel is selected, whereas in the intermediate challenge, the spatial join meant all the pubs outside of the buffer zones were excluded.

The map itself follows very similar principles. We need a dual axis, where one axis is plotting a selected hotel with it’s buffer, and the other axis, the pub locations.

The selected hotel is ultimately going to be derived from a parameter action, but we’ll set that later. For now, let’s just create the string parameter, Selected Hotel, to store the name of the hotel, which is just set to a ‘default’ value of “The Hoxton – Shoreditch”

Additionally, the buffer radius can be changed in this challenge, so we have another parameter, Buffer Radius, this time an integer with a max value of 500, and defaulted to 500 as well.

To draw the selected hotel with buffer on the map, we first need to isolate the selected hotel’s latitude & longitude, to determine the location, and store it against every row in the dataset via a LoD calculation

Is Selected Hotel?

[Name]=[Selected Hotel]

Selected Hotel Lat

{FIXED : MIN(IIF([Is Selected Hotel?], [LAT],NULL))}

Selected Hotel Long

{FIXED : MIN(IIF([Is Selected Hotel?], [LON],NULL))}

Selected Hotel Location

MAKEPOINT([Selected Hotel Lat],[Selected Hotel Long])

Now we know the location, we can create the buffer around it

Hotel Buffer

BUFFER([Selected Hotel Location],[Buffer Radius],’m’)

The Hotel Buffer and the Selected Hotel parameter are needed to display the hotel on the map.

We then need to create the fields used to display the pubs.

Pub Name

IF [Location Type]=’Pub’ THEN [Name] END

Pub Location

IF [Location Type]=’Pub’ THEN MAKEPOINT([LAT],[LON]) END

You should now be able to create the map following the steps outlined above in the intermediate challenge. One axis will show the buffer around the selected hotel, the other will show all the pubs.

The pubs need to be sized & coloured based on the distance from the selected hotel, so we need

Distance Selected Hotel-Pub

DISTANCE([Selected Hotel Location],[Pub Location],’m’)

Add this to the Size & Colour shelf of the pubs marks card, and adjust to suit (you’ll need to reverse the colour range). Also note, there are 2 pubs named Alchemist, so add Neighbourhood to the Detail shelf too to make sure the distance calcs returns the correct values. Update the tooltip on the pubs mark too.

Finally

  • update the tooltip on the pubs mark
  • add the Selected Hotel parameter to the Label of the hotel mark and adjust font to suit
  • remove the tooltip from the hotel mark

At this point the main map is built, but Sean has added a bit extra to this challenge, a bar chart to drive the hotel selection with a sort selector to drive the ranking of the hotels; all of this is wrapped up in a collapsible container – phew!

Let’s break this down and start with the bar chart.

Hotel Selector Bar Chart

Build a bar chart as follows :

  • Name, Yelp Rating (as discrete field), Price Rating on Rows
  • Yelp # of Ratings on Columns
  • Location Type = Hotel on Filter
  • Is Selected Hotel on Colour
  • Show mark labels so Yelp # of Ratings is displayed at the end of the bars

Adjust formatting to match (remove column/row lines, set the row banding, hide headers etc)

Set the Alias of the Price Rating field, so Null displays as <blank>

Name the sheet Hotel List or similar.

On a dashboard, add the Hotel List and the Map, so we can create the parameter action (Dashboard -> Actions -> Add Action -> Set Parameter) to interact between the list and map.

Clicking a hotel in the bar chart should now change which hotel is selected in the map.

Bar Chart Sort Selector

The bar chart can be sorted based on the 3 measures displayed; Price Rating, Number of Ratings, YELP Rating. We need to build the selector to allow a choice, and then change the bar chart based on the selection. This again is parameter actions, and builds on techniques used in previous WoW challenges blogged about here and here and here.

As a result, I’ll be relatively brief about how the selector is built, as the blogs should help with this.

I used 3 instances of MIN(0.0) on the Columns, and aliased the Measure Name of these to ‘ Yelp Rating ‘, ‘ Price Rating ‘, ‘ Number of Ratings ‘ (Note the spaces either side). I also adjusted the axis of each measure to make them all appear left aligned,(this was a bit trial & error).

I also needed a parameter Selected Sort Measure defaulted to ‘ Price Rating ‘

Three calculated fields are used to set the Shape of the displayed mark for each measure

Sort – Price Rating

[Selected Sort Measure] = ‘ Price Rating ‘

Sort – Number of Ratings

[Selected Sort Measure] = ‘ Number of Ratings ‘

Sort – Yelp Rating

[Selected Sort Measure] = ‘ Yelp Rating ‘

I also added the True = False url action trick to ensure the marks all appeared ‘selected’ when only one was selected.

To invoke the sort on the bar chart itself, create a calculated field

Chart Sort

CASE [Selected Sort Measure]
WHEN ‘ Yelp Rating ‘ THEN SUM([Yelp Rating])
WHEN ‘ Price Rating ‘ THEN SUM([Price Rating Sort]) * -1
WHEN ‘ Number of Ratings ‘ THEN SUM([Yelp # of Ratings])
END

Note the Price Rating Sort field is multiple by -1 to ensure it displays from lowest to highest on the sort, whilst the other fields will display highest to lowest.

Alter the Hotel Name field on the Hotel list bar chart to sort descending by Chart Sort

Add the Sort Selector sheet to the dashboard, and add a parameter action

You should now be able to play around, selecting a sort option to change the order of the hotel list, then selecting a hotel to change the map.

Hiding the hotel list / sort selector

On the dashboard add a vertical container, then place the Sort Selector sheet and the Hotel List bar chart 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.

A Cross image will appear, select to Edit Button and change the button style to Text Button

In the Title section enter the required text for when the section is displayed (Item Shown) and then for when the section is collapsed (Item Hidden). Adjust the font too.

After hitting apply, the button section, will need resizing to get the text to display

The show/hide functionality needs to be manually selected on Desktop. When on server the interactivity will work. So to close the container, on the button menu, select Hide

and the container with the selector and the bar chart will disappear

Now it’s all just about finalising the dashboard to display all the objects in the appropriate locations. The colour/size legend and Buffer parameter are also within a container, which is floated and positioned bottom left.

Hopefully I’ve covered everything. There’s a fair bit going on in this Jedi version!

My published versions are here.

Happy vizzin’!

Donna

What is the 90-day reorder rate?

Luke set the #WOW challenge this week, stating it had a difficulty rating of 10/10, so it was with some trepidation when I sat down to tackle it. Was it going to be as difficult as Luke’s radial bar challenge from 2018 week 10….?

Looking at the viz, and reading through the requirements, it didn’t seem as bad to me – it’s a table calculations challenge, and I’m OK with those. I started using Tableau before LoDs were invented, so table calculations don’t scare too much. I’m no expert though, and it still often takes a bit of trial & error to get the settings right.

Building out the Calcs

With a lot of challenges, I often start by just trying to build a tabular view of the data to sense check I’m getting the right numbers, which I then publish onto Tableau Public along with my viz. With a table calculation challenge, building the table of data is crucial.

The 3 key pieces of existing data needed for this challenge are Customer Name, Order Date & Order ID.

To start we want to put these on the rows, but given this will initially generate a lot of rows of data, I chose to arbitrarily filter to a random set of 20 or so customers (include Noel Staavos, as he’s a slight exception, which may catch you out later).

You can see from the above, Noel has multiple orders on the same day, which we need to handle.

The first requirement states we need to order the data by total number of orders per customer, so for this we need the 1st of many calculated fields (this one isn’t a table calculation, though it could be….):

Total Orders

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

Add this as a discrete pill on the Rows (since the final viz needs to show it this way too). I’ve placed it after the Customer Name but it doesn’t have to be there at this point.

We need to sort the customers by this field, so click on Customer Name and select Sort to bring up the Sort dialog. Choose to Sort By Field, Sort Order Descending, Field Name Total Orders. Aggregation Sum

We’ve now got our data ordered in the way we need. The next step is to work out the 90-day reorder rate per customer, which we will tackle in several steps. In much of what follows, some of the calculations could well be done in one calculation, but I like to see the ‘building blocks’ to help verify the calcs are correct.

First up we need to work out how many days between each order, and to figure this out we need to compare the Order Date on each line to the Order Date of the previous line.

I want to display the date of the previous order on the same line as the current order, so create

Previous Order Date

LOOKUP(ATTR([Order Date]),-1)

This ‘looks up’ the Order Date on the previous row. If I wanted to look at the next row, the second attribute would be 1 rather than -1. Or if I wanted to look at the data in the row 2 rows before the current one, I’d use -2.

Add Previous Order Date to the Rows as a discrete field. By default it’ll probably show as ‘Null’, but this is because the table calculation is computing across the table (so is looking for a previous column containing Order Date which doesn’t exist), whereas we want to look down it (ie by Row).

Click on the triangle against the Previous Order Date pill (the triangle indicates its a table calculation), and select Edit Table Calculation

We need to change the settings so they calculate for each Customer Name. Set to Specific Dimensions, and uncheck Customer Name

Note : Its worth having a read/watch of Andy Kriebel‘s Table Calculation Overview post to help you understand table calcs better.

You can see from the above, that for Noel Staavos’ multiple order on the same day, one of the orders is comparing against an order on a different date, while the other is comparing to the other order made on the same date.

Ok, so now we have the current date & previous date on the same row, we now need to work out the number of days between the dates.

Days Since Previous Order

DATEDIFF(‘day’, [Previous Order Date],ATTR([Order Date]))

Add this to the Text shelf. Hopefully it should automatically use the same table calculation settings defined for Previous Order Date, but if the numbers look off, double check the settings. They should match.

Now we can work out if the order is within 90 days of the previous order

Reorder Within 90 Days

IF ISNULL([Previous Order Date]) THEN NULL
ELSEIF [Days Since Previous Order]<=90 THEN 1
ELSE 0 END

Note, I am purposely choosing to output 1 or 0 (ie a number) rather than true or false (a boolean), as it will make the next calculation easier.

Again add Reorder Within 90 Days to the table, and again sense check the table calculation settings if things don’t look right.

Now we have the information we need to work out the reorder rate per customer, which is the number of records where Reorder Within 90 Days is 1 as a proportion of the number of records with a Reorder Within 90 Days value of either 0 or 1, since the requirements state the first order for each customer shouldn’t be included in the metric.

90-Day Reorder Rate

ZN(WINDOW_SUM([Reorder Within 90 Days])/WINDOW_COUNT([Reorder Within 90 Days]))

Set this to be a percentage with 0 dp.

By choosing to set Reorder Within 90 Days to an integer, the WINDOW_SUM() is simply summing up the 1s & 0s in the column. WINDOW_COUNT() is just counting the number of 1s & 0s there are in the column. Wrapping with a ZN means any Customers without any reorders will report as 0% rather than NULL.

Let’s add that to the table now too. You’ll see it reports the same value down the whole table across all customers, whereas we need it to show a different value per customer (although still show the same value for all the rows of the same customer).

We need to once again adjust the table calculation settings for this field.

What you’ll notice this time though, is there are Nested Calculations within this field, so the settings need to be checked for both the Previous Order Date calculation and the 90-Day Reorder Rate calculation

Both need to be identical with Order Date and Order ID being checked.

At this point, we have enough that we could start building the main viz, but I’m going to continue building out the ‘check sheet’ with the data I need for the KPIs too.

For the Overall Reorder Rate < 90 days we need to get a count of all the reorders within 90 days across all customers, as a proportion of all reorders (ie not the 1st order for each customer).

Count Reorders < 90 days per customer

IF FIRST()=0 THEN WINDOW_SUM([Reorder Within 90 Days]) END

I’m choosing to output the value only against the 1st row for each customer. This is because I’m going to be adding up this column shortly, and if I outputted the value against each row, I’d be double-counting.

Add this field to the table, and once again verify the table calculation settings are applied to each nested calculation. If done right, you should get the correct number against the first row for each customer

Now I want a sum of this column

Total Reorders < 90 Days

IF FIRST() =0 THEN WINDOW_SUM([Count Reorders <90 days per Customer]) END

Again, I’m choosing just to show this value against the 1st row, but this time it will be the first row in the whole table, as this time the table calculation needs to considering all the customers.

Add this field to the view. It’ll probably automatically give you the right number, but it’s worth having a look at the table calculation settings.

It contains 3 nested calculations this time : Total Reorders < 90 days, Count Reorders < 90 days per Customer, Previous Order Date.

The settings for Count Reorders < 90 days per Customer and Previous Order Date should be as before, with Order Date & Order ID both checked.

The setting for Total Reorders < 90 days is different though. By default it like shows Table(down), which gives the right result, but to ensure things don’t go awry if the pill gets moved around for some reason, I like to explicitly set the computation, by changing to Specific Dimensions and selecting all 3 fields

So this gives me one of the values I need to help me work out the overall rate; I now need the other

Count All Reorders

IF FIRST()=0 THEN WINDOW_COUNT([Reorder Within 90 Days]) END

This again is a nested calculation. Previous Order Date should be set with Order Date & Order ID as usual, and Count All Reorders should be set across all 3 fields.

Now we have the numbers to work out

Overall Reorder Rate

[Total Reorders <90 days]/[Count All Reorders]

which is set to a Percentage to 0 dp.

Add this onto the table and check the calculation settings again. This time there are 4 nested calculations

  • Total Reorders < 90 days : set to all 3 fields
  • Count Reorders < 90 days per Customer : set to Order Date & Order ID
  • Previous Order Date : set to Order Date & Order ID
  • Count All Reorders : set to all 3 fields

Remember, you may have filtered the customers in your view, so you may get a different value from the solution at this point!

Right onto the final KPI – average number of reorders per customer. For this we need the total number of reorders, which we’ve already got- Count All Reorders, and number of customers.

There’s probably a simpler way of doing this, but I’m continuing down the same route I’ve been working on.

First I want a count of the customer for each customer (which is 1), then I want to sum that up.

Count Customers

IF FIRST()=0 THEN COUNTD([Customer Name]) END

then

Total Customers

IF FIRST()=0 THEN WINDOW_SUM([Count Customers]) END

When added to the table, this is again a nested calculation with Count Customers set to Order Date & Order ID, and Total Customers set to all 3.

Now we can work out

Average Reorders Per Customer

[Count All Reorders]/[Total Customers]

which is set to be a number to 1 decimal place.

Adding this to the table, and verify the nested calculations are all set properly

  • Count All Reorders : set to all 3 fields
  • Previous Order Date : set to Order Date & Order ID
  • Total Customer : set to all 3 fields
  • Count Customers : set to Order Date & Order ID

Yay! We’ve now got all the values we need to build the KPI table and the main Viz.

I would recommend naming this sheet as Check Data or similar.

Building the KPI Viz

First step, duplicate the Check Data sheet.

For the KPIs, we only need 2 of the measures we’ve created, so remove all the pills from the Measure Values section except Overall Reorder Rate and Avg Reorders per Customer.

Then remove Total Orders and Previous Order Date from the Rows.

Due to the way table calculations work, we need to keep Customer Name, Order Date & Order ID in the view, but we only need the 1st row in the whole table, as that is where the data we want has been stored.

Create a new field

First Row

FIRST()=0

Add this to the Filter shelf and select True

Hide Customer Name, Order Date & Order ID by unchecking Show Header from each pill


We need to create a fake axis to get the display we need. In the Columns type in Min(0) twice, then remove Measure Names

Expand one of the Agg(Min(0)) cards on the left, and then drag the Overall Reorder Rate pill from the Measure Values section at the bottom, so it sits directly on top of the Measure Values pill

The Avg Reorders per Customer will now be on the Label shelf of the other AGG(Min(0)) card.

Change the mark type of both cards to Text.

Uncheck Show Header against the MIN(0) pills, and format to remove all columns/row/grid & zero lines.

Now you can format the text for each mark to be the appropriate size and with the relevant wording.

Then finally turn all tooltips off, and remove the Customer Name filter if it’s still there.

Hopefully you should get the numbers that now match the solution! Rename the sheet KPIs.

Building the main Viz

Once again start by duplicating the Check Data sheet we built.

Move Order ID and Previous Order Date onto the Detail shelf.

Move 90-Day Reorder Rate onto the Rows, and set to discrete

Move Order Date to Columns and change to continuous. Remove Measure Names from Columns.

Change the mark type to shape and move the Reorder Within 90 Days pill from the Measure Values section onto the Shape shelf. Change the shapes so NULL is the triangle and 0 and 1 are both diamonds.

Add Reorder Within 90 Days to the Colour shelf too, by clicking on the one on the Shape shelf, holding down Ctrl and dragging onto Colour. This will have the affect of duplicating the pill and will retain the table calculation settings. If you can’t get this to work, then drag from the Measures pane on the right, but make sure you set the table calc settings to match.

Change the colours so Null & 0 are grey (#CAC4BE) and 1 is purple (#5557EB).

The Tooltip for each shape also needs to be coloured accordingly, based on whether the mark represents the First order, a reorder over 90 days, or a reorder under 90 days (I chose to add the logic for first). I created 3 calculated fields for this:

Tooltip: First

IF FIRST()=0 THEN ‘First Order’ END

Tooltip:Over 90

IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END

Tooltip:Witin 90

IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END

I placed all 3 of these fields on the Tooltip shelf which was then formatted as:

while all 3 fields are in the tooltip, only 1 will ever actually display a value,

Make sure the table calculation settings for all 3 tooltip fields are set with Order Date & Order ID checked.

Now we need to create the line to join the marks up.

Duplicate the Order Date pill on Columns to create 2 instances of the pill sitting side by side. Set to Dual axis, synchronise and s’move marks to the back’ so the lines are behind the shapes.

Change the mark type on the Order Date (2) card to line. The requirements state

Make sure the “line” color matches with the following mark. It must be a single color“.

Hmmmm… I don’t think that last sentence was there when I tackled the challenge… I just read the line colour ‘matches’ with the following mark, so to do this I added Previous Order Date to the Path shelf. The line graduates in colour, but from the colour of preceding mark to the colour of the following mark.

Looking at Luke’s solution now that I’ve seen this statement, he chose to use a Gantt mark type to represent the line, which meant each gantt block was coloured fully. Oh well… I didn’t quite get this then 🙂

Stop the tooltips from showing on the line, by deleting the text in the tooltip, then its just a case of re-sizing the marks, and formatting to suit. Set the background colour of the worksheet to grey.

Adding the red header on the dashboard

To get the red header displayed above the table, add, a container onto the dashboard and add the viz to sit inside it. With the container selected (identified by the blue border), set the background of the container object to red (#D81159).

Then select the viz itself (identified by the grey border), and adjust the outer padding settings, to have 0 left, bottom & right, and 4 at the top.

Hopefully this should now be pretty much everything needed to get this challenge sorted. If I’ve missed anything, please comment. My published viz is here.

It’s a pretty lengthy read, so if you’ve got this far – well done & thank you!

Happy vizzin’!

Donna

Can you create a concatenated list of values?

The challenge for this week’s #WOW was set by Sean Miller, with a focus on creating a more ‘traditional’ list style report. The challenge was related to the structure of the data, and using Tableau to take multiple rows of data for a patient, and present as a single row, with some of the information concatenated into a string of comma separated values.

Whilst there are other tools & methods that could be used to shape the data before it hits Tableau, which may be more appropriate/performant for a business use, this is after all a Tableau challenge.

I figured out what I was going to need pretty quickly – a table calculation to compare rows to build up the string. However I did struggle with the volume of data being used. It caused Tableau to keep crashing initially, so I had to ping Sean to sense check I was on the right track, and there wasn’t something I was missing. Sean confirmed I was doing the right thing, so I decided to start building against a much smaller set of data. I restricted the data by adding a data source filter, and using a wildcard filter against Member Name starts with ‘a’.

So onto the build.

Building up the concatenated string

Add Member Name and Health Check Name to Rows to get the set of rows we need to work with.

What we’re aiming for is to get a concatenated string of each Health Check Name for each Member Name, on a row.

We’re going to achieve this by building up a string that combines the Health Check Name for the current row, with the combined list from the previous row.

This requires the use of a couple of Table Calculations. The first one is Index(). This can be used to number the rows in a table. I typically create a calculated field to store this.

Index

INDEX()

Set it to be discrete rather than continuous, and add the field to the Rows.

By default, the index is basically displaying a number per row, starting from 1 to however many rows are being displayed. This is because the table calculation has been set to compute Table Down.

We actually want the calculation to restart at 1 when the Member Name changes, ie for each Member Name.

Change this by clicking the ‘carrot’ / arrow on the Index pill and set to Compute Using -> Health Check Name

Next we need to create new calculated field that will build up the string

Health Check Name List

IF [Index]=1 THEN ATTR([Health Check Name])
ELSE PREVIOUS_VALUE(ATTR([Health Check Name])) + ‘, ‘ + ATTR([Health Check Name])
END

If we’re the first row, then store the name of the current row, else get the value from the previous row and concatenate to the current value, separating with a ‘,’.

Add this field to the Text shelf, and verify the table calculation is set to Compute by Health Check Name

You can see how the list is building up for each row, so by the time you get to the last row for the Member Name you’ve got the complete list.

Restrict to 1 row per Member Name

The last row for each Member Name is ultimately what we want. To identify this, I need another table calculation

Size

SIZE()

This does exactly what it ‘says on the tin’; returns the number of rows being displayed, and like INDEX() it can be computed over different partitions in the table.

Change it to be discrete and add to Rows, once again setting it to Compute By -> Health Check Name

Size is displaying the total number of rows associated to each Member Name.

To reduce the data to 1 row per Member Name, we just need the following:

Index = Size?

Index = Size

This returns true if the values match, so add this to the Filter shelf and set to True, and hey presto! 1 row per Member Name.

You can now start adding all the other fields to the output and remove Index & Size from the rows.

You will need to ensure Health Check Name remains on the canvas though, as otherwise the table calculations will break. You can either hide it from displaying as a column, by unchecking Show Header, or move it to the Detail shelf.

To ‘label’ the concatenated string field, so it ‘looks’ like just another regular column, I type the text directly into the Columns shelf

This gives us a ‘double’ column heading, and we can simply remove one

Min # Healthchecks filter

This is achieved by adding Size to the Filter shelf, but to get a range slider, the field needs to be a green continuous pill rather than blue.

List Must Contain filter

Right click on the Health Check Name field and Create -> Parameter

The Edit Parameter dialog will open with the list of distinct values pre-populated. Add the value ‘All’ to the bottom.

This parameter will be presented on the dashboard to allow user selection. But as it stands it won’t do anything. We need another field to read in the value selected in the parameter and filter the rows.

FILTER: Health Check Names

CONTAINS([Health Check Name List],[Health Check Name Parameter]) OR [Health Check Name Parameter]=’All’

This returns True if ‘All’ is selected or the concatenated list we built, contains the value from the parameter.

Add this to the Filter shelf, and set to True.

Note throughout this challenge, all the table calculations must be set to compute by Health Check Name. This includes Nested Calculations

It’s worth double checking, and if you do alter them, recheck the filter value is selected to True and hasn’t reset itself.

And that’s the core of the tricky stuff all sorted. The additional filters need adding for Physician & Age Bracket, formatting applied and then all displayed nicely on a dashboard.

Once I’d got all this I saved my work, and then took off my data source filter, keeping my fingers crossed it didn’t all grind to a halt… it didn’t, but it wasn’t the quickest either.

My published solution is here.

Apparently someone completed the challenge with LODs… I’m off to investigate further!

Happy vizzin’!

Donna