How do you compare against the world’s best 100m runners?

Donna ColesLeave a comment

As we’re in the midst of the 33rd Summer Olympics, I set an Olympic-ish themed challenge this week which was a recreation of a Viz built by my Biztory colleague, James Newton (Twitter|X / Linked In).

Whilst fun, I knew this was likely to be a bit tricky, so I tried to provide a lot of pointers, and several hints – hopefully they helped, but obviously this guide should clarify further 🙂

Modelling the data

3 csv data sets were provided, and I advised how they needed to be joined using the physical model rather than using logical relations.

Connect to the points.csv file, then click on the context menu and select Open to ‘open’ the physical layer.

Then add the Records data to the canvas and join as a left join on Lane and Event

Then add in the Scaffold data and join to Points with a left join on the Events field.

Define the parameters

Lanes 5 and 6 will be defined via user inputs, so we need parameters to capture this information

pRunner1

string parameter defaulted to any name (eg Donna)

pRunner2

string parameter defaulted to another name (eg Lorna)

pRunner1Time

string parameter defaulted to a time (in seconds) that it may take the 1st runner to run 100m eg 17.5. Note this is a string parameter as we need to handle a value for when there is no value any numeric value like 0 isn’t going to work in this situation.

pRunner2Time

string parameter defaulted to a time (in seconds) that it may take the 2nd runner to run 100m eg 20

Defining the calculations

Let’s start by adding some data to a table to help validate the calculations we need to build.

From the Points data, add Event and Lane to Rows. Then add X Start, X End, Y Start and Y End to Rows as discrete dimensions (unaggregated blue pills).

As we can see, the X coordinates are the same for each lane, as the race starts and finishes at the same points along the x-axis for every runner. The Y coordinates differ per runner as this reflects the ‘lane’ they’re in, but the values are the same for both the start & end points.

So first off, we want to calculate the ‘distance’ of the race

Start to End Distance

[X End] – [X Start]

Make this a dimension then add to Rows as a discrete (blue) field.

The 330 ‘points’ between the X Start and the X End essentially represent the 100 metres to be ‘run’.

To enable the effect of ‘running’ the 100 metre race, the 100m is split into ‘markers’ where we will plot the position of a runner as it ‘runs’. The number of markers (50) is defined within the Scaffold data, but rather than ‘hardcode’ the value, I calculated the number via

Count Markers

{FIXED: MAX([Marker])}

Doing this, means you can adjust the number of markers in the Scaffold data if you wish. From this, we can then work out how much distance is covered per marker

Distance per Marker

[Start to End Distance]/[Count Markers]

Add this into the table as a discrete dimension.

Next we want to establish the time of the slowest runner, but first we need to get a handle on the times of the runners from the parameters.

Runner 1 Time

IIF([pRunner1Time] = ”, NULL, FLOAT([pRunner1Time]))

Runner 2 Time

IIF([pRunner2Time] = ”, NULL, FLOAT([pRunner2Time]))

As we captured the times as strings in the parameters we can now convert to a numeric float value, and capture a NULL if no data is provided.

Now we can work out the slowest time, which is the maximum of the times provided for lanes 1-4 and the times in the parameters. I’ve managed this in a single calculation

Slowest Time

{FIXED : MAX(MAX([Time], MAX(IFNULL([Runner 1 Time],0), IFNULL([Runner 2 Time],0))))}

Most of the time when we use the MAX() function we are just passing a single expression eg MAX([Sales}) to get the maximum value from the [Sales] field. But you can also use MAX() to compare the maximum values from two expressions eg MAX([Value1], [Value2]) will get all the values stored in both the [Value1] and [Value2] fields and return the overall maximum of both.

So breaking down the expression above, we’re replacing any null values for Runner 1 and 2 with 0, then returning the maximum of these two runners via the inner MAX expression ie

Max of parameters : MAX(IFNULL([Runner 1 Time],0), IFNULL([Runner 2 Time],0))

and then comparing this with the Time of the runners from lanes 1-4

(MAX([Time], <Max of parameters>)

This is all then wrapped in a FIXED Level of Detail calc as we want the value to perpetuate across every row of data. Note, the outer MAX that forms part of the FIXED calc could just as easily be a MIN or AVG.

Add this to the table as a discrete dimension. In this instance the value is 20 as the largest value comes from one of the parameters. You can set these to empty or change the timings to see how the field changes.

Now we compare each runner’s time against the slowest time. Add in Time from the Records data source to the table as a discrete dimension.

Lanes 4 & 5 are Null, so we need to get a field that stores the runner’s time for every lane

Lane Time

IFNULL([Time],
(IF [Lane] = 5 THEN [Runner 1 Time] ELSEIF [Lane] = 6 THEN [Runner 2 Time] END)
)

Use the Time field, but if null, get the relevant time associated to the Lane. Format this to a number with 2dp and add to the table as a discrete dimension.

Next we calculate the proportion of time for each Lane (runner) compared to the slowest runner

Time % of Slowest Time

[Lane Time]/[Slowest Time]

Again add to the table as a discrete dimension

Now, if we assume the slowest runner (in this case Lane 6 at 20s) covers the Distance per Marker (6.6 ‘points’) for every marker in the whole of the ’50 point’ race, then we can assume that faster runners, cover a greater distance for each marker, and that is based on the time proportion calculated above. Eg if a runner took 10s (half the time), we would expect them to cover double the distance (13.2 points) for each marker.

Distance per Marker per Lane

[Distance per Marker] / [Lane Time % of Slowest Time]

Add this to the table too as a discrete dimension. This is essentially a ‘constant’ value per runner

Now we can work out the x-coordinate for each marker. So add Marker from the Scaffold data into the table, so we now have 51 rows per lane, and then create

X to Plot

MIN(([X Start] + ([Marker] * [Distance per Marker per Lane])), [X End])

As we need the runner to stop once they reach the end, we are using the MIN([Value1],[Value2]) functionality again to just return the end position of the race. Add this field to Text.

We can see that for Lane 1, they have reached the end of the race at the 24th marker, and the X to Plot has incremented by 13.78 ‘points’ for each marker. While if you look at Lane 6, the slowest runner, X to Plot increments by 6.6 ‘points’ and reaches the end on the last marker.

We now have the core of the data we need to build the viz.

Creating the race

On a new sheet, add X to Plot as a continuous dimension (green unaggregated pill) to Columns and Y Start to Rows (also as a continuous dimension)

Change the mark type to shape and assign the custom runner shape (see here for info on how to do this). Add Lane to Colour and adjust accordinglt. Increase the Size a bit.

Add the background image (Maps > Background Images > <Datasource Name> > Add Image). Browse to the image file you should have saved and then adjust the values to reflect the size of the image (500 wide by 288 high), so X field that references X to Plot goes from 0 to 500, and the Y Field that references Y Start goes from 0 to 288.

Then fix the X to Plot axis to range from 0 to 500,and the Y Start axis from 0 to 288.

This should make the runners appear to be in each lane.

Add Marker to the Pages shelf. The page control should display starting at 0 and only 1 runner image per lane should display. Adjust the Size of each runner if need be.

Press the ‘play’ button on the control, and set the speed to max and you should see the runners move down the track.

Labelling the viz

We need to get the name of runner in each lane, which is sourced either from the parameters for lanes 5 & 6 or from the Records data, BUT we only want the label to display at the start or end of the race.

Label – Runner

IF [Marker] = 0 THEN
IF [Lane] = 5 THEN [pRunner1]
ELSEIF [Lane] = 6 THEN [pRunner2]
ELSE [Record (Records.csv1)] + ” ” + [Gender (Records.csv1)]
END
ELSEIF [Marker] = [Count Markers] THEN
IF [Lane] = 5 THEN [pRunner1]
ELSEIF [Lane] = 6 THEN [pRunner2]
ELSE [Record (Records.csv1)] + ” ” + [Gender (Records.csv1)]
END
END

We also want the runner’s Time to display on the label, but only at the end of the race

Label – Time

IF [Marker] = [Count Markers] THEN [Lane Time] END

Format this to a number with 2 dp and suffixed with ‘s’

Add both these fields to the Label shelf and arrange accordingly. Set the font to match mark colour and align label middle left.. Test the labels display as expected by setting the marker value back to 0 and playing the race through again.

Adding Tooltips

Create the field

Tooltip – Athlete

IF [Lane] = 5 THEN [pRunner1]
ELSEIF [Lane] = 6 THEN [pRunner2]
ELSE [Athlete (Records.csv1)]
END

Then add this field, Lane Time, Gender and Record to the Tooltip shelf and adjust accordingly.

Tidying up

Finally remove all gridlines, zero lines, axis rulers & ticks, row/column dividers and hide the axes.

Add the sheet to a dashboard and arrange the objects as required, using containers to help organise the parameters and controls, and setting a background colour to some objects when required.

Customise the page control object so it only shows the slider and playback controls. Unfortunately, when you publish to Tableau Public, the speed controls won’t display, so the runners won’t ‘run’ as fast on Public 😦

My published viz is here. I hope you enjoyed it, and didn’t find it too complicated!

Happy vizzin’!

Donna

Connected Scatterplots

Donna ColesLeave a comment

Sean set this challenge this week, to build a connected scatterplot to allow additional insights to be gained.

We need to show a circle per country related to the specified year, and then show the data for all years if a country is specified from the drop down, or ‘clicked on’ by the user; these data points are all then connected.

Let’s start by building up the various parameters and calculated fields needed to help with this.

Setting up the data

For the user inputs, I used parameters

pYear

Integer parameter, defaulted to 2000, displayed in a format so no thousand separators are shown. I populated the list using the values from the Year field.

pCountry

string parameter defaulted to All. I populated the list of entries by first adding values from the Country field. I then manually added an All entry to the bottom of the list and dragged it to the top. I could then set All as the default value.

On a new sheet, show these parameters.

We’re going to use a dual axis chart to display the viz, and for this, we’re going to get the relevant measures for the specific Year and for the specific Country.

To see what I’m aiming for, lets’ build out the data in a table. Add Country to Columns and Year to Rows. Display the values of Fertility Rate and Life Expectancy. This just gives us all the data points

But we only want the points related to the pYear (2000) or if pCountry if it’s not All (in this case Afghanistan).

So we create

Fertility Rate for Year

[Year] = [pYear] THEN [Fertility Rate] END

Life Expectancy for Year

IF [Year] = [pYear] THEN [Life Expectancy] END

format these to 1 dp and then add to the table. The fields only contain values for the specified pYear.

Create

Fertility Rate for Country

IF [Country] = [pCountry] THEN [Fertility Rate] END

Life Expectancy for Country

IF [Country] = [pCountry] THEN [Life Expectancy] END

format these to 1 dp and also add to the table. We now have these entries only existing for the selected pCountry.

If pCountry is set to All, the Fertility Rate for Country and Life Expectancy for Country are empty for every Country.

We now have the basics we need to build the viz.

Building the ScatterPlot

On a new sheet, show the parameters, then add Fertility Rate for Year to Columns and Life Expectancy for Year to Rows and Country to Detail. Change the mark type to circle. Adjust the Tooltip.

Create a new field

All Countries

[pCountry] = ‘All’

and add to the Colour shelf. Adjust the colours so when pCountry = All, the All Countries colour legend is True and displays a darker instance of a colour, as opposed to when pCountry is set to something else, and the All Countries colour legend is false.

Now add Fertility Rate for Country to Columns and Life Expectancy for Country to Rows. Change both fields to be Dimensions. On the 2nd marks card, add Year to Detail. and remove the All Countries field from the Colour shelf. Change the mark type to line and move Year to Path. Set the colour accordingly.

Then set both the Rows and Columns to be Dual Axis and synchronise both axis. Remove Measure Names from the colour shelf on the all marks card.

Adjust the Tooltip of the 2nd (line) marks card.

Add Year to the Label shelf of the 2nd marks card and update so it just displays for the Min & Max value. Adjust font size and style.

On the first marks card (the circle) add Country to Label and adjust so it only displays when selected.

Hide the right and top axis. Remove row & column dividers. Hide the null indicator and update the title of the axes. Name the sheet Scatterplot or similar.

Building the Dashboard

Add the sheet to a dashboard, and float the parameters into a suitable location. Add a floating text box that references the pYear parameter and position bottom left of the chart. Add a parameter action to update the pCountry parameter when a circle is clicked.

Click Country

On select of the Scatterplot Viz, set the pCountry parameter, passing in the value from the Country field. When cleared, set the parameter back to All.

Finally, if you click a circle to select a Country, you’ll find that the circles ‘fade out’ more than what you want – you want them to look the same as the colour when a country is selected via the dropdown. Essentially, you want all the circles to be ‘highlighted’ on click. To do this, create a new field

HL

“Highlight”

and add this to the Detail shelf on the scatterplot sheet. Then on the dashboard, add a Highlight action

HL marks on click

On select of the scatterplot viz, target itself, highlighting the HL selected field

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

Happy vizzin’!

Donna

Formatting & Intermediate Tableau Charts

Donna ColesLeave a comment

Erica set this challenge primarily aimed at building a beautifully presented dashboard, with the requirement to consider the use of layout containers and padding. She threw in creating some very specific chart types too. The easiest way to blog this, is by chart type.

Building the Histogram

Add Quantity to Columns as continuous dimension (green unaggregated pill) and add Order ID as a measure using the CNT aggregation to Rows. The easiest way to do this is right click and drag Order ID from the left hand date pane and drop onto rows. When you release the mouse, the option to select the aggregation should be available.

Change the mark type to bar and adjust the colour. Edit the title of the y-axis and remove the title from the x-axis. Update the Tooltip.

Double -click into Columns and manually type ‘Quantity in Order’ (including the quotes). Right click on the first text displayed and hide field labels for columns. Adjust the font of the Quantity in Order label that remains.

Remove row and column dividers and column gridlines. Remove Row axis rulers.

Note, when you add to the dashboard , you may find you want to adjust the Size of the bars.

Building the Peas in a Pod chart

On a new sheet, add Category to Filter and select Technology. Add Order Date to Filter and select Years then choose 2022,2023 and 2024.

Rename the Sub-Category field to Sub-Cat and add to Rows. Add Sales to Columns. Change the mark type to circle. Add Order Date to Colour. By default it should display YEAR(Order Date). Adjust colours to suit. Widen each row a bit.

Add another instance of Sales to Columns.

On the Sale (2) marks card change the mark type to line and move YEAR(Order Date) to Path. Increase the size and adjust the colour so it’s a grey lozenge.

Make the chart dual axis and synchronise the axis. Right click the top axis and move marks to back. Adjust the Tooltip. Edit the title of the x-axis.

Hide the top axis. Remove row and column dividers. Remove row gridlines. Remove axis rulers for both columns and rows.

Note, when you add to the dashboard , you may find you want to adjust the Size of the circles and the line. I found it was best adjusted on the web after I published to Tableau Public.

Building the +/- Bar Chart

On a new sheet add Order Date to Filter and select Years then choose 2022,2023 and 2024. Add Order Date to Columns and select to be at the continuous month level (green pill, May 2015 format). Add Sales to Rows and change the mark type to bar.

Add a quick table calculation of Difference to the Sales pill.

Adjust the size of the bars (select manual over fixed and adjust the slider).

Create a new calculated field

Diff is +ve

ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1) > 0

and add to the Colour shelf. Adjust colours to suit. Hide the null indicator. Adjust the Tooltip. Adjust the title of the x-axis.

Remove all gridlines and axis rulers. Remove the columns zero line. Set the rows zero line to be a continuous unbroken line.

Note – once again the size may need further adjusting once on the dashboard and/or after publishing.

Building the slope chart

Add Category to filter and select Office Supplies. Add Region to filter and select West. Add Order Date to filter and select Years then choose 2021 and 2024 only.

Add Order Date to Columns and Sales to Rows. Add Sub-Cat to Detail.

Add Sales to Colour then add a quick table calculation of Percentage Difference. This only sets a value against the 2024 marks though, whereas we want a value for the whole line for each Sub-Cat.

Double-click into the Sales pill on Colour to edit it, and wrap the whole calculation in a WINDOW_MAX() function – the whole calculation should look like

WINDOW_MAX((ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)))

Adjust the colour legend. I set the start & end colours to #ff00ff (hot pink) and #5d6068 (dark grey) and then applied an upper limit to the range and centred at 0 as below.

Hide the Order Date heading at the top of the chart. Adjust the Tooltip.

Remove column gridlines, zero lines and axis rulers.

Create new fields

2021 Sales

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

and

2024 Sales

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

then create

% Difference

(SUM([2024 Sales]) – SUM([2021 Sales]))/SUM([2021 Sales])

Edit the Sort of the Sub-Cat pill on the Detail shelf, so it is sorting by % Difference ascending. This will ensure the lines are displayed overlapping in the expected manner.

Building the Bar-in-Bar Chart

On a new sheet, add Category to filter and select Furniture. Add Region to filter and select West. Add Order Date to filter and select Years then choose 2023 and 2024 only.

Create a new field

2023 Sales

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

Add Sub Cat to Rows and 2023 Sales to Columns. Add a sort to the Sub-Cat pill to sort by 2024 Sales descending. Add 2024 Sales to Columns. Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to bar. Remove Measure Names from the Colour shelf on the All marks card. Set the colour of the 2023 Sales marks card to light grey. Increase the width of each row, then reduce the size of the bar on the 2024 Sales marks card.

Create a new field

Sales Decreased

SUM([2024 Sales]) < SUM([2023 Sales])

and add to the Colour shelf of the 2024 Sales marks card. Adjust colours to suit.

In the solution, the Tooltip shows an indicator – I’m not sure if this was necessary, but I added it just in case

2024 Sales > 2023 Sales

IF [Sales Decreased] THEN ‘●’ END

Add this to the Tooltip shelf of the All marks card, along with the 2023 Sales and 2024 Sales fields. Adjust the Tooltip accordingly.

Hide the top axis. Remove the title of the x-axis.

Remove row and column dividers. Remove row gridlines and row axis rulers and ticks. Remove all zero lines.

Building the side-by-side bar chart

On a new sheet, add Category to filter and select Technology. Add Region to filter and select West. Add Order Date to filter and select Years then choose 2023 and 2024 only.

Add Sub Cat to Rows and Sales to Columns. Apply a Sort to Sub-Cat based on 2024 Sales descending.

Create a new field

Year

YEAR([Order Date])

And add to Rows and Colour. Adjust colour to suit. Widen each row.

Create new field

Diff is Neg Indicator

IF NOT([Diff is +ve]) THEN ‘●’ ELSE ” END

Add to Rows before Year and then adjust the table calculation setting so it is just computing by Year only.

Adjust the alignment of the Sub-Cat column so it is aligned middle right. Narrow the width of the Diff is Neg Indicator column to try to remove all the column heading text. If some still shows, rename the field so it is padded with some spaces at the front. Adjust the Tooltip.

Remove the x-axis title. Remove Column dividers. Adjust the row dividers so they are at level 1 and are partitioning each Sub Cat only and not splitting the Year column.

Remove all gridlines

Building the dashboard

It’s always hard to walk through the steps for placing objects on a dashboard in the specified places. My general rules are

  1. Start with a floating vertical container that is positioned 0,0 and set to the dashboard height and width. I name this Base.
  2. Then add tiled objects such as a text object for the title, blank objects, other containers, charts etc.
  3. When you add a container, add a blank object initially to help get everything into place. Remove once you have at least 2 objects side by side / on top of each other depending on the direction you’re organising.
  4. The item hierarchy shouldn’t have any containers of type Tiled listed.
  5. Try to name your containers to help maintenance in the future

Below is a picture of the item hierarchy I ended up with using this approach

I created a floating vertical container called Base, positioned 0,0 and 1200 x 850. Background set to None, no border and inner and outer padding all 0.

I added a text object to contain the title. Background set to None and no border. Outer padding set to 10 all round, and inner padding 0.

I added a blank object, which I renamed Horizontal divider. Background set to light grey, no border. Outer padding set to left and right 10 and top and bottom 0. Inner padding all 0. Height set to 2.

I added another Vertical container, which I renamed Body. Background set to None, no border and all inner and outer padding set to 0.

I added 3 horizontal containers on top of each other, and set the property of the Body vertical container to distribute contents evenly so each horizontal container was the same height.

1st horizontal container

I named Row 1 – Level 1. I set the background to the pale green. No border. Outer padding set to left & right 10, top & bottom 5. Inner padding all 0.

Into this I added a text field to describe the levels. Background of this was white, no border and outer padding set to 0 (so the green background disappears). Inner padding was set to top: 20 and 10 for the rest.

Next the Histogram chart. Border set to green. Background white. Outer padding right:5, rest 2. Inner padding set to 10 all round. Width of chart fixed to 380 px.

Next the Level 1 text object. No border, no background. Outer padding 4 all round; inner padding 0. Formatted text object to rotate text. Width of object set to 40 px.

2nd horizontal container

I named Row 2- Level 2. I set the background to the pale blue. No border. Outer padding set to left & right 10, top & bottom 5. Inner padding all 0.

Into this I added a text field to describe the challenge. Background of this was white, no border and outer padding set to 0 (so the blue background disappears). Inner padding was set to 10 all round. Width of object set to 380px.

Next the Peas in a Pod chart. Border set to blue. Background white. Outer padding right:5, rest 2. Inner padding set to 10 all round.

Next the +/- bar chart. Border set to blue. Background white. Outer padding right and left 5, top & bottom 2. Inner padding set to 10 all round. Width of object set to 380px.

Next the Level 2 text object. No border, no background. Outer padding 4 all round; inner padding 0. Formatted text object to rotate text. Width of object set to 40 px.

3rd horizontal container

I named Row 3- Level 3. I set the background to the pale purple. No border. Outer padding set to left & right 10, top & bottom 5. Inner padding all 0.

I added the Slope chart. Border set to purple. Background white. Outer padding right:5, rest 2. Inner padding set to 10 all round. Width of object set to 380px.

Next the bar-in -bar chart. Border set to purple. Background white. Outer padding right & left 5, top & bottom 2. Inner padding set to 10 all round.

Next the side-by-side bar chart. Border set to purple. Background white. Outer padding right and left 5, top & bottom 2. Inner padding set to 10 all round. Width of object set to 380px.

Next the Level 3 text object. No border, no background. Outer padding 4 all round; inner padding 0. Formatted text object to rotate text. Width of object set to 40 px.

It was a bit of trial and error to get the spacing as required, and a few calculations to work out how wide I wanted each chart to be, based on the width of the dashboard and the other items in each row.

Anyway, my published viz is here.

Happy vizzin’!

Donna

Can you design a multi-dimensional Panel Chart for detailed analysis?

Donna ColesLeave a comment

Yusuke set this week’s challenge which is pretty tough-going. I got there eventually, but it wasn’t smooth sailing, and had a lot of false starts and changes to calculations throughout to get to the end. I will endeavour to explain where I had difficulties, but some of the calculations I came up with were more down to trial and error ( eg I wonder if this will work…?) rather than a known direction.

Modelling the data

We were provided with a version of Superstore and a State Abbreviations data set. I chose to use the Superstore Excel file I already had. I combined the two data sets in the data source pane using a relationship where State/Province in the Orders table of the Superstore excel file matched with the Full Name field in the State Abbreviations csv file.

As the data was just related to 2024, I added this as a data source filter (Year of Order Date = 2024)

Identifying the month

I decided I was going to use a parameter to select the month to filter. I first created

Order Date MY

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

and then created a parameter

pOrderDate

date parameter defaulted to 01 Dec 2024, that I populated as a list using the values from the Order Date MY field. I set the display format to be a custom format of mmmm yyyy

Examining the data

On a new sheet, add State/Province and Abbreviation to Rows and Category to Columns to create a very simple ‘existence table’.

The presence of the Abc in the text table, shows that at least 1 record exists for the State/Category combination during 2024.

We can see some states, such as Arkansas, District of Colombia, Kansas etc don’t have any records at all for some Categories. However, when we look at the viz, we can see markers and labels associated to these Categories. In the image below, Kansas shows markers and labels against Furniture and Technology, when there are no records for these combinations at any point in 2024.

Handling this situation is the reason some of the calculations that follow are more complicated than you might expect.

Building the Trellis/Panel Chart calculations

Now when I built the viz, putting it into a trellis display was the last thing I did, but in rebuilding in order to write the blog, I’m finding that the table calcs needed start to help with the missing marks discussed above.

We need to count the number of States. For this create

State Count

SIZE()

Make this discrete and add to Rows. Adjust the table calculation to compute by State/Province and Abbreviation. 47 should be listed against every row in the column which is the number of rows displayed, and an Abc mark now exists for every State/Category combination.

We’ll also create

Index

INDEX()

make this discrete and add to Rows as well adjusting the table calculation to also compute by State/Province and Abbreviation

This has the effect of providing a counter for every row from 1-47.

We also need a parameter to define how many columns the display will be over

pCols

integer parameter defaulted to 8 which displays a range from 1 – 15 with a step of 1

With these fields, we can now define which row each record will sit on based on the counter for each State (the Index) and the number of columns to display (pCols).

Rows

INT(([Index]-1)/[pCols])

and we can also work out which column each record will sit in

Cols

([Index]-1)%[pCols]

Make both these fields discrete and add to Rows. Verify the table calc setting is as before. Show the pCols parameter, and test changing it and observe how the Rows and Cols values change

Remove State Count and Index from Rows. These won’t be needed in the final viz, as they’re just building blocks for other calculated fields.

Calculating the Profit Ratios

We need two Profit Ratio values – one for each State and Category per month and one ‘overall’ for each State by month

At the State/Category level, create

Monthly Sales

IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END

and

Monthly Profit

IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END

and from this create

Monthly PR

ZN(SUM([Monthly Profit])/SUM([Monthly Sales]))

custom format this to 0.0%;-0.0%;N/A which will then display the text N/A whenever the value is 0. Add this to the Text shelf.

Some additional State/Province records have appeared at this point with no Abbreviation (null). Filter these out.

To get the Overall profit ratio for the State, I created

State Sales for Month

{FIXED [State/Province]: SUM(
IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END)
}

and

State Profit for Month

{FIXED [State/Province]: SUM(
IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END)
}

and then created

PR for Month

SUM([State Profit for Month])/SUM([State Sales for Month])

and formatted this to a % with 1 dp.

Add PR for Month into the table and then apply a Sort to the State/Province field to sort by PR for Month descending

We’ve still got some gaps – we want to see the PR for Month value populated against every Category which have a profit ratio. For this create

State PR for Month

WINDOW_MAX([PR for Month])

Add this to the table and apply the table calculation to compute by Category only – we now have the overall profit ratio for each State plotted against every Category.

Note – we needed both PR for Month and State PR for Month as the latter is a table calculation and we can’t apply sorting on a table calc.

Building the panel chart

In building this I referred to my own blog post on a previous challenge, as the bar chart we need to build, isn’t a typical bar chart.

On a new sheet, add State/Province, Abbreviation and Category to Rows. Add Monthly PR to Text. Move the Abbreviation pill from Rows to Filters and exclude Null. Sort the State/Province field by PR for Month descending. Show the pOrderDate and pCols parameters.

Add Cols to Columns and Rows to Rows, so it’s the first pill listed. Move State/Province to Detail. Adjust the table calculations of both the Rows and Cols fields to be computing by State/Province and Category (listed in that order), and at the level of State/Province.

To build the bars, we’re using ‘fake axis’ on both the x & y axis to position the marks we want to display. We need

PR Bar Axis

ZN(LOOKUP(MIN(0.0),0))

and

Y-Axis

IFNULL(LOOKUP(MIN(0.5),0),0.5)

As alluded to above, these evolved as I played around to get the behaviour required.

Add Y-Axis to Rows and adjust the table calculation to compute by State/Province and Category (in that order) at the level of State/Province. Edit the axis to be fixed from -1 to 4.

Add PR Bar Axis to Columns. Again adjust the table calculation setting to be as the others. Change the mark type to Bar. Add another instance of Monthly PR to the Size shelf, and then adjust the Size to be Fixed and aligned Left.

Add Monthly PR to the Colour shelf, and adjust the colour legend to be fixed from -1.5 to 1.5 and centred at 0.

Edit the PR Bar Axis axis to be fixed from -1.25 to 1.25, to ensure the bars remain centred aligned regardless of the month selected.

Adjust the Tooltip as required.

Format the chart to remove all gridlines, remove the zero line for rows, and make the zero line for columns more prominent – dashed and darker. Remove all axis ticks. Add dark row and column dividers against the pane only and at the appropriate level so the information for a single State is contained within the boundaries.

Add row banding against the pane only at the appropriate band size and level

Reduce the width of each row, and widen each column to get a ‘squarer’ display.

Now we need to add the State/overall profit square. for this we need to ‘position’ a mark on the viz

Square Point

IF LAST()=0 THEN 0.9 ELSE NULL END

Add this to Columns. Edit the table calculation so it’s just computing by Category, then make the chart dual axis and synchronise axis.

This has created a second marks card, and essentially ‘duplicated’ the information for the ‘last’ Category only in each State.

Change the mark type to square. Remove Monthly PR from Colour and Size and Label . Add State PR for Month to Label and Colour instead. Adjust the table calculations to be computing by Category only. Align the label to be middle centre. Increase the Size of the mark so it’s roughly square.

Adjust the Colour legend of the State PR for Month so it ranges from -1.5 to 1.5 like the other one.

Adding Abbreviation to Label doesn’t work for every cell, so we need

Label – Abbreviation

WINDOW_MAX(MAX([Abbreviation]))

Add to Label and adjust the table calculation to be computing by Category only. Adjust the layout of the Label to match.

Update the Tooltip to reference State PR for Month instead. Hide the null indicator.

Finally hide the axis and the Cols and Rows pills (right click, uncheck show header). Hide the Category column title. Name the sheet.

And that should be the main viz. Add to a dashboard and only show one of the colour legends, renamed accordingly.

My published viz is here.

Happy vizzin’!

Donna

Can you show YTD and PYTD, whilst also being able to change the date range and switch between Month & Week?

Donna ColesLeave a comment

It was Lorna’s turn to set the challenge this week – to compare YTD with Previous YTD while also filtering date ranges which updated based on the year of focus. She hinted it was a great use case for an INCLUDE LoD and I have to admit that seemed to fry my brain! I spent a couple of hours trying various concepts and then put it to bed as I wasn’t progressing.

When Rosario Gauna published her solution, I had a quick look and realised that there was no need for an LOD after all, and I shouldn’t have got hung up on the hint. So thanks Rosario!

Setting up the data

The data set provided contains 4 complete years worth of data from 1st Jan 2021 to 31st Dec 2024. In order to simulate ‘YTD’ for 2024 and ensure my workbook would always show the concept of a partial year, I created a parameter

pToday

date parameter defaulted to 3 July 2024

and then I created

Records to Keep

[Order Date]<= [pToday]

In a typical business scenario, the pToday parameter would simply be replaced by the TODAY() function.

I then added this as a data source filter (right click data source > add data source filter) and set to True to restrict the data from 01 Jan 2021 through to 03 July 2024.

Setting up the calculations

Before building the Viz, we’re going to work through what calculations are needed to drive the behaviour we require. We’ll do this in a tabular view. First up we will need a couple of parameters

pYear

integer parameter defaulted to 2024 containing a list of values from 2021 to 2024. The display value should be formatted to a whole number without the , separators.

pDatePart

string parameter defaulted to Month containing a list of 2 values Month and Week.

Show these parameters on a sheet.

On the sheet, add Order Date as a discrete exact date (blue pill) to Rows.

The first thing we will do is identify the set of Order Dates that we need to consider based on the pYear parameter. That is if pYear is 2024, we want to consider the Order Dates in 2024 and the Order Dates in 2023. But if the pYear is 2023, we want to consider all Order Dates in 2023 and 2022. When we do identify these dates, we will ‘baseline’ them to all align to the focus year (ie the pYear value).

Date Baseline

DATE(IF YEAR([Order Date]) = [pYear] THEN [Order Date]
ELSEIF YEAR([Order Date]) = [pYear]-1 THEN DATEADD(‘year’, 1, [Order Date])
ELSE NULL
END)

Add this as a discrete exact date (blue pill) to Rows.

If you scroll through, you should see how the Date Baseline field is behaving as you change the pYear value.

Now we only want to include rows with dates, and in the case of 2024 v 2023, we only want dates up to ‘today’.

Filter Dates

IF [pYear] = YEAR([pToday]) THEN
[Date Baseline] <= [pToday]
ELSE
[Date Baseline] <= MAKEDATE([pYear], 12, 31)
END

If the pYear parameter matches the year of ‘Today’, then only include dates up to ‘Today’, otherwise include dates up to 31 Dec of the selected year.

Add this to the Filter shelf and set to True.

When pYear is 2024, you can see that we have Order Dates from 01 Jan 2023 to 03 Jul 2023 and then 01 Jan 2024 to 03 Jul 2024. But changing pYear to 2023, you get all dates through from 01 Jan 2022 to 31 Dec 2023.

Add Date Baseline to the Filter shelf, and select Range of Dates, then choose the Special tab and verify All dates is selected and select Apply to commit this option.

Show the Date Baseline filter to display the range control filter.

Changing the pYear parameter will change the start & end dates in the Date Baseline filter to match the year selected. But with 2024 selected, the range ends at 31 Dec 2024. We don’t want this – we only have data up to 3rd July 2024. To resolve this, set the property of the filter control to Only Relevant Values

Adjust the date ranges and the records in the table should adjust too. If you change the pYear parameter after you’ve adjusted the date range, you’ll need to reset/clear the date range filter.

The next thing we need to handle is the switch between months and weeks. For this create

Date to Display

DATE(CASE [pDatePart]
WHEN ‘month’ THEN DATETRUNC(‘month’,[Date Baseline])
ELSE DATETRUNC(‘week’, [Date Baseline])
END)

Add this as a discrete exact date to Rows and you can see how this field works when the pDatePart field is altered.

So now we have the core filtering functionality working, we need to get the measures we need

YTD Sales

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

PYTD Sales

IF YEAR([Order Date]) = [pYear]-1 THEN [Sales] END

format both of these to $ with 0 dp

% Diff

(SUM([YTD Sales]) – SUM([PYTD Sales]))/SUM([PYTD Sales])

custom format this to ▲0%;▼0%;0%

Building the KPI

On a new sheet, double click into Columns and type MIN(0), then repeat, so there are 2 instances on MIN(0) on Columns and 2 marks cards.

Change the mark type on the All marks card to shape, and select a transparent shape (see here for details). Set the sheet to Entire View.

On the 1st MIN(0) marks card, add YTD Sales to Label. Then adjust the label font and text and align middle centre.

On the 2nd MIN(0) add PYTD Sales and % Diff to Label and adjust the layout and formatting as required. Align middle centre.

On the tabular worksheet we were using to test things out, set both the filters to ‘apply to worksheets > all using this data source’, so persist the changes made on one sheet to all others.

On the All marks card, add Date Baseline to Tooltip and adjust to use the MIN aggregation. Then add another instance of Date Baseline to Tooltip and adjust to use the MAX aggregation. Update the Tooltip accordingly.

Remove all row/column dividers, gridlines, zero lines. Hide the axis. Name the sheet KPI or similar.

Building the line chart

ON a new sheet add Date to Display as a continuous exact date (green pill) to Columns and YTD Sales to Rows. Show the pYear and pDatePart parameters. The Date Baseline and Filter Date fields should have automatically been added to the Filter shelf. Show the Date Baseline filter.

Drag the PTYD Sales field from the data pane onto the YTD Sales axis and drop it when the cursor changes to a double green column icon.

This will automatically adjust the pills to include Measure Names and Measure Values onto the view. Adjust the colours of the lines and ensure the YTD Sales line is displayed on top of the PYTD Sales line (just reorder the values in the colour legend if need be).

Add YTD Sales and PYTD Sales to Tooltip and adjust accordingly. Edit the Value axis and adjust the title of the axis to Sales. Remove the title from the Date to Display axis.

Building the dashboard

Use a combination of layout containers to add the objects onto a dashboard. I started with a horizontal container, with a vertical container in each side.

My published viz is here.

Happy vizzin’!

Donna

Can you filter a donut without highlighting the pie?

Donna ColesLeave a comment

For this week’s challenge, Kyle looked to solve a problem that he’s seen discussed within another blog – how to solve a highlighting problem when filtering donut charts.

I’ve been away on a little holiday abroad for a family wedding, so am on catch up this week. So I’m going to make this as brief as I can as time is limited.

Building the donut charts

Use the steps described in this blog post I wrote for my company to build a donut chart using the dual axis method.

For the Category donut chart, you will need Category on Colour and Sales on Angle of the outer Pie Chart. For the inner circle, you will need to add Sales to Text. Adjust the text as required. Sales needs to be formatted to $ with 0 dp.

For the Sub-Category donut chart, you will need to add Category to Colour. Then add Sub-Category to Detail and click on the 3 dots to the left of the Sub-Category pill and change to also add to Colour.

To adjust the colours, edit the colour legend, select all the options within the same Category. Select a sequential colour palette that matches the core colour for the category, then select Assign Palette. The colours should change to a range of that colour.

Create a new field

# Products

COUNTD([Product ID])

and add this to the Angle shelf. Add Sales to the Tooltip shelf and adjust the tooltip.

For the inner circle, add #Products to Text. Adjust the text as required

Filtering the donut

Add the two sheets to a dashboard. Add a dashboard filter action

Filter Cat

On select of the Category donut, target the Sub-Category donut chart passing in all fields. Keep filtered values when selection cleared.

Stopping the Category donut from being highlighted

Create new fields

True

TRUE

False

FALSE

and add these to the Detail shelf on the All Marks card of the Category donut sheet.

Then create a dashboard filter action

Unhighlight

On select of the Category donut on the dashboard, target the Category donut sheet itself, passing in the fields Tue = False. Show all values when selection cleared.

Now when the Category donut is clicked on, the other segments won’t fade. However, the selection is still visible – the edges of the pie are displayed.

Stop showing the selected section of the pie

For this we employ a trick mentioned in the blog post referenced in the challenge. Create a new field

Dummy

‘Dummy’

and add this the Detail shelf of a new sheet. Change the mark type to polygon so nothing is visible.

Add this to the dashboard as a floating object – make it small and place somewhere inconspicuous

Whilst the selections will still be visible when testing on Desktop, once published to Tableau Public, the presence of the polygon forces the whole dashboard to be rendered server side rather than client side. This reduces the amount of interactivity, and consequently the pie chart segments don’t display when clicked.

My published viz is here.

Happy vizzin’!

Donna

Can you combine part-to-whole and comparison?

Donna ColesLeave a comment

Sean’s #WOW2024 challenge this week was to visualise the comparisons between different entities within a limited space, by using a Viz In Tooltip (VIT) to provide additional information on hover.

Building the Treemap

Add Sales to Size, Category to Colour and Sub-Category to Detail to create the basic tree map. Tableau will automatically define the layout based on the space available, and you can’t control this, so don’t worry if it doesn’t match the final output.

Move Sub-Category from Detail to Label and add Sales to Label too. Format Sales to to be $ at 0dp, then adjust the Label as required.

Crete a new field

Rank

RANK(SUM([Sales]))

Format the field so it is formatted to be a whole number with a suffix of .)

Convert to discrete and add to the Detail shelf. Adjust the table calculation and verify it is computing by both Category and Sub-Category – this ranks every cell from 1 to 17 based on the Sales.

Adjust the Tooltip to just show the Category, Sub-Category and Sales data.

Name the sheet TreeMap.

Building the bar chart (the VIT)

On a new sheet add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Add Category to Colour and adjust the opacity to around 30%. Manually widen each row.

Add Sales and Sub-Category to Label and adjust to get the correct layout and then align left middle.

Add Rank to Rows after Sub-Category. Hide the Sub-Category field (uncheck show header).

We need to identify a row that has been ‘selected’ by the user. For this we need a parameter

pSelectedRank

Integer parameter defaulted to 0

Show the parameter on the page. We need to indicate which row is selected.

Selected Rank Indicator

IF [Rank] = [pSelectedRank] THEN ‘●’ ELSE ” END

Add this field to Rows before Rank, then update the pSelectedRank parameter to a valid number, eg 5.

Hide field labels for rows, make the Selected Rank Indicator and Rank columns narrower. Adjust alignment of columns and increase the font size of the Selected Rank Indicator column.

Hide the Sales axis, remove all gridlines, zero lines and row & column dividers. Format the Row Axis Ruler to be a black line.

We only want a subset of the rows to show – those that are 2 below and 2 above the selected rank. Create a new field

Rank in Range

([Rank] >= [pSelectedRank]-2) AND ([Rank]<= [pSelectedRank]+2)

Add this to Rows in front of Sub-Category. The bar chart will split.

We only want to see the ‘True’ rows. You can do this in 2 ways.

1 – Click on the word True and select Keep Only from the dialog box displayed. This will add Rank in Range to the Filter shelf

or

2 – right click on the section with the False records and select Hide from the context menu. The rows associated to False will disappear but won’t actually be filtered out of the view (this is what I did)

Hide the Rank in Range column from showing (uncheck show header). Name this sheet VIT or similar.

On the TreeMap sheet, update the Tooltip to show the VIT worksheet (via Insert > Sheets). Adjust the code snipped inserted so the filter property = “” (ie no filters are passed and the complete viz is displayed).

If you test it from the worksheet, and still have the pSelectedRank set to 5, you should see the same set of bars regardless of which part of the TreeMap you hover on.

Applying the filter

Create a dashboard of the required size and add the TreeMap sheet. Set the viz to fit entire view. It’s likely it will now rearrange itself to (nearly) match the solution.

Add a parameter dashboard action to set the selected rank

Set Rank

On hover of the TreeMap sheet, set the pSelectedRank parameter by passing in the value from the Rank field aggregated to nothing. When the selection is cleared, set to 0.

Hovering over the dashboard should now update the display in the VIT to ‘focus’ on whichever Sub-Category has been selected on the TreeMap.

My published viz is here.

Happy vizzin’!

Donna

Can you visualise headcount distribution in multiple ways on a single sheet?

Donna Coles5 Comments

This week’s challenge focuses on allowing the user flexibility in both how they want to ‘slice’ the measure being reported (actual headcount), and how they want the results displayed to meet the needs of the question they are trying to answer. Whilst the above could be built using multiple sheets and ‘sheet swapping’ using dynamic zone visibility, the aim is to try to build within a single sheet.

Let’s get cracking.

Enhancing the data

Not all the fields we need to slice the data by are included initially in the data provided, so we need to define some additional calculated fields, as stated in the requirements.

Contract Type

IF [FTE] = 1 THEN ‘Full Time’ ELSE ‘Part Time’ END

To determine the Age Bracket an employee belongs to, we first need to know their age. Usually this would be based on today’s date (using the TODAY() function), but as this data is static, we’re going to pretend today is 01 Dec 2022, by capturing this in a parameter.

pToday

date parameter defaulted to 01 Dec 2022

We can then work out how old the employee is (in complete years) on 01 Dec 2022

Employee Age

IF DATEPART(‘dayofyear’, [Birth Date])<=DATEPART(‘dayofyear’,[pToday]) THEN
DATEDIFF(‘year’, [Birth Date], [pToday])
ELSE
DATEDIFF(‘year’, [Birth Date], [pToday])-1
END

The ‘dayofyear’ parameter of the DATEPART function returns as it suggests the day of the year, so is a number from 1 to 365 (or 366 in a leap year). So this calculation is basically saying, if the birthday falls before ‘Today’, then they’ve had their birthday in that year, so a simple difference between the years suffices, otherwise the birthday hasn’t happened yet, so take one off the difference in years.

We can then create

Age Bracket

IF [Employee Age] < 20 THEN ‘Under 20’
ELSEIF [Employee Age] < 30 THEN ’20-29′
ELSEIF [Employee Age] < 40 THEN ’30-39′
ELSEIF [Employee Age] < 50 THEN ’40-49′
ELSEIF [Employee Age] < 60 THEN ’50-59′
ELSE ‘Over 60’
END

Setting up the parameters

All the user interactivity is driven through selection of parameters.

pDisplayType

string parameter defaulted to Butterfly Chart with a list of values Stacked, Side by Side, Butterfly which each have a slightly different Display As value (up to you whether you want to do this).

pDisplayRowsBy

string parameter defaulted to Age Bracket with required options listed.

pSplitBarsBar

string parameter defaulted to Contract Type with required options listed

Building the Viz

The pDisplayRowsBy parameter defines the field that we want to display on the Rows shelf. The parameter is just a string value though which we need to map to a proper field from the data set

Dimension – Rows

CASE [pDisplayRowsBy]
WHEN ‘Age Bracket’ THEN [Age Bracket]
WHEN ‘Contract Type’ THEN [Contract Type]
WHEN ‘Department’ THEN [Department]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Nationality’ THEN [Nationality]
END

The pSplitBarsBy parameter defines the field that will be used to colour the bars. Again we need to map this to fields in the data set

Dimension – Colour

CASE [pSplitBarsBy]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Contract Type’ THEN [Contract Type]
END

The existing Employees(Count) field that is automatically included is just a counter for the number of rows in the data set, so is essentially our headcount number. However, we need to think about how the display needs to look for the different types of chart, specially the butterfly chart.

When building a butterfly chart, the bars to the right of the middle axis are plotted as positive numbers, while the bars to the left are plotted as negative numbers (so the middle is actually 0). So in the event the display type of Butterfly Chart is selected, we need to make some adjustments, so that some of the values are plotted on the negative axis. This means our calculation looks like

Headcount

IF [pDisplayType] = ‘Butterfly’ THEN
IF MIN([Dimension – Colour]) IN (‘Part Time’, ‘Female’) THEN COUNT([Employees]) * -1
ELSE COUNT([Employees])
END
ELSE
COUNT([Employees])
END

If it’s a Butterfly chart, then if the values associated to the field we’re colouring the bars by are ‘Part Time’ (pSplitBarsBy = Contract Type) or ‘Female’ (pSplitBarsBy = Gender), then negate the count of employees, otherwise just return the count of employees.

Let’s start putting some of this into a viz to see what we’ve got…

On a sheet, add Dimension-Rows to Rows and Headcount to Columns. Add Dimension-Colour to Colour. Show the 3 core parameters and adjust the colours to suit.

Change the pSplitBarsBy to Gender, and adjust colours again. Change to ‘N/A’ and adjust again, so all possible options for the Dimension-Colour field have been set. Change pSplitBarsBy back to Contract Type.

Now change pDisplayType to Stacked Bar.

We want Full Time segment of the bar to appear first against the zero line of the axis, so manually re-order the options in the colour legend, so Part Time is listed first.

Change pSplitBarsBy to Gender and verify that the Male segment of the bars are displaying first against the zero line of the axis. Change pSplitBarsBy back to Contract Type.

For the side by side bar chart, we need another dimension on Rows that is essentially the Dimension -Colour field. However, we only want the value when the pDisplayType is Side by Side. So we need

Dimension – Side by Side

IF [pDisplayType]=’Side By Side’ THEN [Dimension – Colour] ELSE ” END

Add this to Rows after the Dimension-Rows field.

This adds an additional column to the display. While we’re in side by side or butterfly ‘mode’ , nothing is displayed. Change pDisplayType to Side by Side and we get the separation required.

Since the Colour legend is defining what the bar relates to, we can hide this field (right click on the Dimension – Side by Side pill and uncheck show header.

We want to also show the % of Total headcount split for each row. Create a new field

Total Employees Per Row

{FIXED [Dimension – Rows] : COUNT([Employees])}

then create

% Total Headcount

[Headcount]/SUM([Total Employees Per Row])

and format to a custom number format of 0%;0%. This is a % with 0 decimal places, but the custom format is used as we want negative numbers (caused by the butterfly chart type) to be displayed as +ve percentages.

Add % Total Headcount to Columns and verify the display for the different display types. Hint – if the calc is working as expected, the Stack Bar option should show 100% 🙂

Labelling the Viz

We can’t simply add labels using the usual method (show mark labels) and adjusting the positioning, as it won’t always show what we want, where we want for all the different display types. So we need to employ different techniques based on the chart being displayed.

Let’s start with the stacked bar chart. For the Headcount bars, we want to display the total number of employees at the end of the bar, and for the % Total Headcount, we want the % displayed in the middle of the bar.

Create a new field

Ref Line – Stacked

IF [pDisplayType] = ‘Stacked’ THEN [Headcount] END

Add this to Detail shelf of the Headcount marks card, then add a reference line to the Headcount axis (right click axis > Add Reference Line) that for each pane references the Sum of the Ref Line – Stacked field, and just displays the Value on the Label. No line or tooltip is displayed.

Format the reference line (right click the ‘invisible’ line) to adjust the colour and alignment of the font.

Add % Total Headcount to the Label shelf of the % Total Headcount marks card. You may need to adjust the width of the bars so you can see the values. Adjust the font to be white.

Change the pDisplayType parameter to Side by Side. No labels should be displayed. This is because the label we used for the headcount bar was based on the display type, and the label we used for the % total has been coloured white and is therefore invisible on a white background (set it to black and you’ll see it displayed). So again we need more fields

Ref Line – Side By Side

IF [pDisplayType] = ‘Side By Side’ THEN [Headcount] END

Add this to the Detail shelf of the Headcount marks card, and again add a reference line to the Headcount axis, this time setting the scope at the cell level.

Once again format the reference line to set the font and alignment.

Create

Label – % of Total Side by Side

IF [pDisplayType] = ‘Side By Side’ THEN [% Total Headcount] END

Format to a % with 0 dp and add this to the Label shelf of the % Total Headcount marks card, and then adjust the Label so this field is appropriately coloured and listed before the other label (which is coloured white).

Change the pDisplayType parameter to Butterfly chart. The % labels should already display. We just need to sort the labelling for the actual headcount. For this we need

Ref Line – Butterfly Pos

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] >= 0 THEN [Headcount] END
END

and

Ref Line – Butterfly Neg

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] < 0 THEN [Headcount] END
END

Add both these fields to the Detail shelf of the Headcount marks card. Then add reference lines for each of the two fields

Then format both the reference lines so the font and alignment is set as required (in the case of the Ref Line – Butterfly Neg reference line, it needs to be left aligned)

Add Headcount and % Total Headcount to the Tooltip shelf of the All marks card, and then adjust the tooltip. Finally tidy up by

  • Adjusting format of the Dimension – Rows values and aligning middle left.
  • Hide field labels for rows
  • Remove column dividers
  • Adjust row dividers to be more subtle
  • Remove gridlines, zero lines, axis ticks & rulers
  • Remove the Headcount & % Total Headcount axis.

Add the viz to a dashboard. I used a horizontal container placed above the viz to add text objects to represent the headings. The first text box just the referenced the pDisplayRowsBy parameter value, so it was dynamic.

My published viz is here.

Happy vizzin’!

Donna

Can you manually overwrite a value in a table?

Donna ColesLeave a comment

For this week’s challenge, Erica wanted us to be able to set a discount value for a Sub-Category which once set, overwrote the value displayed in the table and applied the discount to the other visuals. She added an additional complexity to display the input field aligned with the selected Sub-Category. She alluded to the fact this last requirement was likely to be tricky, and she wasn’t wrong. I managed to build a solution, and I’ll walk through the principles, but there will be a bit of trial and error involved….

Building the table

We will need to capture the discount value to apply in a parameter, so create

pDiscount

integer parameter defaulted to 5

and we also need capture the Sub-Category to apply the discount to

pSubCat

string parameter defaulted to Art

The discount to display will need to be adjusted for the selected Sub-Category

Discount to Display

IF [Sub-Category] = [pSubCat] THEN [pDiscount]/100 ELSE [Discount] END

format this to % with 1 dp.

Add Category and Sub-Category to Rows. Double-click into rows and manually type in MIN(1). Change the mark type to shape and change the shape to be a transparent shape (see this blog for more details). Add Discount to Display to the Label shelf, and change the aggregation to Average. Align middle centre. You should see that the value associated to Art is 5%.

Note – you may be wondering why this is not being displayed in a standard table – why the need for the fake axis? I can’t recall exactly why I ended up with this, but it will have been borne out of later steps, and the need to try and get the input parameter aligned – by all means feel free to try without and see how it goes 🙂

Hide the MIN(1) axis, remove column dividers and gridlines / zero lines, axis rulers etc. Add subtotals (Analysis menu > Totals > Add all subtotals). Stop the Tooltip from displaying. Adjust the height and width of the cells so you can see all the rows on the screen. Show the parameters, and test the functionality by manually changing the parameters.

Create a new field

Index

INDEX()

Set this to be a discrete field and add to Rows after Sub-Category. Adjust the table calculation so it is set to compute using both Category and Sub-Category, and you show see the rows numbered from 1 to 17 (except for the total rows).

In order to help us position the input parameter, we will need to capture the index of the selected Sub-Category, so create a parameter

pIndex

integer parameter defaulted to 6 (the value associated to Art)

For now, we’ll leave the index displaying in the table. But we will hide it eventually. Name this sheet Table.

Building the line chart

The line chart needs to show the actual Sales and the sales as they would be if the revised discount was applied for the selected Sub-Category. The value stored in the Sales field will already account for the original value stored in the existing Discount field. So to work out what the adjusted Sales will be, we need to determine the full sale price (Sales / (1 – Discount)) and then apply the inputted discount value from pDiscount (multiply by (1- (pDiscount/100))

Adjusted Sales

IF [Sub-Category] = [pSubCat] THEN ([Sales] / (1-[Discount])) * (1- ([pDiscount]/100))
ELSE [Sales] END

format this and the Sales fields to $ with 0do

On a new sheet add Order Date at the continuous Month/Year level (green pill) to Columns. Add Sales to Rows, and then drag Adjusted Sales and drop it on the Sales axis when the green ‘2 column’ icon appears. This will automatically add Measure Values to Rows and Measure Names to Filter and Colour.

Calculate the difference as

Difference

IF [pSubCat]<>” THEN
(SUM([Adjusted Sales]) -SUM([Sales])) / SUM([Sales])
END

and apply a custom number format of ▲0.00%;▼0.00%;0%

Format the date axis to display dates as custom format mmm yy, and edit the axis to change the title to Month.

Add Sales, Adjusted Sales and Difference to the Tooltip and update to suit. Show the pDiscount parameter and update it to a really large value, say 10,000. The 2 lines will show more prominently and the Sales axis will adjust its scale.

The requirement is to ensure the grey line (the original sales) doesn’t move, so edit the value axis, adjust the title to Sales and then fix the start from 0, but end ‘automatic’

This will push the Adjusted Sales off the chart. Reset the pDiscount to something more reasonable like 5.

Remove row/column dividers and gridlines, but retain axis rulers. Name the sheet Line.

Building the KPI card

On a new sheet, add Sales to Text. Change the Mark type to shape and select a transparent shape. Align the text middle centre, and set the display to Entire View.

We want to only show the Adjusted Sales if a Sub-Category has been selected, but we need to line chart to display a blue line all the time, so we need another field

Label Sales

IF [pSubCat]<>” THEN [Adjusted Sales] END

format this to $ with 0dp and add to the Label shelf.

Adjust the layout and display of the text as required. Hide the Tooltip. Name the sheet KPI.

Right, we’ve got the key components. Let’s get these all on a dashboard first.

Building the dashboard

Getting all the objects you want on the dashboard (including titles, footers etc) positioned exactly where you want them, with the appropriate padding set is crucial to getting the method I’m going to use to reposition the input parameter. It’s also quite fiddly and I can’t guarantee that even if you follow the steps, you’ll get things looking right…

Anyway, let’s start with the dashboard.

I set the dashboard size to 1100 x 650, then I added a floating vertical container which I positioned 0,0 and sized 1100 x 650. I formatted the dashboard and set the background colour to light grey.

I then switched to Tiled and added a text box for my title. I set the background of this to white, outer padding to 0 and inner padding to 5.

I then added another text box beneath for the instructions. I set the outer padding to 0 and inner padding to 5. I then fixed the height to 70.

Next I added a horizontal container beneath the instructions. I add a blank object to it as a placeholder. Ensuring the horizontal container was selected (blue border), I set the outer padding to 5.

I then added another horizontal container beneath this, and added my standard footer (created by, recreated by etc). I set the outer padding of this container to have 5px on the left and right, and 0 on top and bottom. All the text boxes within I set to have 0 outer padding and 0 inner padding.

I then added another text box beneath to add in the link to the challenge, also part of my ‘standard footer. I set the outer padding for this text box to 0.

I then calculated how high all the ‘rows’ of objects were on the dashboard (the height of the title + the height of the instructions + height of my standard footer and challenge link), and then subtracted this from 650. I then fixed the height of the central horizontal container based on this value

Add the Table sheet into the left side of this container. Remove the title. Set the background to white. Adjust the outer padding to 0. Set the sheet to Fit Width. Very carefully, adjust the width of a row, so the table fills as much of the vertical space as possible without there being a scroll bar. This is really fiddly to do.

The addition of the table will have automatically added some parameters and a Tiled object to the layout. We’ll deal with these shortly, but leave them be for now.

Add a Vertical container to the right hand side. Add the KPI sheet and then then Line sheet underneath. Remove the blank object that was the placeholder. Widen the vertical container so the vizzes have more space. For both the KPI and the Line objects, remove the title, set the background to white, set the outer padding to 0. Set the inner padding of the KPI chart to 20, and the inner padding of the line chart to 10. Adjust the height of the KPI chart so its visible.

If all is well, you should have something like

Adding the interactivity

Create a parameter action

Set Sub Cat

On select of the Table chart, set the pSubCat parameter, passing in the value from the Sub-Category field. Reset to ” when unselected.

Set Index

On select of the Table chart, set the pIndex parameter, passing in the value from the Index field aggregated to None. Reset to 0 when unselected.

If you click different Sub-Categorys, the KPI and line chart will change. Once unselected, no adjusted sales or discount will display.

Getting the parameter to move

Duplicate the Table sheet, and remove all subtotals. On this sheet, we’re only going to display the rows up to the row before the selected Sub–Category. For this we need

Show Top Rows

[pIndex]=0 OR [Index]<[pIndex]

Add this to the Filter shelf and set to True. Verify the table calculation is computing by both Category and Sub-Category. If need be adjust, then recheck the filter is just displaying True still. Show the pIndex parameter and just test the filter is working, by changing the value. Here, with the parameter set to 6, it is showing rows up to 5.

What we’re trying to do is just display the rows necessary so that the parameter input can be added beneath this sheet on the dashboard. The reason we have removed the subtotals, is that if the index is set to 2, we only want to display 1 row above; that for Bookcases. With subtotals included we’d get a row for Bookcases and a Total row. However once we get to a new Category as we have above, we need to show an additional row to accommodate for the subtotal displayed within the original table.

So we need to force additional rows to show in some circumstances but not others.

To help with this I have made use of the Region field. I checked that for two regions, Central & East, there were Sales in both Regions for every Sub-Category.

Add Region to the Filter shelf and set to Central & East only. Add Region to the Detail shelf. Remove Discount to Display from the Label shelf. Create

Extra Row

IF LAST()=0 THEN STR(ATTR([Region]) )
ELSE ''
END

Add this to Rows after Index. Adjust the table calculation so that it is computing by Sub-Category only. With pIndex set to 0, this should just display the two Regions against the last Sub-Categorys in each Category, simulating the subtotal rows.

But set the index to 6 and we just get the additional rows for the Furniture Category

and set to 2 and we just the row for Bookcases

Hide the Category column (uncheck show header). Name the sheet Top. Set the pIndex back to 0, and let’s start seeing how this works on the dashboard.

Add a vertical container between the existing table and the kpi/line chart. Reduce the width. Add the Top sheet into this container. Remove the title and set the inner and outer padding to 0. Set the sheet to fit width. If you’re lucky, all the rows should align. If not you may need to tweak again.

Select a Sub-Category in the original table, and the 2nd table should shrink.

From the Tiled section on the layout item hierarchy, navigate through until you find the pDiscount parameter. Click it to select it on the dashboard, then move that object to sit beneath the shortened table. Then select the Tiled section on the item hierarchy, and right click and remove from dashboard, which will remove all the unnecessary parameters/legends that were being displayed.

For the pDiscount object, remove the title, set the background to white and set the outer padding to 0. Set the background of the vertical container to white too. We only want this discount parameter to show when a Sub-Category has been selected. To manage this, we need

Show pDiscount

[pSubCat]<>”

Select the pDiscount object on the dashboard, and then from the Layout tab, check the Control visibility using value and choose the Show pDiscount field

Unselecting the Sub-Category and the field won’t display

So now we’ve got the basics of what we’re trying to do, we obviously don’t actually want any of the table to be visible. But if we hide all the fields (uncheck show header), we lose the column headings which was helping with the positioning, as we can see below – the input box is no longer aligned with Art.

To fix this, create a new field

Dummy Header

”

and add to the Columns of the Top sheet. If you haven’t already, uncheck show header against all the other blue pills (Category, Sub-Category, Index and Extra Row). The sheet should look like below, and what the Dummy Header has done is create an extra spacing at the bottom of the page, which compensates for the heading we don’t have at the top… and this is the reason for creating a table using a fake axis 🙂

Back on the dashboard, everything is aligned again.

… except when we select Bookcases … argghhh!

Add a blank object above the parameter. Set the padding to 0, and adjust the height to about 18 px – enough to bring the parameter in line. Create a new field

Show Blank

[pIndex]=1

and use this to control the visibility of the blank object – ie we only want the blank object to come into play when Bookcases is selected, and nothing else.

Click around every Sub-Category and hopefully the parameter box is aligned each time.

Final touches

On the Top sheet, remove row dividers, so the sheet just always looks empty.

On the Table sheet, hide the Index field (uncheck show header).

Add left outer padding of 10px to the vertical container that contains the Top sheet and the pDiscount parameter. This should mean some grey spacing appears between the table and the input field.

Adjust the width of the objects to suit BUT DON’T fiddle with the heights at all!

To stop the other discounts from ‘fading’ when a Sub-Category is clicked, create a new field

HL

‘HL’

and add to the Detail shelf on the Table sheet. Then on the dashboard, add a highlight dashboard action that on select of the Table sheet, targets the Table sheet with the HL field only. This essentially has the effect of highlighting all the fields, since the HL field is applicable to every row.

Phew! This took some time and a lot of fiddling to get right, and even then I know there’s every chance that you can’t quite get things to align just right, or publishing to Tableau Public and it all seems to shift … My published viz is here. Fingers crossed you’re successful!

Happy vizzin’!

Donna

Can you make a page navigator?

Donna ColesLeave a comment

Yoshi set this week’s challenge to build a page navigator, but there was so much more in it too, so this could be a bit lengthy 🙂

Note, I’m blogging based on the full ‘advanced’ challenge, to include an ‘apply all’ button as well. I built the following sheets to build this via and I’ll talk through the basics of each of them in turn

  • List of State names
  • Bar chart of total State sales
  • Line chart of monthly State sales
  • Jitter plot of State sales by order
  • Navigation page number buttons
  • Back arrow
  • Forward arrow
  • Filter summary
  • Apply button

Preparing the data

The data being presented is only applicable to the states of the US. In the latest versions of Superstore, information for both Canada and the US is included, so I started by adding a data source filter to include only Country/Region = United States (right click data source -> Add Data Source Filter).

Building the list of State names

Add State/Province to Rows, and apply a sort to sort by the field Sales descending

Add State/Province to Text and to Colour. Adjust font to be bold and widen each row.

Create a new field

Index

INDEX()

and add to Rows before State/Province. Set the table calculation to be explicitly computing by State/Province. Index is essentially ranking each State from 1 to 49, as we’ve already sorted the listing of the states.

The requirement is to show up to 7 states on a page, so create

Page No

INT(((INDEX()-1) /7)) +1

Set to be a discrete field and add to Rows in front of Index. Again explicitly set the table calculation to compute by State/Province. This shows us which states are on which page.

We’re going to identify the page we’re on based on a parameter

pPageSelected

integer parameter defaulted to 1

Show the parameter, then create a new field

Is Selected Page

[pPageSelected] = [Page No]

Add to the Filter shelf. Initially select All. Then adjust/verify the table calculation is explicitly set to compute using State/Province. Then edit the filter to just show values that are True.

Adjust the pPageSelected parameter to test the functionality.

Hide the Page No, Index, and State/Province field from Rows (uncheck show header). Remove column dividers and don’t show the tooltip. Name this sheet States.

Building the bar chart

Note – to get the labelling and the spacing between the bars, this isn’t a ‘standard bar chart’. This is a technique that has been included in previous WOW challenges.

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Sales to Columns and Add State/Province to Colour. Add a grey border to the bars (via Colour shelf).

Double click into Rows and manually type MIN(1.0) and change the Mark Type to bar. Add Sales to Size, then click on the Size button and adjust the size from Manual to Fixed and align right.

Add Sales to Label and align top left. Adjust the Tooltip. Add Index to the front of Rows and adjust the table calculation to be computing by State/Province.

Add Page No to the front of Rows and adjust to computing by State/Province.

Set the page to Fit Width. Show the pPageSelected parameter and add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.

Change the parameter to show page 2. You’ll notice the axis has now adjusted from 0 – $80,000 whereas on page 1 it went up to $450,000. We want to retain the axis scale across the pages. For this, create

Max Sales

WINDOW_MAX(SUM([Sales]))

Add this to the Detail shelf and ensure the table calculation is computing by State/Province.

Add a reference line to the bottom Sales axis (right click axis > add reference line) and set it to cover the entire table, using the average Max Sales value. Don’t show any label., tooltip or line

The axis will now have readjusted and display up to 450,000 regardless of the page you’re on.

Adjust the Min(1.0) axis to be fixed from -0.5 to 2 to add some white space around the bars.

Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Name the sheet Total Sales.

Building the line chart

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province.

Add Order Date to Columns and adjust to be at the continuous Month/Year level (green pill ). You’ll notice the page numbering & indexes start to look odd – ie multiple states have same index.

Adjust the Order Date field to Show Missing Values, and our numbers are all aligned again.

If we just add Sales to Rows though, the indexes all mess up again due to the there being no values for some points.

To fix this create

Sales to Plot

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

This returns 0 if there is no value for the date / state combination. Add this to Rows instead and adjust the table calculation so it is computing by both State/Province and Month Order Date.

Edit the Sales to Plot axis, so it is displays an independent axis range for each row or column – this makes the records near the bottom show peaks, rather than just a straight line.

Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.

Add State/Province to Colour. Adjust Tooltip. Reduce the Size of the line.

Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Building the Jitter Plot

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province. Add Sales to Columns and Order ID to Detail. Change mark type to circle.

Readjust the table calc settings of Page No& Index to also include Order ID, but also set the leval at State/Province.

Add State/Province to Colour, and reduce the opacity.

To get the marks to not overlap so much, create a new field

Jitter

RANDOM()

add add to Rows as a dimension. Again adjust the table calcs so Jitter is also included in the settings.

Add Max Sales to Detail and adjust the table calc settings to be computing over all 3 fields – State/Province, Jitter & Order ID.

Add a Reference line to the Sales axis across the entire table, using the average of Max Sales and don’t display any label/tooltip or line.

Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using all fields and at the level of State/Province. If not adjust, and then recheck the filter is just showing True value.

Adjust the Tooltip. Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Name the sheet Sales by Order.

Building the Navigation Number Buttons

On a new sheet add State/Province to Rows and sort by Sales descending. Add Index to Rows and set the table calc to compute by State/Province. Move Index to Columns and State/Province to Detail.

Change the mark type to square and add Index to Label, aligning middle centre.

Create a new field

Colour – Page No

[Index] = [pPageSelected]

and add to the Colour shelf. Verify table calc is set to compute by State/Province. Adjust colours to suit and add a dark border.

We only want to show the indexes relating to the number of pages we have, which in turn is going to be based on what the data has been filtered by. So firstly we want to understand what the maximum number of pages is

Max Pages

IF SIZE()%7 = 0 THEN INT(SIZE()/7) ELSE INT(SIZE()/7)+1 END

If the number of results (ie number of states after filtering has occurred – the SIZE()) is exactly divisible by 7 (%7 = 0) then divide the results by 7 to get the max number of pages, otherwise, increment this value by 1. Eg if 14 results, it’ll be 2 pages, but 15 results will require 3 pages.

Now we know that, we can create

Pages to Show

INDEX() <= [Max Pages]

Add this to the Filter shelf. Set the True, Then adjust the table calc settings to be explicitly computing by State/Province for all nested calcs too.

Re-edit the filter to ensure it just shows True results.

Hide the Index from Rows and don’t show row/column dividers. Don’t show the tooltip. Name the sheet Page Nos.

Building the back arrow

On a new sheet, show the pPageSelected parameter, and change the mark type to Shape.

Create a new field

Show Page Back

[pPageSelected]>1

Add to the Shape shelf. If pPageSelected = 1, then False should display and adjust the shape to use a transparent shape (refer to this blog on how to set this up). Change the pPageSelected parameter to 2 and adjust the shape of the True option to be a filled arrow. Change colour to black.

On the dashboard ,we will need to define what page is being navigated to on click, so we need

Page Back

IF [pPageSelected]>1 THEN [pPageSelected]-1 END

Add this to the Detail shelf as a dimension.

Name the sheet Page Back.

Building the Forward Arrow

This is slightly more tricky than the back arrow, as we need to know how many pages are being displayed to know when we no longer need to show the arrow.

On a new sheet, add State/Province to Detail and sort by Sales descending. Remove the Lat/Long fields that automatically get added and change the mark type to shape. Create a new field

Show Page Forward

[pPageSelected]<[Max Pages]

and add to the Shape shelf. Set the table calc to be computing by State/Province explicitly. Set the mark type for ‘True’ to be a filled arrow and adjust colour to black.

Show the pPageSelected parameter and set to 7. Adjust the ‘False’ option to be a transparent shape.

Once again, on the dashboard, we will need to define what page is being navigated to on click, so we need

Page Forward

IF [pPageSelected]<[Max Pages] THEN [pPageSelected]+1 END

Add this to the Detail shelf as a dimension, and verify table calc is set to compute by State/Province explicitly.

We only want 1 arrow to show at most, so add Index to filter. Set to 1, then adjust table calc so it is set to compute by State/Province explicitly, and then re-edit filter to just select 1 again. Name the sheet Page Forward

Building the Filter Summary

On a new sheet add Category, Segment and Ship Mode to the Detail shelf and change the mark type to polygon..

Edit the Title of sheet and update as required

Name the sheet Filter Summary

Building the Apply Button

The basic outline for this is documented in this Tableau KB article here.

Create a calculated field

Apply

‘Apply Filters’

and add to Rows on a new sheet.

Add Category, Segment and Ship Mode to the Detail shelf and to the Filter shelf (set to All for each). Change the mark type to polygon. Right click the work ‘Apply’ in the column header and select hide field labels for rows.

Right click on the words ‘Apply Filters’ and select Format – set the shading of the header to teal.

As well as applying filters when the button is clicked, the page needs to reset to the first page. For this create

Reset Page 1

1

Add this to the Detail shelf as a dimension.

Adjust the size and colour of the font. Remove row dividers. Set the background of the worksheet to light grey. Remove the Tooltip. Name the sheet Apply Button.

Creating the dashboard

Now we have all the components, we can arrange the objects on a dashboard.

I added the 4 sheets making up the main viz int a horizontal container. All the sheets had the titles hidden, were set to fit entire view and had 0 padding, which gives the illusion of them all being a single viz. I added some outer padding to the container itself.

I used another horizontal container positioned above this one to add text boxes to give the viz headings.

Another horizontal container was placed above the title one. IN the left hand side I placed the Filter Summary viz., and in the right, I added a vertical container.

The vertical container had a blank and then a horizontal container underneath the blank object. The horizontal container then stored the page back, the page nos and the page forward sheets.

Another horizontal container was place above all this and I add the Apply Button sheet. I then moved the 3 filter objects automatically added to the sheet into this horizontal container too. I set the background of this container to light grey

Adding the interactivity

Multiple dashboard actions are needed to get the page to function as required. Now, I did have issues getting somethings to behave as I wanted, and I believe it was something to do with the order in which the actions were added. I can’t prove this… all I know is that I spent a long time trying to figure out why the filters I selected were getting reset when I pressed a page number, but removing all actions and adding again worked…

You need these actions

Apply Filters

Filter action that on select of the Apply Button sheet, targets all other sheets. Clearing the selection keeps filtered values. Category, Segment and Ship Mode should be passed through as selected fields.

Set Page No from Square

Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Index field that is not aggregated. Clearing the selection, keeps the current value.

Reset to Page 1

Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Reset Page 1 field that is not aggregated. Clearing the selection, keeps the current value.

Prev Page From Arrow

Parameter action that on select of the Page Back sheet, sets the pPageSelected parameter passing in the value from the Page Back field that is not aggregated. Clearing the selection, keeps the current value.

Next Page From Arrow

Parameter action that on select of the Page Forward sheet, sets the pPageSelected parameter passing in the value from the Page Forward field that is not aggregated. Clearing the selection, keeps the current value.

With these actions, you should be able to test the functionality, but you will find some fields become greyed out/ need clicking twice. We need to automatically ‘deselect’ them on click. For this I applied the basic principles discussed here.

Create new calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of the Page Back, Page Forward, Page Nos, and Apply Button sheets. Then add a dashboard filter action for each sheet.

Deselect Apply

On select of the Apply Button sheet on the dashboard, target the Apply Button sheet itself (ie not the object on the dashboard), passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat the above for the Page Back, Page Forward and Page Nos sheets.

Hopefully with all this you have a fully functioning dashboard. My published viz is here.

Happy vizzin’!

Donna