Tag: Gantt

Can you break down YoY Profit into its drivers?

Donna ColesLeave a comment

It was Yoshi’s turn to set the challenge this week. The requirement was to build a waterfall chart, and I have to confess I did end up having to have a sneak peak at Yoshi’s solution to point me in the right direction.

I tend to always be looking for generic solutions, and in this case trying to make use of Measure Names / Measure Values, but struggled to do this. When I peaked at the solution, I found there was an element of ‘hardcoding’ being applied for the specific layout. Armed with that knowledge, I was then able to build a solution which ended up differing from Yoshi’s, but (looks to) produces the same outcome.

Defining the reporting period

The viz is driven by a Base Date input control that allows the user to select a date. Based on the date selected, the viz then displays information for the whole of the previous month, and compares that to the same month in the previous year. This means if the user selects any date from 01 Aug 2025 to 31 Aug 2025, the viz shows the information related to the whole of July 2025 and compares it to July 2024.

We will use a parameter to capture the date inputted by the user, but rather than ‘hardcode’ the data to use, I’m going to set it based on a field in the data set that I’ll create

Date Default

IIF(YEAR(TODAY())>2025, #2026-01-01#, TODAY())

The data set we’re using goes up to 31 Dec 2025. To ensure the viz still shows data if it’s accessed in 2026 or beyond, I’m going to set the date to 01 Jan 2026 if we’re looking at the viz in 2026 or later, otherwise I’ll default to whatever ‘today’ may be. This means that from 01 Jan 2026 onwards, the viz by default will always show the data for December 2025 compared to December 2024.

With this field, I can then create the parameter

pDate

Date parameter that references the Date Default field when the workbook is first opened

And with the date captured by the user, we can then determine the date of the previous month we’ll be reporting over

Date – Last Month

DATE(DATEADD(‘month’, -1, DATETRUNC(‘month’, [pDate])))

This truncates the pDate value to the 1st day of that month, then subtracts a month to return the 1st day of the previous month. Eg if pDate is 20 Aug 2025, it is first truncated to 1st Aug 2025, then 1 month is subtracted to return 1st July 2025.

We’ll then refer to this field when determining all the measures we need to build.

Calculating the measures

There are multiple measures we need to determine to build this viz – information for the previous month, the previous month last year and then the difference between the two. So what follows is just a list of all these 🙂

Sales – Last Month

IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Sales], NULL)

format this to $ with 0 dp.

Sales – Last Month LY

IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Sales], NULL)

format this to $ with 0 dp.

Sales – Last Month YoY

(SUM([Sales – Last Month]) – SUM([Sales – Last Month LY])) / SUM([Sales – Last Month LY])

custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%

Profit – Last Month

IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Profit], NULL)

format this to $ with 0 dp.

Profit – Last Month LY

IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Profit], NULL)

format this to $ with 0 dp.

Profit – Last Month YoY

(SUM([Profit – Last Month]) – SUM([Profit – Last Month LY])) / SUM([Profit – Last Month LY])

custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%

Profit Margin – Last Month

SUM([Profit – Last Month])/SUM([Sales – Last Month])

format to % with 1 dp

Profit Margin – Last Month LY

SUM([Profit – Last Month LY])/SUM([Sales – Last Month LY])

format to % with 1 dp

Profit Margin – Last Month YoY

[Profit Margin – Last Month] – [Profit Margin – Last Month LY]

custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%

List Price

[Sales]/(1-[Discount])

List Price – Last Month

IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [List Price], NULL)

format to $ with 0 dp

List Price – Last Month LY

IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [List Price], NULL)

List Price – Last Month YoY

SUM([List Price – Last Month]) – SUM([List Price – Last Month LY])

Discount Amount

[List Price] * [Discount]

Discount – Last Month

IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Discount Amount], NULL)

Discount – Last Month LY

IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Discount Amount], NULL)

Discount – Last Month YoY

SUM([Discount – Last Month]) – SUM([Discount – Last Month LY])

Cost

[Sales]-[Profit]

Cost – Last Month

IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Cost], NULL)

Cost – Last Month LY

IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Cost], NULL)

Cost – Last Month YoY

SUM([Cost – Last Month]) – SUM([Cost – Last Month LY])

There are additional fields we’ll need, but we’ll define these at the point we need them, as it’ll make more sense.

Building the KPIs

On a new sheet, double click into the Columns shelf and manually type MIN(0) to create a ‘fake axis’. Repeat this 2 more times, so 3 instances of MIN(0) exist and 3 marks cards exist. These are the placeholders for each of the KPIs we need to display.

On the 1st MIN(0) marks card, add Profit-Last Month and Profit – Last Month YoY to Label. Widen the row so you can see the text and change the mark type explicitly to Text. Adjust the label wording, layout and formatting as required, but don’t adjust the font colour. Instead create a new field

Colour – Profit

[Profit – Last Month YoY]>=0

and add this to the Colour shelf and adjust accordingly. Note – you will only ever get a true or false displayed and never both. You will need to adjust the date parameter to find a time period when the value is the opposite in order to set the opposite colour value.

Hide the Tooltip.

Repeat the process, adding Sales – Last Month and Sales – Last Month YoY to the 2nd MIN(0) marks card. Create

Colour – Sales

[Sales – Last Month YoY]>=0

and add to the Colour shelf.

The add Profit Margin – Last Month and Profit Margin – Last Month YoY to the 3rd MIN(0) marks card. Create

Colour – Profit Margin

[Profit Margin – Last Month YoY]>=0

and add to the Colour shelf. Hide column & row dividers, and name the sheet KPIs or similar.

Building the Waterfall

As mentioned above, this Waterfall chart involves a bit more “hardcoding” and the ‘explicit placement’ of the various measures into the viz.

We are displaying 5 different measures, each one in a ‘specific column’. We’re going to make use of the Row ID field to define what measure is displayed where and how it will be formatted.

Add Row ID to the Dimensions pane (drag above the line in the data pane, so it is above Measure Names). On a new sheet, add Row ID to Filter and filter to rows 1-5 only. Then add Row ID to Columns. Create a new field

Header

CASE [Row ID]
WHEN 1 THEN ‘Profit (LY)’
WHEN 2 THEN ‘List Price Sales YoY’
WHEN 3 THEN ‘Total Discount’
WHEN 4 THEN ‘Total Cost YoY’
WHEN 5 THEN ‘Profit (TY)’
END

add this to Columns. Set the sheet to Fit Width.

This gives the start of the structure. We now want to display the relevant measure in each column, but we need a single field to do this, and the values of the fields need to be cumulative based on the preceding values.

Display Value

CASE [Row ID]
WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])}
WHEN 2 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])}
WHEN 3 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]})
WHEN 4 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]}) – {FIXED: [Cost – Last Month YoY]}
WHEN 5 THEN {FIXED: SUM([Profit – Last Month])}
END

Here, we’re using a FIXED LoD calculation to ensure the measures we need are calculating across the whole data set and not segmented by the Row ID which we’re just using as an arbitrary placeholder.

Add this to Rows and change the mark type to gantt bar.

The size of the gantt bar is determined by the specific measures (rather than the cumulative values)

Size

(CASE [Row ID]
WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])}
WHEN 2 THEN {FIXED:([List Price – Last Month YoY])}
WHEN 3 THEN (-1*{FIXED:[Discount – Last Month YoY]})
WHEN 4 THEN -1*{FIXED: [Cost – Last Month YoY]}
WHEN 5 THEN {FIXED: SUM([Profit – Last Month])}
END) -1

Add this to Size

To label the bars create

Label

ABS([Size])

format this to $ with 0 dp. Add to Label and align centrally.

For the colouring create

Colour

IF ([Row ID]) = 1 THEN ‘Light’
ELSEIF ([Row ID]) = 5 THEN ‘Dark’
ELSEIF -1 * [Size] > 0 THEN ‘Blue’
ELSE ‘Red’
END

and add to Colour and adjust accordingly. And then create

Label Indicator

IF [Row ID] = 2 AND [Colour]= ‘Blue’ THEN ‘+’
ELSEIF (([Row ID] =3) OR ([Row ID]) = 4) THEN
IF [Colour]=’Blue’ THEN ‘-‘
ELSE ‘+’
END
END

Add to Label and adjust the font and layout of the label text accordingly.

Tidy up the formatting by

  • Adjust font style of the Header label.
  • Hide the Row ID pill (uncheck show header)
  • Hide the ‘header’ label (right click > hide field labels for columns)
  • Hide the axis title
  • Adjust the font style of the axis
  • Hide all axis lines/zero line, row & column dividers
  • Adjust the Tooltip
  • Name the sheet Waterfall or similar

Add the two sheets onto a dashboard and arrange with the parameter as required.

My published viz is here.

Happy Vizzin’!

Donna

Can you make a candle line chart?

Donna ColesLeave a comment

Sean provided this week’s #WOW2024 challenge, to produce a dual axis chart which he labelled a ‘candle line’ chart. The line displays the 2024 sales per month, while the candles represent the absolute difference from the previous year.

Setting up the calculations

Rather than ‘hardcode’ to 2024, I decided to use a calculation to get the latest year in the data set

Latest Year

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

which I formatted to a number with 0dp which did not use thousand separators

and then created

Previous Year

[Latest Year] – 1

I moved both of these up into the ‘dimensions’ section of the data pane (above the line).

To get the latest year sales I created

Latest Year Sales

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

and then created

Prev Year Sales

IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END

Both these fields I formatted to $ with 0 dp

We need to know the difference between these values, so I created

Sales Diff

SUM([Latest Year Sales]) – SUM([Prev Year Sales])

which I formatted to $ with 0 dp.

and we also need the percentage difference

% Diff

[Sales Diff] / SUM([Prev Year Sales])

which was formatted to a % with 0 dp.

We need to know if the Sales Diff is positive or negative, so create

Diff is +ve

[Sales Diff]>=0

Finally, when we hover on the tooltip we can see the values are coloured based on whether the difference is +ve or not, so we need some additional fields for the tooltip

Tooltip Sales Diff +ve

IF [Diff is +ve] THEN [Sales Diff] END

and

Tooltip Sales Diff -ve

IF NOT([Diff is +ve]) THEN [Sales Diff] END

both these fields I custom formatted to +”$”#,##0;-“$”#,##0 (essentially this is $ with 0 dp, but positive values are prefixed with an explicit + sign).

And then we also need

Tooltip Sales Diff % +ve

IF [Diff is +ve] THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END

and

Tooltip Sales Diff % -ve

IF NOT(Diff is +ve]) THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END

Now we have all the fields, we can build the viz.

Building the viz

On a new sheet, add Order Date at the discrete month level (blue pill) to Columns and add Latest Year Sales to Rows.

Then add another instance of Latest Year Sales to Rows. On the second marks cards, change the mark type to Gantt Bar and add Sales Diff to Size. Reduce the Size to make the bars that display narrower.

Add Diff is +ve to the Colour shelf, and adjust accordingly. Change the colour of the line chart on the other marks card. Make the chart dual axis and synchronise the axis.

On the All marks card, add Latest Year and the four Tooltip xxx fields we created to the tooltip shelf, then update the tooltip to reference all the relevant fields, and colour them accordingly

Add Region, Category and Segment to the Filter shelf, selecting all values.

Then finally, tidy up the sheet by removing all row/column dividers, the right hand axis (uncheck show header), and the Order Date label (right click and hide field labels for columns). Rename the left hand axis Sales.

Add to a dashboard and position as appropriate adding a title and updating the filters to be single value drop downs.

That ultimately is the core of the challenge, but Sean did suggest to use the new Google font Poppins. I’m on Windows and that font isn’t visible by default/installed, so after publishing, I then edited on Tableau Public and changed the fonts throughout via the Format > Workbook menu option and setting All fonts to Poppins.

My published viz is here.

Happy vizzin’!

Donna

Can you build a Measure Names Waterfall?

Donna Coles1 Comment

I’ve been on my holibobs, so haven’t blogged a solution for a few weeks. It’s been a bit of a struggled to get my head re-engaged to be honest, as I’m sure you can all relate to.

Anyway this week’s challenge was set by Sean, to produce a waterfall chart depicting specific measures without any pivoting.

I had a little bit of an initial struggle with this… firstly I assumed from the title of the challenge that I would need to be using Measure Names/Measure Values, and secondly, as nothing was mentioned, that I just had to use the data provided. This is how far I got…

but I couldn’t figure out how to get the sizes of the gantt bars inverted for some of the measures…

So I had a bit of a Google, and came across this video by one of our old WOW alumni, Luke Stanke. It made use of a scaffold data source which basically provide placeholders for each of the specific measures we want to display. Sean hadn’t explicitly said we’d need a scaffold, but then he hadn’t explicitly said we couldn’t use one either… so I had a quick peak at his solution, and I found he had used one.

So I went about recreating the challenge just by following Luke’s video. As a result, this blog won’t be as detailed, but I’ll detail the core information needed.

The scaffold data set

I created a simple excel sheet on 1 column called Points with values 1 to 5 listed.

This was then related to the Financials.csv data Sean provided using a relationship calculation of 1=1 as demonstrated in the video.

The calculations

4 calculations are created in the video

Label

CASE [Point]
WHEN 1 THEN ‘Gross Sales’
WHEN 2 THEN ‘Discounts’
WHEN 3 THEN ‘Net Sales’
WHEN 4 THEN ‘COGS’
WHEN 5 THEN ‘Profit’
END

Start

CASE [Point]
WHEN 1 THEN 0
WHEN 2 THEN [Gross Sales]
WHEN 3 THEN 0
WHEN 4 THEN [Gross Sales] – [Discounts]
WHEN 5 THEN 0
END

Value

CASE [Point]
WHEN 1 THEN [Gross Sales]
WHEN 2 THEN [Discounts] * -1
WHEN 3 THEN [Gross Sales] – [Discounts]
WHEN 4 THEN [Cogs] * -1
WHEN 5 THEN [Profit]
END

format this to $, millions with 2 dp.

Colour

SIGN(SUM([Value]))

convert this to discrete

Note on the date field

When I connected to the csv, I found the dates were being displayed to me in the UK format so a date in source of 06/01/2024 was reporting as 6th Jan, when it was intended to represent 1st Jun. There’s probably something I could have done with regional settings etc, but the quickest way for me to resolve as create

Date Adjust

MAKEDATE(YEAR([Date]), DAY([Date]), MONTH([Date]))

which just transposed the month & day and gave me the dates expected.

Building the Viz

Add Date to Filter, select Month-Year and select May 2024. Add Label to Columns and apply a Sort to sort by Point ascending. Add Start to Rows and change the Mark type to Gantt

Add Value to Size and Colour to Colour and adjust colours to suit

Add Value to Label and adjust font to match mark colour and increase size and style, Set the sheet to Entire View. Uncheck Show Tooltip.

Double click into the Rows shelf and type SUM([Start]) + SUM([Value]). This will create a second marks card. Change the mark type of this to line and remove all fields from the marks card shelf. Set the line type (via the Path shelf) to stepped and manually adjust colour to black.

Set the chart to dual axis and synchronise axis, then right click on the right hand axis and move marks to back.

Finally tidy the display up by hiding both axis, removing row & column dividers, hiding the Label title (right click and hide field label for columns) and formatting the Measure Name labels to a larger, darker font style.

Add fields Country, Product and Segment to the Filter shelf, then add to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Can you compare salaries to the median?

Donna ColesLeave a comment

For the first WOW challenge of 2024, I set the task of completing a viz related to HR salary data.

Creating the calculations

To start, we’ll build out all the necessary calculations required, and display them in tabular form. First create

Employee

[Forename] + ‘ ‘ + [Surname]

then add Employee ID and Employee to Rows, along with Department and Job Title. Add Pay Level as a discrete dimension (blue pill) to Rows too, then format Salary to £ with 0 dp, and add to Text.

We want to compare each employee’s salary with the median salary of all the employees in the same pay level. For this we need

Median Salary per Pay Level

{FIXED [Pay Level]: MEDIAN([Salary])}

Add this to the view too. You should see that the value is the same for those rows where the Pay Level is the same.

We need to compute the difference between these numbers (to draw a line from the median to the salary)

Difference From Median

SUM([Salary])- SUM([Median Salary per Pay Level])

and we also need to understand the salary as a proportion of the median

Salary / Median %

SUM([Salary])/SUM([Median Salary per Pay Level])

format this to % with 0 dp and add both these fields to the table

Each employee is given a scale value based on the proportion

Salary Scale

IF [Salary / Median %] > 1.2 THEN 6
ELSEIF [Salary / Median %] >1.1 AND [Salary / Median %] <=1.2 THEN 5

ELSEIF [Salary / Median %] >1 AND [Salary / Median %] <= 1.1 THEN 4

ELSEIF [Salary / Median %] > 0.9 AND [Salary / Median %] <= 1 THEN 3

ELSEIF [Salary / Median %] > 0.8 AND [Salary / Median %] <=0.9 THEN 2
ELSE 1
END

Set this to be discrete, then add to the view on Rows.

Finally we need the markers for the 80%, 90% etc of the median salary

80% Median

0.8 * [Median Salary per Pay Level]

90% Median

0.9 * [Median Salary per Pay Level]

110% Median

1.1 * [Median Salary per Pay Level]

120% Median

1.2 * [Median Salary per Pay Level]

Building the Viz

On a new sheet, add Employee ID, Employee, Department and Job Title to Rows. Add Salary to Columns and set the mark type to Circle. Set the Colour of the circle to suit (or use #64cdcc).

Add Median Salary per Pay Level, 80% Median, 90% Median, 100% Median, 120% Median to the Detail shelf. Add Pay Level, Salary Scale and Salary / Median % to Tooltip.

Format the axis (right click) and set the scale to be at £k with 0dp

This will also change the formatting of the salary on the tooltip, but we want it to be more detailed so create

Tooltip-Salary

[Salary]

and format this to £ with 0 dp. Add this to Tooltip too and adjust tooltip to match.

Add a reference line to the axis (right click axis > add reference line) for the Median Salary per Pay Level. Set it to be per cell and display a dashed line with no labels/tooltips displaying.

Add another reference line. This time set it to be a band at the cell level which ranges from the 80% Median to the 90% Median. Don’t display any labels or tooltips or lines and fill the band with the appropriate colour.

Repeat the above process, adding a reference band from 90% – 110% of the median, and a further band from 110% – 120% of the median.

Add another instance of Salary to Columns. Change the mark type of the 2nd Salary marks card to gantt and adjust the colour. Add Difference From Median to Size and decrease the size as small as possible. Double click into the Difference From Median pill on the size shelf and manually type in * -1 to the end.

Now make the chart dual axis and synchronise the axis. Right click on the top axis and move marks to back.

We want the user to be able to filter the chart, so add Department, Pay Level (select All values) and Salary Scale to the Filter shelf.

Finally, format the chart – make each row a bit wider; set the background colour of the worksheet to #f9f8f7; remove column dividers; set row dividers to a thick white line; remove all gridlines, zero lines and axis rulers. Reduce the size of the axis values and increase the width of the initial 3 columns so the text doesn’t wrap as much. Test your filters.

If all working, then add the sheet to a dashboard.

Building the Legend

To build the legend, I simply duplicated the core sheet, then filtered to a specific Employee ID and hid the Employee ID, Employee, Department and Job Title fields. I then edited the reference lines to add custom labels to label each line band, formatting the text to display to the top or bottom. I added an annotation to the Salary circle mark to label that as ‘Employee Salary’ which I then manually moved into position.

When I added this to the dashboard, I then floated a blank object over the top so the legend could not be interacted with.

My published viz is here.

Happy vizzin’!

Donna

Can you add candlesticks to bar charts?

Donna ColesLeave a comment

For the final week of global recognition month, Shunta Nakjima set this challenge inspired by one of the ‘founders’ of #WorkoutWednesday, Andy Kriebel.

Let’s get stuck in, by starting with the selector sheets.

Building the Measure Selector

The measure selector will be used to set a parameter which will store the particular measure selected, so we need

pSelectedMeasure

string parameter defaulted to the value Sales

We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.

Measure Selector Alias

CASE [Segment]
WHEN ‘Consumer’ THEN ‘Sales’
WHEN ‘Corporate’ THEN ‘Profit’
ELSE ‘Quantity’
END

Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.

Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.

We need to identify which measure has been selected, both through colour and an arrow indicator. So we need

Is Measure Selected

[Measure Selector Alias] = [pSelectedMeasure]

Add to the Colour shelf and adjust to suit.

Then create

Measure Selected Arrow

IF [Is Measure Selected] THEN ‘â–º’ END

I use this site to get the characters I need.

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.

Name the sheet Measure Selector.

Building the Year Selector

In order to not ‘hardcode’ the latest year, we need

Current Year

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

format this to be a number with 0dp and not to show the thousands separator.

From this we can create

Comparison Year

IIF(YEAR([Order Date])<>[Current Year],YEAR([Order Date]),NULL)

On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).

As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.

We’re going to need a parameter which will capture the year selected

pSelectedYear

integer parameter defaulted to 2022 with the display format set to not include the thousand separator

We need to identify which year has been selected, both through colour and an arrow indicator. So we need

Is Selected Year

[Comparison Year] = [pSelectedYear]

Add to the Colour shelf and adjust to suit.

Then create

Year Selected Arrow

IF [Is Selected Year] THEN ‘â–º’ END

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.

Building the Current Year ‘card’

Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.

Building the bar chart

Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need

Measure to Display – Curr Year

IF YEAR([Order Date]) = [Current Year] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

and

Measure to Display – Comp Year

IF YEAR([Order Date]) = [pSelectedYear] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.

Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters

So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.

Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.

Difference

SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])

custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.

% Difference

IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0)
OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN
[Difference]/ABS(SUM([Measure to Display – Comp Year]))
ELSE 0
END

If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to â–²0.0%;â–¼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.

Diff is +ve

[Difference]>0

Add this to the Colour shelf (remove Measure Names) and adjust accordingly.

Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.

Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.

Adding the interactivity

Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.

To select the year and the measure, I needed parameter actions

Select Year

on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the Comparison Year field

and

Select Measure

on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field

Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action

Deselect Years

On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.

Create another similar filter action for the Measure Selector sheet, and that should then be it!

My published workbook is here.

Happy vizzin!

Donna

Profit vs Target (with Tolerance)

Donna Coles1 Comment

Erica set this challenge this week, an extension of the classic actual vs target visual that is very common in business dashboards. She provided a customised data set based on Superstore Sales which included some target values.

Building the basic viz

Add Order Date to Filter shelf and restrict to the Year 2023 only. Then add Order Date to Columns and set to the discrete (blue) month level. Add Profit to Rows. Change the mark type to bar.

Add Target Profit to Rows. Change the mark type on the Target Profit marks card to Gantt Bar. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. Adjust the colour of the gantt bar to grey.

Note – it is possible to add the Target Profit as a reference line. However the width of the line will span the whole width of the ‘space’ allowed for a single month, and can’t be adjusted. Putting Target Profit on its own axis means the width of the bars can differ from the width of the gantt bar which in turn differs from the profit tolerance area we’ll add next.

Adding the tolerance bands

Create a new parameter

pTolerance

Float value defaulted to 0.05 that is displayed as % to 0 dp. Set the range from 0 to 1 with 0.01 increments.

Then create

Target Tolerance Min

SUM([Target Profit]) * (1-[pTolerance])

and

Target Tolerance Max

SUM([Target Profit]) * (1+[pTolerance])

Add both these fields to the Detail shelf on the All marks card. Right click on the Profit axis and Add Reference Line. Add a reference band per cell, which gors from the Target Tolerance Min to the Target Tolerance Max field. Set all Labels & Tooltips to None. Fill the band with a light grey.

Colouring the bars

Create a calculated field

Colour – Bar

IF SUM([Profit]) < [Target Tolerance Min] THEN ‘red’ ELSEIF SUM([Profit]) > [Target Tolerance Max] THEN ‘blue’
ELSE ‘grey’
END

Add this the Colour shelf of the Profit marks card, and adjust the colours to suit. Reduce the opacity of the colour to 85%.

Reduce the Size of the bars slightly, so they are narrower than the Gantt lines.

Finalising the chart

Create a new field

Profit Diff From Target

(SUM([Profit])-SUM([Target Profit]))/SUM([Target Profit])

format to % with 0 dp, and add this field to the Detail shelf of the All marks card.

In addition, format Profit and Profit Target to be $ with 0 dp.

Add Profit, Profit Target, Order Date as a discrete (blue) pill set to the Year level, to Detail too.

Adjust the Tooltip accordingly.

The right click on the right hand Target Profit axis and uncheck show header to hide the axis. Remove all column and row dividers. Remove the title from the Profit axis, and hide the Order Date header label (right click > hide field labels for columns).

Show the pTolerance parameter and test the functionality.

Building the legend

This is a sneaky way to build a legend using the data source available. It relies on using a field that isn’t in use, that has at least 3 dimensions. In this case I chose Region.

Add Region to Columns and exclude West. Right click on the Region field in the data pane and select Aliases. Add an alias for each of the other values to marry up to the legend names.

Manually resort the Regions on the sheet so they are listed in the correct order.

Double click into the Rows shelf and type MIN(1). Edit the axis to fix it to run from 0-1. Add Region to colour and adjust colours accordingly. Reduce opacity to 85% to.

Adjust the height and width of the display and you can see how it starts to look like the required legend.

Hide the axis and remove all gridlines. Adjust the font of the header labels. Hide the Region label heading and stop tooltips from displaying.

The final step is just the then add the two sheets onto a dashboard with the pTolerance parameter displayed too.

My published viz is here.

Happy vizzin’!

Donna

Can you recreate this difference chart?

Donna Coles2 Comments

Lorna created this challenge for #WOW2021 this week incorporating tips from the Speed Tipping session she and fellow WOW leader Ann Jackson had presented at TC21.

Defining the calculations

The requirements were to ensure there were only 7 calculated fields used, and no date hardcoding (including in the title – a feature I missed to start with). So let’s start by just going through the required calculations.

We need to identify the latest year in the data set

Current Year

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

This uses an LoD (Level of Detail) calculation to identify the maximum date in the whole data set, which is 31st Dec 2021, and then extracts the Year of this ie 2021.

From this, we work out

Previous Year

[Current Year] – 1

Both of these fields return numbers, so automatically sit in the measures section of the left hand data pane (ie under the horizontal line). I want to treat these as dimensions, so I just drag the fields above the line.

We now need to create dedicated fields to store the Sales values for both years

CY Sales

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

PY Sales

IF YEAR([Order Date])=[Previous Year] THEN [Sales] END

and with both of these, we can work out the

Difference

SUM([CY Sales])-SUM([PY Sales])

[TIP] This is custom formatted to â–³#,##0;â–½#,##0.

I googled ‘UTF 8 triangles’ and used this link to find the suitable shapes which I just copied and pasted into the number format field.

We’re going to need to determine whether the difference is positive or not.

Is Loss?

MAX(0,[Difference]) =0

This is another [TIP] making use of the array function. If the Difference is negative, it will return 0 as this is the maximum of the two numbers. I’m not entirely sure if this is more efficient than simply writing Difference<=0, but I wanted to incorporate another of the tips presented.

The final calculation we need is another of the PY Sales field, as we need another distinct Measure Name value to display. I simply chose to duplicate the existing field to have a PY Sales (copy) field.

Building the viz

Add Category to Columns, Segment to Rows and then add CY Sales to Columns, which will create a horizontal bar chart. Then drag PY Sales to the CY Sales axis, and when the ‘two columns’ icon appears, drop the field.

This will automatically change the pills so Measure Values is on Columns and Measure Names is on Rows.

Swap the order of the pills on the Measure Values section on the left hand side, so PY Sales is listed before CY Sales.

Add Measure Names to the Colour shelf and adjust. Increase the width of the rows.

Check the Show Mark Labels option on the Label shelf and adjust alignment to display the text to the left

Increase the Size of the bars to the maximum size, and add a white border (via option on Colour shelf)

Add PY Sales (Copy) to Columns, and change the mark type to Gantt Bar. Remove Measure Names from the Colour shelf of this marks card, as it will automatically have been added. Instead add the Is Loss? field to Colour and adjust.

Add Difference to the Size shelf, then click on Size, and reduce it to as small as possible. Set the border of this mark to Automatic (it should become a little thicker).

Next add the Difference field to the Label shelf, align right and set the font colour to match mark colour.

Now make the chart dual axis, synchronise axis, and set the mark type of the Measure Names mark type back to a bar.

On the All marks card, add CY Sales, PY Sales and Difference to the Tooltip shelf. And add Current Year and Previous Year to the Detail shelf.

Adjust the Tooltip against the All marks card, so it is the same when you hover on all of the marks. And edit the title of the chart, referencing the Current Year and Previous Year fields.

The challenge has a ‘space’ between each Segment, and this is the final TIP I used.

On the Measure Values section on the left below the marks card, type in MIN(NULL). This will initially create a new ‘blank’ row between the bars and the gantt marks, which isn’t where we want the blank row to be.

To resolve this, simply click on the MIN(NULL) text in the chart and drag the text below the PY Sales (copy) text

And now you just need to uncheck Show Header against the Measure Names pill on Rows, and the Measure Values and PY Sales (copy) fields on the Columns. Then remove all row and column borders and gridlines and hide labels for rows and columns.

Hopefully you’ve got the final viz which you can now add to a dashboard. My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a small multiple waterfall chart?

Donna ColesLeave a comment

For this week’s #WOW2020 challenge, Lorna Brown asked us to recreate a waterfall chart – a chart style that hasn’t featured in many previous challenges (if at all), and is always a useful one to know how to build.

I’m familiar with these, and this challenge didn’t cause me too many issues, so this blog is going to be brief.

  • Building the waterfall
  • Small multiple / grid layout
  • Adding the month label

Building the waterfall

So I’d built out the basic waterfall for each month and day by plotting Year(Order Date), Month(Order Date) and Day(Order Date) on Columns. The Day(Order Date) field was set to Show Missing Values so each day without an order was still plotted, and I was trying to figure out how to get the additional ‘long’ bar at the end.

I worked out it was essentially a ‘total’ bar and when I duplicated my data as crosstab and played round with the data in a tabular form, I got the subtotals I needed displayed.

But I seemed to be having issues displaying these on the chart view. So I turned to my usual route, Google, and had a search, and came across this blog from Tim Ryan at The Data School, which gives you the complete guide to building the waterfall, so there’s no need for me to repeat it all – thanks Tim! 🙂

My issue was I had a green continuous Day(Order Date) field rather than a blue discrete one – doh!

The only couple of things you need to make note of – you need to ensure you have 0 displayed for the missing dates

Actual Profit

ZN(SUM([Profit]))

and the gantt bars should be coloured red for negative profit, blue for positive and grey for the missing days

Colour

IF SUM([Profit])<0 THEN ‘Red’ ELSEIF SUM([Profit]) > 0 THEN ‘Blue’
ELSE ‘Grey’
END

Small multiple / grid layout

For this you need fields to add to the Rows and Columns shelf that position the month in the appropriate cell.

The Quarter(Order Date) (blue discrete) on Rows, is just used to define the row a month lands in.

You then need

Cols

IF MONTH([Order Date])%3 = 0 THEN 3
ELSE MONTH([Order Date])%3
END

which assigns each month a value of 1,2 or 3. You’ll need this on the Columns shelf.

Adding the month label

The label shows the month and the total profit in the month, so I created an LOD for this

Profit for Month

{FIXED YEAR([Order Date]),MONTH([Order Date]): SUM([Profit])}

From this I wanted the maximum value of all the monthly profits

Max Monthly Profit in Year

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

I then used a dual axis to plot this field on the Rows, set the mark type to a line and set the opacity of the line colour to 0%, so it disappears.

The month and value were then added to the Label shelf and the label set to Label start of line only and also right aligned to get the required positioning.

And that’s it. I said this would be brief 🙂 My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you visualise time in a unique way?

Donna ColesLeave a comment

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

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

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

Joining the data

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

Building the Small Multiple layout

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

Month Column

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

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

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

Determining Hour of Order

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

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

Hour of Order

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

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

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

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

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

Orders by Day chart

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

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

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

We now need to

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

Filter to latest year

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

Colour & Size by number of Orders

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

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

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

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

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

Range of Order Hours

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

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

Max Hour Per Day of Month

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

Min Hour Per Day of Month

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

Time Range Days

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

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

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

Add Trend Line

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

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

Name this sheet By Day or similar.

Orders by Weekday chart

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

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

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

Max Hour per Weekday per month

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

Min Hour per Weekday per month

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

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

Time Range Weekdays

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

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

Format the Weekday axis

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

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


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

Sheet Swapping

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

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

Then create another calculated field

Time Selected is Days

[Time Selector] = ‘Days’

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

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

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

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

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

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

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

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

My published version of the viz is here.

Happy vizzin!

Donna