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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s