For this week’s challenge, Luke asked us to recreate this KPI card on a single sheet.

We needed to display data for the last 2 years up to the latest complete month. If this was being built for a business situation, we’d make use of the TODAY() function to get a handle on the current date. Since this is being built with a static dataset which includes data up until 31st Dec 2023, I am using a parameter to ‘hardcode’ the ‘today’ date, as I want this viz to still present the relevant data on my public profile if it’s accessed in a year’s+ time.

pToday

date parameter defaulted to 12th Dec 2023

With this I can the define the data I want to include within the viz

Records to Include

[Order Date] < DATETRUNC(‘month’, [pToday]) AND [Order Date] >= DATEADD(‘year’,-2,DATETRUNC(‘month’, [pToday]))

based on pToday = 12th Dec 2023, this includes records where the Order Date is less than 01 Dec 2023 and greater or equal to 01 Dec 2021.

Add this to the Filter shelf and set to True. Then add Order Date set to the Continuous Month level (green pill) to Columns and Sales to Rows.

The add another instance of Sales to Rows and change the Mark type on the ‘Sales 2’ marks card to Area. Make the chart dual axis and synchronise the axis. Adjust the opacity of the area chart via the Colour shelf as required, and amend the Tooltip on the All marks card to display the month and sales value in the relevant format.

This has formed the basis of the sparkline. Now we need to determine the calculations we need which are displayed in the text.

The text displays information related to the month the user ‘selects’ by hovering over the sparkline. By default the information for the latest full month (in this case Nov 2023) is displayed. We need to capture this latest month in a field

Latest Month

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

We also need to capture the month the user selects via the hover into a parameter.

pSelectedMonth

date parameter defaulted to 01 Jan 1900 (think of this as a ‘null’ date)

With this we can then create

SelectedMonth

IF [pSelectedMonth] = #1900-01-01# THEN [Latest Month]
ELSE [pSelectedMonth]
END

format this to the <month year> format ie November 2023

and then also create

Selected Month Sales

{SUM(IF DATETRUNC(‘month’, [Order Date]) = [Selected Month] THEN [Sales] END)}

notice the function is wrapped in { } which makes it a Level of Detail (LOD) calculation, so the value is fixed across every row in the data set.

We also need to work out the sales from the same month in the previous year

Select Month Sales -PY

{SUM(IF DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’, -1, [Selected Month]) THEN [Sales] END)}

and subsequencty we can work out the change in sales

Change from PY

(SUM([Selected Month Sales]) – SUM([Selected Month Sales – PY ]))/SUM([Selected Month Sales – PY ])

To sense check what we’ve got, on a new sheet display the pSelectedMonth parameter then build a sheet as below

with the parameter set to 01 Nov 2023 we can see the values for Nov 2023 and Nov 2022 and captured in the relevant fields, and then the % change between the two also reflected.

But the % change is displayed on the KPI in different coloured text depending on whether the field is +ve or -ve. FOr this we need

Change from PY +ve

IF [Change from PY] >=0 THEN [Change from PY] END

and

Change from PY -ve

IF [Change from PY] <0 THEN [Change from PY] END

apply a custom number format to both fields of ↑0%;↓0% and add both fields to the sheet. Only 1 of these columns will ever be populated. If you change the parameter to 01 Aug 2022, you’ll see a negative change.

Now we have these fields, we can start to add the text element to the sparkline chart.

We’re going to plot a ‘mark’ against the first point in the x-axis, in this instance the point associated to the 1st Dec 2021. But we don’t want to ‘hardcode’ this date, so we can use

Dummy Y-Axis

IF FIRST() = 0 THEN 1 END

where FIRST() is a table calculation that is 0 for the first point on the month axis.

Add this to Rows before the Sales pills.

We have a single mark plotted for 1st Dec 2021 on a second Y-axis at position 1 on the axis. But no other marks. Change this mark type to shape and use a transparent shape (see this blog for details on how to do this).

Add Selected Month as an exact date to the Label shelf, along with Selected Month Sales, Change from PY +ve and Change from PY -ve. We also need

Previous Year

YEAR(DATEADD(‘year’,-1,[Selected Month]))

convert this to a dimension (drag to be above the line in the left hand data pane) and then add to Label too. Adjust the layout of the label as below and align top left. Note I added some spaces to the front on each line of text.

You should have something that looks similar to

To get the vertical line to display on hover, we need to create

Ref Line

IF [pSelectedMonth]<>#1900-01-01# THEN [pSelectedMonth] END

Add this to the Detail shelf of the Area chart sales marks card and set to be exact date (green pill). The right click on the date axis and Add Reference Line

Changing the pSelectedMonth parameter the line will display

Finally clean up the chart by hiding all the axis, removing all row & column dividers, gridlines, axis lines and zero lines. Hide the ‘null’ indicator.

Add the sheet to a dashboard, then create a parameter action

Select Month

on hover of the KPI card, update the pSelectedMonth parameter with the value from the Month(Order Date) field. When the selection is cleared, set the value to 01 Jan 1900.

Note – you may find that based on the size of the dashboard, you don’t get the text part to display. This is an annoyance in Desktop, that it isn’t completely WYSIWIG (what you see is what you get). I spent time adjusting font sizes etc to make the text display in Desktop, but once published to Tableau Public, it all looked too small. After setting it all back to the sizes I wanted in Desktop and re-publishing, I found it did actually display ok on Public. So you may find you just need to play around a bit to get the display as you want.

My published viz is here.

Happy vizzin’!

Donna