Tag: Table Calc Filter

Let’s make a Tableau Pulse-Inspired Dashboard!

Donna ColesLeave a comment

Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.

Define the parameters

Create a parameter to define the ‘reporting’ date

pBaseDate

date parameter defaulted to 21 Sept 2025

Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against

pWeeks

integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step

Building the KPI card

This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations

MtD Sales

IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END

format to $ with 0 dp.

Prev MtD Sales

IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND
[Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END

MtD Sales Diff

SUM([MtD Sales]) – SUM([Prev MtD Sales])

custom format to +”$”#,##0;-“$”#,##0

MtD Sales % Diff

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

custom format to +0.0%;-0.0%;0%

On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.

Building the Line Chart

Before building the viz, we’ll start by building the calculations and checking them through a tabular display.

On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.

For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.

25th Percentile

MODEL_QUANTILE(“model=gp”,0.25,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

and we also need the 75th percentile

75th Percentile

MODEL_QUANTILE(“model=gp”,0.75,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date

However we only want the calculations to be based on the last x weeks, so we want to filter the display.

Dates to Include

[Order Date]>= DATEADD(‘week’, -1 * [pWeeks], [pBaseDate]) AND [Order Date] <= [pBaseDate]

Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)

But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.

We can do this using a filter based on a table calculation

Filter: Dates for Chart

LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate])
AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]

The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.

Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).

Let’s start to build the viz :

Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.

Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.

Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.

Create new fields

Ref Line – Start of Month

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

and

Ref Line – End of Month

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

Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).

Add 2 reference lines to the Order Date axis, which reference these pills.

Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm

Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.

To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.

Sales for Base Date

WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))

format this to $ with 0 dp.

25th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)

75th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)

Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).

You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns

With this, we can now work out the ‘text’ we want to disply in the caption

Expected Range Text

IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’
ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’
ELSE ‘within’
END

Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.

Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.

Then edit the caption and remove all text and update, referencing the various fields and parameters.

Building the bar chart

On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field

Diff is +ve

[MtD Sales Diff]>=0

Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the Row Axis Ruler as a thicker grey line.

Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.

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

Happy vizzin’!

Donna

Filtering Table Calcs

Donna ColesLeave a comment

It was Sean Miller’s turn this week to set the challenge, which had an added twist – to complete only using the web authoring feature in Tableau Public, something I’d never tried before.

Most challenges use a version of Superstore Sales which I usually already have on my laptop, due to the multiple instances of Tableau Desktop installed, but this time, I had to get the data from data.world. I saved this locally then logged into Tableau Public online and started the process to build a viz online, by clicking the Create a Viz (Beta) option.

When prompted I then chose to upload from computer, the data source file I’d downloaded, and once done I was presented with the online canvas to start building

Unsure as to whether there was any auto save facility online, I decided to save the workbook pretty much immediately, although I was first prompted to navigate to the Data Source tab and create an Extract before I could save.

Now I was ready to start building out the requirements.

I started with the trend chart. The grain of the chart was at the month level, so I first created a specific field to store the Order Date at this particular level

Order Date Month

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

I then added this to the Columns shelf as a continuous (green) exact date, and added Sales to the Rows shelf. I changed the mark type to Area, set the Colour to a blue, dropping the opacity to 50% in an attempt to match Sean’s colouring and adjusted the tooltip.

I then added another instance of Sales to the Rows shelf next to the existing one, changing the mark type of this instance to Line and setting the Colour to a darker blue at 100% opacity. I then used the context menu on this pill to set a Quick Table Calculation of type Moving Average.

By default this sets the average to the 3-month rolling average as per the requirement. I adjusted the tooltip, made the chart dual axis and synchronised the axis, then removed Measure Names from the Colour shelf that had automatically been added.

My next step at this point was to work on the filtering requirement. To help with this, the first thing I did was to Duplicate as crosstab (right click on the chart tab). This created a tabular view of the data so I could work on getting the calculations I need for the filtering. I flipped the columns and rows, so my starting point was as below.

I chose to use parameters to capture the min and max dates that the user selects on the dashboard.

pMinDate

Date parameter defaulted to 01 Jan 1900

And I also created pMaxDate exactly the same way.

I then needed fields to store the relevant dates depending on whether a selection had been made or not

Min Date Selected

IF [pMinDate]=#1900-01-01# THEN {FIXED : MIN([Order Date Month])}
ELSE [pMinDate]
END

The FIXED statement sets the date to the minimum month in the data set, if the parameter is set to its default. The below doed similar to get the maximum date in the data set.

Max Date Selected

IF [pMaxDate]=#1900-01-01# THEN {FIXED : MAX([Order Date Month])}
ELSE [pMaxDate]
END

Using these dates, I then created a field to determine whether the month was within the min & max dates

Is Month Selected?

[Order Date Month]>=[Min Date Selected] AND
[Order Date Month]<= [Max Date Selected]

Pop this field onto the table view, and show the parameters and set them to some other values eg Min Date = 01 Jun 2018 and Max Date = 01 Sept 2018. You’ll also need to adjust the table calc setting of the Moving Average Sales pill to compute by Is Month Selected as well.

You’ll see True is stamped against the rows which match the date range. Now, if you make a note of the Moving Average values against these rows, and then add Is Month Selected? = True to the Filter shelf, you should get just the True rows displaying, BUT the Moving Average values are now different. This is because, with this type of field as a filter, the filter is being applied BEFORE the table calculation is being applied – the table calculation essentially has ‘no knowledge’ of the rest of the rows of data as they have been filtered out. Solving this issue is the crux of this challenge.

Remove Is Month Selected? from the filter shelf. We’re going to create a new field to filter by instead

FILTER

LOOKUP(MIN([Is Month Selected?]),0)

This field uses the LOOKUP table calculation, and is basically looking at the value of the Is Month Selected? field in it’s ‘own row’ (denoted by the 0 parameter).

Pop this onto the table view, and you can see that it’s essentially a duplicate of the Is Month Selected? field.

Now add the FILTER =True field to the Filter shelf instead, and this time the rows will once again filter as required, but the Moving Average values will be the same as those in the unfiltered list, which is what we need.

This works because the field we’re filtering by is a table calculation. When added to the filter shelf, these types of fields are applied at the ‘end’ of the processing; ie the table calculation is computed against all the rows in the data AND THEN the filter gets applied. As a result the Moving Average values are retained.

This is all part of Tableau’s Order Of Operations and is a fundamental concept when working with filters.

Armed with this knowledge, we can now apply the FILTER=True to the Filter shelf of the chart we built.

Now we can start to work on the bar charts, which will show Sales along with Average Monthly Sales. For this 2nd measure, I need to work out the number of months captured between the min & max dates.

No. Months

DATEDIFF(‘month’, [Min Date Selected],[Max Date Selected])+1

And with this I can now create

Avg Monthly Sales

SUM([Sales])/MIN([No. Months])

The bar chart viz can then be quickly created by adding Sub-Category to Rows and both Sales and Avg Monthly Sales to Columns, and sorting by Sales descending. The colours of each bar are adjusted to suit.

The bars also need to react to the dates selected, but we can simply add the Is Month Selected? = True to the Filter shelf for this, as none of the values displayed on this chart are reliant on table calculations.

To remove the gridlines from both charts, select Format > Workbook from the menu, scroll down the Format dialog displayed on the right and set Gridlines to Off

Finally, title both sheets appropriately, and then add to a dashboard. We need to add 3 actions to this dashboard – 2 parameter actions and 1 filter action.

Create a parameter action which will on Select, set the pMinDate parameter by passing the Minimum Order Date Month, and will reset back to 01 Jan 1900 when unselected.

Then create another instance, which sets the pMaxDate parameter instead, passing the Maximum Order Date Month.

Finally, create a Filter Action which on Select of the bar chart, filters the trend chart

And with that, the challenge should be complete. My published viz is here.

Happy vizzin’! Stay Safe!

Donna