Tag: Table Calculations

Can you build a satellite chart to represent values exceeding 100%

Donna ColesLeave a comment

For this week’s challenge, we’re using the data from a previous challenge and visualising it using Sam Parson’s ‘satellite’ chart idea – see here.

Modelling the data

Connect to the Food Self-Sufficiency csv file then add another connection to the Circle_Scaffold excel file. Relate the two together using a relationship calculation where 1 = 1 (ie relate every row in left hand data source to every row in the right hand).

Since we only care about data from FY2019, add a data source filter to set Fiscal Year = FY2019.

This saves us from having to apply that filter to each sheet we build.

Building a single spiral

On a new sheet, add Prefecture to the Filter shelf and select Akita-ken (which is near the top of the list and has a % value over 200%).

Create a parameter

pMinRadius

integer parameter defaulted to 500

To build the spiral, we need to plot a mark for every percentage point from 0 up to the Food Self-Sufficiency % value. For this we will need to determine an (x,y) coordinate value for each point, which will require some trigonometry, based on the diagram below

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. As we are building a spiral, the radius of the circle will increase as we move around each percentage point, so we need

Radius

[pMinRadius]+[Path Percent Point]

We then need to determine the angle θ. As a circle is 360°, and a complete circle represents 100%, then 1% is 360/100, so the angle (in radians) for each % point plotted round the circle can be calculated as

Angle

RADIANS([Path Percent Point] * (360/100))

X

(SIN([Angle]) * [Radius])/360

Y

(COS([Angle]) * [Radius])/360

Now create the point

Spiral Point

MAKEPOINT([X],[Y])

Note – the X & Y values are divided by 360 due to the spiral we’re building and the increasing radius when displayed using map layers. If we were just plotting X against Y and not using map layers, this wouldn’t be required.

Double click on Spiral Point to automatically add Longitude and Latitude fields to the sheet.

Change the mark type to line and then add Path Percent Point to the Path shelf.

Add Prefecture to the Detail shelf, as it’ll be needed later when we build the trellis and remove the filter.

At this point, the spiral is showing 3 complete revolutions, as the data in the circle_scaffold data set contains info for up to 300%. We need to restrict it so we only show up to the Self-sufficiency ratio… so we need

Filter Percent Point Displayed

[Path Percent Point] <=[Self-sufficiency ratio for food in calorie base 【%】]

Add this to Filter shelf and set to True.

We now want to colour the spiral based on the percentage point associated to each mark plotted being <100%, between 100% & 199% or >= 200%, so we can use

Colour – Spiral

FLOOR([Path Percent Point]/100)

which will return an integer of 0, 1, or 2

Change this field to be discrete and then add it to the colour shelf and adjust colours accordingly.

Now obviously, you might be thinking things aren’t quite right – we’re not starting at the top and rotating differently. Simply pressing the swap rows and columns icon in the menu bar will resolve this, but if we do that too early, we lose the ability to add map layers, so leave as is for now.

Add the label map layer

Create a 0 point

Zero

MAKEPOINT(0,0)

Drag this onto the canvas and drop when the Add a Marks Layer option appears

This has the effect of creating a 2nd marks card

and now we have this, we can press the swap rows and columns icon in the menu bar to get the start of the spiral at X=0

Change the mark type to circle and add Self-sufficiency ratio… and Prefecture to the Label shelf. Adjust the font style and align centrally. Set the colour of the circle to white and increase the size. Move the Zero marks card to be below the Spiral Point marks card.

Rename the marks cards if you wish.

Add the starting point map layer

We need to create a point for the start of each line which is at the 0% mark

Start Point

MAKEPOINT((IF [Path Percent Point] = 0 THEN [X] END), (IF [Path Percent Point] = 0 THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Colour the mark to the same base colour you used for your <100% range and remove the border. Rename the marks card to 3.Start Point

Add the end point map layers

Create a new point to represent the end of each line

End Point

MAKEPOINT((IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [X] END), (IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Add Colour-Spiral to the Colour shelf as a discrete dimension (blue disaggregated pill) and remove the border. Rename the marks card to 4.End Point Outer.

Add another instance of End Point as another marks layer. Again change the mark type to circle and adjust the size so it is smaller than the previous circle, and set the Colour to white. Rename the marks card to 5.End Point Inner.

Tidy up by

  • removing the Tooltip from each layer
  • disabling selection of each map layer (so nothing happens when you hover over it)
  • Hide the axis
  • Remove axis rulers and gridlines, but make sure the zero lines are shown
  • Hide the null indicator

Name the sheet Single Spiral or similar.

Building the trellis

Duplicate the single spiral (so if things go awry, you can get back to this). Then start by adding Prefecture to the Detail sheet of all the marks card.

When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created

Cols

INT((INDEX()-1)%10)

Rows

INT((INDEX()-1)/10)

and make both fields discrete.

Add Cols to Columns and Rows to Rows. Adjust the table calculation setting of each field so it is computing by Prefecture only, and custom sorted descending by Self-sufficiency ratio…

Then show the Prefecture filter and select all values to display the ordered set of Prefectures.

Hide the Rows and Cols fields, remove row & column dividers. Adjust the size of the start and end point circles to suit, and if the zero lines aren’t showing, reduce the size of the label circle map layer and fit to Entire View.

Then name this sheet Trellis or similar and add to the dashboard.

My published viz is here

Happy vizzin’!

Donna

Let’s practice Table Calcs!

Donna ColesLeave a comment

For this week’s #WOW2025 challenge, Sean went ‘back to basics’ with a focus on table calculations.

Let’s jump right in.

After connecting to the data, add Team to Rows and Timestamp as a continuous (green) pill at the Day level to Columns. Create a new field

Call Count

COUNT([synthetic_call_center_data.csv])

and then add this to Rows.

Add Timestamp to Filter, and select relative date. Set the options to Last 3 weeks, and then additionally check the anchor relative to check box and enter 23 Dec 2024. This is because the data set only goes up to the end of December 2024. Not setting this field will apply the date filter based on ‘today’ so it’s unlikely anything will appear.

(Note – you may also need to update the date properties of the data set to ensure a week starts on a Sunday to get matching numbers: right click the data source and select the date properties option).

To add a marker to the last point, create a field

Call Count – Most Recent

IF LAST()=0 THEN [Call Count] END

Add this to Rows and adjust the table calc setting so it is computing specifically by Day of Timestamp only. By default it was doing this via the Table (across) option, but I tend to always prefer to always explicitly fix what the calculation is computing over, as it won’t then matter where I then move that field too if I choose to change the layout of the viz.

Set the mark type on the Call Count marks card to line, and then adjust the colour to grey and reduce the size. Set the mark type of the Call Count – Most Recent marks card to circle, set the colour to blue and increase the size. Hide the null indicator (right click > hide).

Set the chart to dual axis, synchronise the axis and then remove the Measure Names field from the All marks card.

remove both the axis titles (right click axis > edit axis), hide the right hand axis (right click, untick show header), and format to remove the column divider from the header section only.

Now we’ve got the core display, we need to create the following fields

No. of Calls

WINDOW_SUM([Call Count])

Highest Call Vol

WINDOW_MAX([Call Count])

Lowest Call Vol

WINDOW_MIN([Call Count])

Avg Call Vol

WINDOW_AVG([Call Count])

format this to a number with 0 dp

Calls this period

WINDOW_MAX([Call Count – Most Recent])

the window_max is required here, as the data set we’re displaying at the day level, has 2 values – the latest value and null. We only want to return 1 value, which is the maximum of these.

Previous Period

WINDOW_MAX(IF LAST()=1 THEN [Call Count] END)

LAST()=1 returns the value of the next to last record, and the window_max is again applied, as the nested IF clause will return null for all others records.

Period Var

[Calls this period] – [Previous Period]

Add each of these fields, one by one, to Rows following the steps below

  • Add to rows (it will automatically display as a green continuous pill).
  • change to discrete (right click on the pill and select discrete – the pill will turn blue and move to before the green pills)
  • Explicitly set the table calc to be computing by Timestamp (as above)

Once, you should have something that looks like this

but I noticed, that the display in the solution is sorted based on the total number of calls and not by Team, so add a Sort to the Team pill to sort by Call Count descending

Update the Tooltip if you wish, and then add the viz to a dashboard, floating the Timestamp filter.

My published viz is here.

Happy vizzin’!

Donna

Can you recreate this marginal histogram?

Donna ColesLeave a comment

For this week’s challenge, Lorna visualised the output of a crazy golf game she had with the latest Data School cohort, and thought it would be a fun challenge to share.

While the data set is very small, there’s several core calculations required, which we’ll start off creating.

Defining the core calculations

After connecting to the data, I initially dragged the Hole field from Measures to Dimensions (dragged it to be above the line on the data pane).

We need to know the average score per person (across all 11 holes)

Overall Average Score

{FIXED [Person]: AVG([Score])}

format this to 1dp

We also need the average score per hole

Avg Score Per Hole

{FIXED [Hole]: AVG([Score])}

format this to 1 dp

We need each person’s score up to 9 holes

Score up to 9

{FIXED [Person]:SUM( IF [Hole]<=9 THEN [Score] END)}

We need the tie break score for the 10th & 11th holes

Tie Break

{FIXED [Person]:SUM( IF [Hole]>9 THEN [Score] ELSE 0 END)}

We need the average score across 9 holes

Avg Score

[Score up to 9]/9

format to 1 dp

We need to the lowest score per person

Lowest

{FIXED [Person]: MIN([Score])}

With this information, we can then define a field we can use for sorting which is a numeric field that is a combination of multiple fields as stated in the requirements

Sort

(SUM([Score up to 9])*10000) +
(SUM([Tie Break]) * 1000) +
(SUM([Avg Score]) * 100)+
SUM([Lowest])

Building the Heat Map

On a new sheet add Score up to 9 to Rows and change it to be discrete. Repeat the same for Tie Break, Avg Score and Lowest. Then add Person to Rows and Hole to Columns.

Double click into Columns and manually type MIN(1.0) to create a ‘fake axis’. Change the mark type to bar and increase the Size to the maximum. Edit the axis to be fixed from 0 to 1 and the hide the axis. Manually increase the width of each row and set the chart to fit width.

Add Score to Colour and choose a diverging colour palette and then manually change the start & end colours to match the solution (or define your own colour style). I used #62a14b (green) and #13316d (dark blue). Check the box to use full colour range

Update the Tooltip as required.

We then need to include some indicators on the heat map. To determine the arrow indicator we first need

Previous Score

LOOKUP(SUM([Score]),-1)

and then create

Change Indicator

IF SUM([Score]) > [Previous Score] THEN ‘↗’
ELSEIF SUM([Score]) < [Previous Score] THEN ‘↘’
ELSEIF SUM([Score]) = [Previous Score] THEN ‘→’
END

(I sourced the arrow characters from https://jrgraphix.net/r/Unicode/2190-21FF)

Add Change Indicator to the Label shelf and then adjust the table calculation so it is computing by the Hole field only

We also need

Hole in 1 indicator

IF SUM([Score]) = 1 AND MIN([Hole])<> 9 THEN ‘●’ END

and

Max Shots Indicator

IF SUM([Score]) = 10 THEN ‘🙁’ END

(I used this site https://unicode-explorer.com/emoji/ for the emoji icon)

Add these to Label too and adjust label so it is aligned bottom centre, and coloured white. You may need to adjust size of font for each label item, and increase the height of each row to get the information to display.

Finalise the formatting by

  • Set the background colour of the whole sheet to #7299aa
  • Set the font of all the column headings and label headings to Tableau Medium, 9pt, white
  • Hide the Hole label heading (right click the label and hide field labels for columns)
  • Add a white border to the bars via the Colour shelf
  • Remove all column dividers
  • Remove row dividers from the pane only, ensure the row dividers for the headers remain.
  • Remove all gridlines , zero lines, axis rulers & ticks

Name the sheet HeatMap or similar.

Building the Average Score Per Person bar chart

On a new sheet, add Person to Rows and Overall Avg Score to Columns. Add a Sort to the Person pill that references the Sort field ascending

Add Overall Avg Score to the Colour shelf, and then adjust the colour scale so it uses a diverging colour scale which is then edited to use the same colour range as before and spans the same range, which means explicitly stating the start (-1) , middle (0) and end (10) values

We want to display a * against the winner

Winner Icon

IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘★’ ELSE ” END

Add this to Rows and adjust the table calculation so it is explicitly computing by Person only.

Create

Winner Label

IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘Coach Always Wins!’ END

and add this to Label along with Overall Avg Score. Increase the width of the bars to see the text and align middle left and change the font to white. Adjust the Tooltip.

Format the sheet

  • Set the worksheet background colour
  • Hide the Person column (uncheck Show Header)
  • Hide the Overall Avg Score axis (uncheck Show Header)
  • Remove all column/row dividers, gridlines, axis lines, zero lines
  • Add a white border around the bars (via the Colour shelf)
  • Increase the Size of the bars so there is a small gap between
  • Format the * to be white font
  • Hide the Winner Icon label (right click > hide field labels for rows)
  • Make the Winner Icon column as narrow as possible

Name the Sheet Avg Per Person or similar.

Building the Average Score Per Hole bar chart

On a new sheet, add Hole to Columns and Avg Score Per Hole to Rows. Add Avg Score Per Hole to Colour and adjust the scale as we did above so it ranges from green to blue and -1 to 10.

Show mark labels, adjust the tooltip. Set the background colour of the worksheet and remove all gridlines, zero lines, row/column dividers etc. Hide the Avg Score Per Hole axis and the hole labels (uncheck show header on the Hole pill). Adjust the font of the labels to be Tableau Medium and white. Add a white border around the bars. Increase the Size to leave a small gap.

Name the sheet Avg Per Hole or similar.

Building the dashboard

Set the background of the dashboard to the same colour of the worksheets.

Using containers, add a horizontal container and add a text object (for the title), the Avg Per Hole sheet and then a blank object. Remove the container that gets added with the colour legend.

Add another Horiztonal container beneath and add the Heatmap sheet and the Avg Per Person sheet.

Remove all padding. Remove all titles from the sheets. Set all the charts to fit entire view. Manually line up everything, but you’ll find you have an issue getting your horizontal bar chart to align to the player rows due to the header in the heatmap

(Note – you may notice your labels on the heat map aren’t displaying due to the space available). You can continue to adjust on Desktop so they do appear (make the heatmap have more vertical space), or wait until you’ve published to Tableau Public and see if you get the desired result… although at the point of writing , Tableau Public is having issues with the table calcs and causing odd behaviour with the display).

To fix this, go back to the Avg Per Person sheet and double click into column and manually type “” to create a dummy header row with no text. Hide the “” label (right click > hide field labels for columns). You can then adjust the height of this header label section to help get the alignment right.

Then make any final adjustments required – add the title, and any imagery etc. My published viz is here.

Happy vizzin’!

Donna

Can you build an alternative to a stacked bar chart?

Donna Coles2 Comments

For this week’s challenge we’re going to look at building an alternative to a stacked bar chart. This challenge was inspired by this post by the Flerlage twins, which in turn was inspired by other members of the #datafam community. Once again, we’re making use of some Premier League data. 

Building the viz

Add Team to filter and select Manchester United, Chelsea, Arsenal and Manchester City then add Season to filter and select the last five seasons. Then add Team and Season to Columns and Points to Rows.

Sort team by the field Points descending. Add Team to colour and adjust accordingly. Show Mark labels to display the number of points for each bar.

Add subtotals via the analysis menu >  totals > show all subtotals

Hide the subtotal bar by clicking on one of the total bars and selecting hide from the drop down which is defaulted to automatic


Right click on the Total label on the axes and select format – in the left hand side, delete the label field

Create a new calculated field

Total Points

WINDOW_SUM(SUM([Points]))

And add this to rows. Remove Team from the colour shelf on the marks card, and change the colour to pale grey. Increase the size of the bars to be as wide as possible. Uncheck show mark labels. Hide the total bar like we did above.

Make the chart dual axis and synchronise axis. If need be, right click the right axis and move marks to back. Adjust the table calculation on Total Points so it is computing by Season only. Hide the total bar again if it reappears.


We want to label the total bar with the Team and the Total Points so we need to create some more calculated fields

Label: Team 

IF [Season] = ‘2021-22’ THEN [Team] ELSE NULL END

We’re being sneaky here, and just labelling the central bar.

Label: Total Points

IF MIN([Season]) = ‘2021-22’ THEN [Total Points] END

Add Label: Team to the label shelf of the Total Points marks card and adjust so it is an attribute – this means it’s not referenced in any table calculations. Add Label: Total Points to the label shelf too.

Adjust the label as required and then change the alignment so the direction of the text is swapped. Format the font as desired.

Delete all the text from the Tooltip on the Total Points marks card and adjust the text on the Points marks card to match the required format. 

Hide the right hand axes (right click axis > uncheck show header). Hide the Team column heading. Remove all gridlines/ axis lines/ zero lines  and row and column dividers. 

Hide the Season label heading (right click on the label > hide field labels for columns).

Format the axes font to be smaller and rotate the axes labels on the Season axis. 

Name the sheet Bar chart or similar and add to dashboard.

My published viz is here

Happy vizzin’!

Donna

Competitor Comparison

Donna ColesLeave a comment

Erica set this week’s challenge, focusing on the ability to compare specific entities against themselves and ‘the whole’ without resulting in a mess of coloured spaghetti. 3 levels of difficulty were provided. As it stated the levels didn’t necessarily follow on from each, I just built (and am therefore blogging about) level 3 – the advanced challenge.

Defining the core parameters

For the user to select the main element they want to analyse we need

pPrimarySubCat

string parameter, that is sourced from a List based on the Sub-Category field when the workbook opens. Default to Binders.

This parameter will be visible to the user to select from a drop down list control.

To capture the secondary element to compare against, we need

pSecondarySubCat

string parameter defaulted to Bookcases.

This is just a ‘type in’ field, that won’t ultimately be displayed to the user, but populated via a dashboard parameter action on select of a line in the chart.

To control the different type of display options, we need

pDisplay

integer parameter sourced from a manual list which aliases the integer values for the displayed text strings. Defaulted to 2 (Difference from Primary)

Defining the additional calculations

As I often do, we’ll build out a tabular display to determine all the calcs required. On a new sheet, add Region and Sub-Category to Rows, then add Order Date at the Quarter level as a discrete (blue) pill to Columns. Add Sales to Text. Show the 3 parameters created above.

We need to identify which Sub-Categories will be coloured. This is based on whether they are a primary or secondary Sub-Category.

Is Primary or Secondary Sub Cat

[pPrimarySubCat] = [Sub-Category] OR [pSecondarySubCat] = [Sub-Category]

Add this to Rows. Based on existing selections, the rows for Binders and Bookcases should be set to True.

We will also need to identify which is the the Primary Sub-Category only to help determine how many rows are displayed, so create

Is Primary SubCat?

[pPrimarySubCat] = [Sub-Category]

Add to rows. In this case just Binders should be True at this point.

With this field, we can then work out how many ‘rows’ are going to be in our final viz display.

Display Row

IIF([pDisplay] = 0, TRUE, [Is Primary SubCat?])

ie, if the pDisplay parameter is ‘Raw values – overlay’ , then we’ll just display 1 row (so all rows set to True), otherwise there will be 2 rows, split based on whether the Sub-Category is the selected value in the pPrimarySubCat parameter or not.

Add this to Rows, and change the pDisplay parameter to see how this field changes.

We also need to display different values depending on what pDisplay option is selected. When the ‘Difference from Primary’ option is selected, then we need to show the Sales value for the primary Sub Category, but the difference from this value for all others. For this we first need to capture just the sales for the primary Sub-Category

Sales For Primary Sub Cat

IF [Is Primary SubCat?] THEN [Sales] END

Add to the table and adjust Measure Names so it is displayed after the Order Date field. Rows for this column will only have values when the Sub-Category is the primary one selected.

Now we calculate the difference, but only if it’s not the primary Sub-Category; we want Sales in that instance

Sales Difference

IF MIN([Is Primary SubCat?]) THEN SUM([Sales])
ELSE SUM([Sales]) – WINDOW_MAX(SUM([Sales For Primary Sub Cat]))
END

Here we’re using a WINDOW_MAX table calc to essentially ‘spread’ the value in the Sales for Primary Sub Cat column across all rows associated to the Region. Add this to the table, and adjust the table calculation setting of the pill, so it is computing by all fields except Region and Order Date

Finally, we need a field that will decide whether we’re displaying Sales or Sales Difference based on the pDisplay selection

Value to Display

IIF([pDisplay]=2, [Sales Difference ], SUM([Sales]))

Again, add to the table, adjust the table calc as above and then test the output of the field, as you adjust the pDisplay parameter.

While we’re here, we’ll just define another couple of calcs needed for the viz

Label Sub Cat

IF [Is Primary or Secondary Sub Cat] THEN [Sub-Category] END

Used to only display a label for either of the two selected Sub-Categories.

Tooltip – Value Label

IIF([pDisplay]=2 AND NOT([Is Primary SubCat?]), “Difference from ” + [pPrimarySubCat] + ” Sales”, “Sales”)

Will be used on the Tooltip to ensure the correct text is displayed depending on type of display selected.

Building the Viz

On a new sheet, show the 3 parameters and set them to the defaults (ie Binders, Bookcases and Difference from Primary).

Add Region to Columns, then add Order Date at the Quarter level as a continuous (green) pill to Columns. Add Display Row to Rows and adjust the Sort on the pill to be a manual sort, where True is listed first. Add Sub-Category to Detail, then add Value to Display to Rows and adjust the table calc so all fields except from Region and Order Date are selected.

Add Is Primary or Secondary Sub Cat to Colour. Some lines will disappear, but don’t worry. Then add Region to Detail, and then select the ‘detail’ icon to the left of the pill on the marks shelf, and change it to Colour so 2 pills are now on the Colour shelf. Adjust the table calculation setting of the Value to Display pill to ensure the Is Primary or Secondary Sub Cat field is also now checked – this should make all the lines reappear.

Then adjust the colours in the colour legend so all the entries that start ‘False’ are grey and the others are as required.

Adjust the sort on the Is Primary or Secondary Sub Cat pill on the marks card, so it is manually sorted with True first. This ensures the coloured lines are ‘on top’ and always visible. Add Is Primary SubCat? to Size shelf. Readjust the table calc on Value to Display again, and then adjust the Size so it is visibly thicker than the rest of the lines, which will probably be by adjusting both the range in the Size legend, and adjusting the slider on the Size shelf.

Add Label Sub Cat to the Label shelf (adjust table calc again), and set label to allow labels to overlap other marks. Add Tooltip – Value Label to tooltip and update the Tooltip as required

Add a reference line to the Value to Display axis, and set to be a constant of 0 displayed as a black dashed line

Edit both axis to update the axis titles on each, hide the Display Row pill (uncheck show header on the pill) and hide the Region column label (right click > hide field labels for columns).

Building the dashboard

Use layout containers to construct the dashboard as required

Create a dashboard parameter action to capture the value of the secondary Sub-Category

Set Second Sub Cat

On select of the Viz, set the pSecondarySubCat parameter with the value sourced from the Sub-Category field. When selection is cleared, set it <none>

Clicking one of the grey lines should now change the comparison Sub-Category. But you’ll notice the rest of the unselected lines are ‘faded’ and your selection is ‘highlighted’. We don’t want this to happen. To resolve, create new calculated field

HL

‘Dummy’

and add to the Detail shelf on the viz sheet itself.

Then add a dashboard highlight action

Un-Highlight

On selection of the Viz sheet on the dashboard, target the viz sheet on the dashboard, selecting the HL field only.

As all the marks have the HL ‘dummy’ field associated to them, they all become ‘highlighted’, giving the appearance of nothing actually being highlighted.

Finally, we need to make the title of the dashboard ‘dynamic’ and reflective of the selections made in the primary and secondary Sub-Category parameters. But the secondary one can be empty, so the text needs to handle this. An additional ‘ and ‘ needs to display if the secondary Sub-Category is set. I chose to use a parameter to help with this, as text objects on a dashboard can reference parameters.

Create a new parameter

pTitle-and

string field defaulted to the text <space>and<space>

Create a calculated field

Param-and

‘ and ‘

and add to the Detail shelf on the viz. Set it to be an attribute (this won’t impact the table calc).

Back on the dashboard, create another dashboard parameter action

Set ‘and’

on select of the Viz, set the pTitle-and parameter passing in the value from the Param-and field. When the selection is cleared, set to <none>.

Then create (or adjust) the title text object so it references the relevant parameters (notice the spacing – or lack of – between some of the fields)

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

Happy vizzin’!

Donna

Can you create a control chart?

Donna ColesLeave a comment

Sean set this table calculation based challenge this week. The key to this was for the reference line & bands to change if any of the non-date related filters were change, but for the values to remain constant regardless of the timeframe being reported. Based on this, I figured a table calculation filter would be necessary for the timeframe filter, as these get applied at the end of the ‘order of operations’.

I used the data set Sean provided in the challenge, to ensure I would get the same values to verify my solution. After connecting to the dataset, I did set the date properties on the data source so the week started on a Sunday, again to ensure the numbers aligned (in the UK, my preferences are set to Mondays).

Building out the calculations

Since we’re working with table calcs, I’m going to build the calcs into a table first, so we can verify the figures are behaving. Start by adding Order Date to Rows as a discrete (blue) pill at the Week level. Then add Sales.

Create a new field

Median

WINDOW_MEDIAN(SUM([Sales]))

and add this into the table. The value should the same for every row.

Create a new field

Std D

WINDOW_STDEV(SUM([Sales]))

and add this too – again this should be the same for every row.

But we want to to show the distribution based on +1 or -1 standard deviations, so create

Std Upper

WINDOW_AVG(SUM([Sales])) + [Std D]

and Std Lower

WINDOW_AVG(SUM([Sales])) – [Std D]

and add these to the table.

Each mark needs to be coloured based on whether it is greater than the upper band, so create

Sales above Std Upper

SUM([Sales]) > [Std Upper]

and add to the table on Rows

Add Category, Sub-Category and Segment to the Filter shelf, and show the filters. Adjust them to see that the table calculation fields change, although still remain the same across every row.

To restrict the timeline displayed, we need a parameter

pWeeks

integer parameter defaulted to 18

Show this parameter on the canvas. Then, to identify the weeks to keep, create a new field

Index

INDEX()

and convert it to discrete, then add to Rows to create a ‘row number’ for each row.

But, I want the index to start from 1 from the end of the table, so adjust the tableau calculation on the Index field so that is it computing explicitly by Week of Order Date and is sorted by Min Order Date descending

The first row in the table is now indexed with the greatest number, and if you scroll down, the last row should be indexed with 1.

With this we can now created

Filter – Date

[Index]<=[pWeeks]+1 AND [Index]<>1

I was expecting just to have [Index}<=pWeeks, but Sean’s solution excluded the data associated to the last week, I assume as it wasn’t a full week, so the above calculation resolved that.

Add this to the Filter shelf and set to True.

Changing the value in the pWeeks parameter only should adjust the number of rows displayed, but the values for the table calculated fields shouldn’t change.

Building the viz

On a new sheet, add Order Date as a continuous (green) pill at the week level to Columns and Sales to Rows.

Add another instance of Sales to Rows. Change the mark type of the Sales(2) marks card to circle. Make the chart dual axis and synchronise axis.

On the All marks card, add Median, Std Upper and Std Lower to the Detail shelf.

Add a reference line to the Sales axis, referencing the Median value. Set it to be a thicker darker line.

Then add another reference line but this time set it to be a band and reference the Std Lower and Std Upper fields, selecting a pale grey fill.

On the Sales(2) marks card, add Sales above Std Upper to the Colour shelf, and adjust to suit. Add a border to the circles. If required, adjust the colour and size of the line on the Sales marks card too.

Add the Category, Sub-Category and Segment fields to the Filter shelf, and show the filters. Show the pWeeks parameter. Then add Filter-Date to the Filter shelf. Adjust the table calculation as described above, then re-edit the filter to just show the True values

Finally tidy up by

  • remove row and column dividers
  • hide the right hand axis (uncheck show header)
  • edit the date axis and delete the title

Then add all the information to a dashboard and you’re good to go!

My published viz is here.

Happy vizzin’!

Donna

Data Normalisation Methods

Donna ColesLeave a comment

Erica set this fun and incredibly useful challenge this week, based on the TC25 talk by Lorna Brown & Robbin Vernooij, to showcase different methods of normalising data when comparing measures which have drastically different scales.

Building the Raw Values chart

Add Sales to Rows. Then drag Quantity on to the canvas and drop the pill on the Sales axis (when you see the ‘2 column’ icon appear). This has the affect of adding the fields onto a shared axis, and the sheet will update to automatically reference Measure Names and Measure Values. Swap Quantity so it is displayed below Sales in the Measure Values section.

Add Region and Category to Detail and change the Mark type to Circle.

I’m going to incorporate the last requirement at this stage, as it helps with the build, so create parameters

pSelectedRegion

string parameter, defaulted to West

pSelectedCategory

string parameter, defaulted to Furntiture

show both these parameters on the sheet.

Create a new field

Is Selected Region & Category

[pSelectedCategory]=[Category] AND [pSelectedRegion]=[Region]

Add this field to Colour, and swap the values in the legend, so True is listed first. Then change the Region on the Detail shelf, so it is also on colour, by adjusting the icon to the left of the pill. Adjust the colours as required and then reduce the opacity on colour to 80%.

Manually update the entry in the pSelectedRegion parameter to each Region, so the True-<Region> colour combination can be updated to the dark grey.

Add Is Selected Region & Category to Size. Edit the size so they are reversed and the range in size is closer than the default. Once done, then manually adjust the dial on the Size shelf.

Show mark labels, selecting the option to only show the min & max values per cell and aligning middle right

Update the Tooltip. Then create fields True = TRUE and False = FALSE and add both of these to the Detail shelf. We’ll need these to disable the default highlighting later (adding now, as for all the other sheets, we’ll duplicate this one, so makes things easier).

Show the caption (Worksheet menu > show caption) and update the caption to reference the website Erica refers to. Then update the title of the sheet, and name the tab Raw or similar.

Building the Decimal Normalisation chart

Duplicate the Raw sheet, and name Decimal or similar. Update the title.

Create new fields

Sales – Decimal

SUM([Sales]) / 10^6

Quantity – Decimal

SUM([Quantity]) / 10^4

Drag Sales – Decimal onto the canvas and drop directly over the existing Sales pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Decimal pill. Uncheck Show Labels.

Add constant reference line of 0 that displays as a black solid line at 100% opacity

Repeat and create a constant reference line with value of 1. Edit the axis and fix from -0.05 to 1.05 and remove the axis title.

Update the text in the caption.

Building the Max-Min Normalisation Chart

Duplicate the Decimal sheet and rename Max-Min or similar. Update the title.

Create new fields

Sales – Max-Min

(SUM([Sales])- WINDOW_MIN(SUM(Sales))) / (WINDOW_MAX(SUM(Sales)) – WINDOW_MIN(SUM(Sales)))

Quantity – Max-Min

(SUM([Quantity])- WINDOW_MIN(SUM([Quantity]))) / (WINDOW_MAX(SUM([Quantity])) – WINDOW_MIN(SUM([Quantity])))

Drag Sales – Max-Min onto the canvas and drop directly over the existing Sales – Decimal pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Max-Min pill.

Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category.

Adjust the Tooltip if required. Right click on the bottom column headings and Edit Alias to update the text- you may not be able to rename Sales – Max-Min along xyz… just to ‘Sales’, so you may need to be creative and add spaces eg ‘ Sales ‘ or similar. Update the caption.

Building the Z-Score Normalisation Chart

Duplicate the Max-Min sheet, and name Z-Score or similar. Update the title.

Create new fields

Sales – Z-Score

(SUM([Sales]) – WINDOW_AVG(SUM([Sales]))) / WINDOW_STDEV(SUM([Sales]))

Quantity – Z-Score

(SUM([Quantity]) – WINDOW_AVG(SUM([Quantity]))) / WINDOW_STDEV(SUM([Quantity]))

Drag Sales – Z-Score onto the canvas and drop directly over the existing Sales – Max-Min pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Z-Score pill.

Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category. Remove the reference line for the constant value of 1. Edit the axis, so the range is now Automatic rather than fixed.

As before, adjust the Tooltip again if required, edit the column labels using the alias feature, and update the caption.

Creating the dashboard and adding the interactivity

Add all 4 charts onto a dashboard, using a horizontal container to arrange the charts side by side. From the object context menu on the dashboard, select the option to show the caption

To disable the default highlighting ‘on click’ create a dashboard filter action based on the True/False method described here – you’ll need to create an action per sheet.

To set the parameters, create a parameter action

Set Category

On select of all the sheets, set the pSelectedCategory parameter passing in the value from the Category field.

Create another similar action called Set Region which sets the pSelectedRegion parameter with the value from the region field.

Finally, add a text section to the top right of the dashboard that references the pSelectedRegion and pSelectedCategory parameters.

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

Happy vizzin’!

Donna

Can you make a drill down chart?

Donna ColesLeave a comment

Yusuke set the #WOW2025 challenge this week, asking us to build a chart that was drill downable and drill uppable 🙂

I had a fair idea of how this was going to play out, knowing it would involve parameter actions and built the main table fairly quickly. Then it came to the parameter actions, and defining the logic to get them set to the right values. This was very tricky, and I confess I couldn’t completely manage it. The behaviour just wasn’t doing what I wanted 😦

So I looked at Yusuke’s solution, and even after using the exact same logic, field naming and parameter actions (including the names of these), it still wouldn’t quite do what Yusuke’s did. At the point the bars are expanded down to Manufacturer, if a Category is selected, Yusuke’s solution collapses back to the Category > Sub-Category level. Mine expands down to Manufacturer for the Sub-Category listed first (see below).

I spent a considerable amount of time trying to get this to work. Ultimately, I believe it’s something to do with the order in which the parameter actions get applied. In Yusuke’s solution, there are 4 parameter actions firing on each ‘click’, but they only affect 2 parameters. So one change it being applied before the other. But figuring out the order is tricky. From what I understand, actions of the same type (ie all parameter actions as opposed to filter actions, or set actions say), are applied based on alphabetical order. But, as I say, I tried naming my actions exactly like Yusuke’s (even copying and pasting from his solution), and I still couldn’t get his behaviour, and with all 4 actions applied, the drill-down from Sub-Category to Manufacturer didn’t work at all. I couldn’t get my actions to be displayed in the same order as Yusuke’s solution either, even by removing them and then adding them in the order listed, when I closed the dialog and re-opened, the order changed. So, as a result of this, my solution only has 3 actions and doesn’t quite behave exactly like Yusuke’s…. maybe I missed a tiny detail.. who knows, or maybe it’s just Tableau and some quirk in how things get applied…

Anyway, now I’ve said all that, let’s get on to the solution I did manage 🙂

Building out the calculations

For a challenge like this, I’m going to build out all my calculations into tabular form, so I can get the display and sorting as required, especially since table calculations are involved.

We need to capture the selections made ‘on click’ into parameters

pSelectedCategory

string parameter, defaulted to Furniture

pSelectedSubCat

string parameter, defaulted to Bookcases

The Sub-Category and Manufacturer to display will be based on the values in these parameters

Display – Sub Cat

IIF([pSelectedCategory] = [Category], [Sub-Category],”)

Display – Manufacturer

IIF([pSelectedSubCat] = [Sub-Category], [Manufacturer], ”)

On a sheet, add Category, Display – Sub Cat, and Display – Manufacturer to Rows and show the two parameters

If you change the values in the parameters, you’ll see how the display changes.

We want to get the total sales for each ‘level of the hierarchy’, so we can the compute the % sales, and apply sorting. We’ll used Fixed Level of Detail calculations for this.

Sales per Category

{FIXED [Category]: SUM([Sales])}

Sales per Sub-Category

{FIXED [Category], [Sub-Category]: SUM([Sales])}

Sales per Manufacturer

{FIXED [Category], [Sub-Category], [Manufacturer]: SUM([Sales])}

Format all these to $ with 0 dp, add into the table and note how the values are duplicated across each row, depending on what ‘level of the hierarchy’ we’re looking at

Adjust the sort on the Category pill, to sort by Sales per Category descending – this will move the Technology row to the top.

Sort the Display – Sub Cat pill to sort by Sales per Sub-Category descending and sort the Display – Manufacturer pill to sort by Sales per Manufacturer descending.

With these fields, we can calculate the % of sales

% Sales per Category

SUM([Sales per Category]) / SUM({FIXED:SUM([Sales])})

% Sales per Sub-Category

SUM([Sales per Sub-Category]) / SUM([Sales per Category])

% Sales per Manufacturer

SUM([Sales per Manufacturer]) / SUM([Sales per Sub-Category])

format all these to % with 1 dp and add into the table

For the final display, we don’t want values in every row. We need values displayed at the first row of every level of the hierarchy. I’m going to use the INDEX() tableau calculation to help with this.

Index – Category

INDEX()

Index – Sub-Category

INDEX()

Make both of these fields discrete (right click > convert to discrete).

Add Index – Category to Rows to the right of the Category pill. Adjust the table calculation on the pill so it is computing using Display – Sub Cat and Display – Manufacturer only. This should index the rows so that the numbering restarts when the Category changes.

Add Index – Sub-Category to Rows to the right of the Display – Sub Cat pill. Adjust the table calculation on the pill so it is computing using Display – Manufacturer only. This should index the rows so that the numbering restarts when the Sub–Category changes.

We can then use this information to determine which rows need to display the % Sales values.

Display – % Sales per Category

IIF([Index – Category] = 1, [% Sales per Category],NULL)

Display – % Sales per Sub-Category

IIF([Index – Sub-Category] = 1 AND MIN([Category]) = [pSelectedCategory], [% Sales per Sub-Category],NULL)

Display – % Sales per Manufacturer

IIF(MIN([Sub-Category]) = [pSelectedSubCat], [% Sales per Manufacturer],NULL)

format all these to % with 1 dp, and add to the table (sense check that the table calculations for each field have the settings we applied to the Index fields above.

These 3 fields, are the core fields we need to use in the viz.

Building the Viz

On a new sheet, add Category, Display – Sub Cat, Display – Manufacturer to Rows and apply the sorting on each pill described above, and how the parameters.

Add Display – % Sales per Category to Columns and apply the table calculation settings described above. Add Display – % Sales per Sub-Category to Columns too, and again apply the table calc settings. Then add Display – % Sales per Manufacturer to Columns. Change the mark type on each of the 3 marks cards, specifically to use bar.

Add Category to the Colour shelf on the All marks card, and adjust accordingly.

On the Display – % Sales per Category marks card, add Category and Display – % Sales per Category to Label. Adjust the table calc settings of the % field as required. Adjust the layout of the label. Add Sales per Category to Tooltip and update the tooltip to suit.

On the Display – % Sales per Sub-Category marks card, add Display – Sub Cat and Display – % Sales per Sub-Category to Label. Adjust the table calc settings of the % field as required. Adjust the layout of the label. Add Sales per Sub-Category to Tooltip and update the tooltip to suit.

On the Display – % Sales per Manufacturer marks card, add Display – Manufacturer and Display – % Sales per Manufacturer to Label. Adjust the layout of the label. Add Sales per Manufacturer to Tooltip and update the tooltip to suit.

You may need to widen each row to see the labels displayed.

The axis titles on the top of the chart adjust based on the selections made. To present this within the chart itself (rather than using carefully positioned text fields on a dashboard), we need to make the chart dual axis using ‘fake axes’.

Double click into the space in Columns to the right of the last pill, and manually type MIN(0). Drag this field to sit between Display – % Sales per Sub-Category and Display – % Sales per Manufacturer.

Remove all pills from the MIN(0) marks card. Change the mark type to Shape and select a transparent shape for this (refer to this article to set this up – you can also use any other type of mark but set to very small, and 0% opacity on the colour to make it “invisible”, though a mark could appear on hover, which is why I prefer to use transparent shapes).

Click on the MIN(0) pill and set it to be dual axis, so 2nd column now has a MIN(0) axis heading.

Right click on this top axis, to Edit the axis – Change the Title to reference the pSelectedCategory parameter and set the tTck Marks to None

Repeat the process, creating another instance of MIN(0) to the right of the Display – % Sales by Manufacturer, but this time the axis title should reference the pSelectedSubCat field.

Tidy up the display formatting by

  • Add row banding with Band Size = 1 and Level = 0, so the whole of the Furniture block is coloured grey.
  • Remove column dividers
  • Remove gridlines and zero lines
  • Hide the 3 pills on the Rows (right click each pill and uncheck show header).
  • Hide the null indicator (right click > hide)
  • Edit the bottom 3 axis to remove the titles and hide the tick marks on all
  • Make the axis heading section narrower
  • Add a border around each of the bars, and make each bar narrower if required
  • Add some space to the start of each bar, by adjusting the bottom axis to be fixed from -0.1 to 1

Update the title of the sheet, and name the sheet.

Adding the interactivity

Add the sheet to a dashboard.

Firstly, we’re going to stop the bars from being ‘highlighted’ when clicked. we’ll use the True/False filter action technique described here. Create 2 calculated fields True = TRUE and False = FALSE and add to the Detail shelf on the All marks card of the viz. Add a dashboard filter action

Deselect Marks

On select of the the Viz sheet on the dashboard, target the Viz sheet directly, setting True = False.

Now we need to deal with the parameter values. As I discussed at the start of this blog, getting the calculations required and making the functionality work was pretty tricky, so I’m just going to document what I’ve ended up using, that seems to mostly work. Note the names of the parameter actions which both affect the pSelectedCategory param are pre-fixed with a number to force the order (I did test with them the other way round, and things broke).

Create fields

Category for Param_1

IF (([pSelectedCategory]<> [Category]) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Display – Sub Cat]) AND ISNULL([Display – Manufacturer])) THEN ‘ ! Please select a Category !’
ELSE [Category]
END

Category for Param_2

[Category]

SubCat for Param

IF (([pSelectedCategory]<> [Category]) OR([pSelectedSubCat]=[Display – Sub Cat])) AND NOT(ISNULL([Display – Manufacturer])) THEN ‘ ! Please select a Sub-Category !’
ELSE [Display – Sub Cat]
END

Then create 3 parameter actions

Set SubCategory

On select of the viz, set the pSelectedSubCat parameter passing in the value from the SubCat for Param field.

1. Set Category

On select of the viz, set the pSelectedCategory parameter passing in the value from the Category for Param_1 field.

2. Set Category

On select of the viz, set the pSelectedCategory parameter passing in the value from the Category for Param_2 field.

And fingers crossed, that should work, at least work the same as mine… my published viz is here. Note – when I uploaded to Tableau Public, the dynamic axes seemed to break, so I had to manually reset them on public…. or it may have broken before I published and didn’t realise.. the feature does seem to be a bit temperamental.

Happy vizzin’!

Donna

Let’s practice using the multi-fact relationship model

Donna ColesLeave a comment

Yoshi chose to challenge us this week to try using the multi-fact relationship model, something I hadn’t used before so I was keen to have a go.

I have to admit, I did ultimately find the challenge a bit tricky… I did have to look at the hint in the challenge to see how Yoshi had folded in the additional month data source, and then when building the vizzes, I was getting slightly different numbers on occasion. I wasn’t sure whether this was due to differences in how I’d applied the actual relationship fields between the tables in the model, but can’t actually get to Yoshi’s data model in the solution to tell. I used the information on the help article to determine the linking fields, so I think it’s right…

I also struggled with the final requirement – to always show all the libraries in the display, even when there were no copies of the selected book. As soon as I restricted the display to just show the data for the selected book, the libraries which didn’t stock the book disappeared, which with relationships is what I expected as they act like ‘inner joins’. Even looking at Yoshi’s solution, I can’t see what magic trick has been applied, unless there’s something in the model I haven’t applied..

So I’ll explain what I did, and if you can figure out where I may have gone wrong, or if you concur with my set up, then please let me know 🙂

Modelling the data

Download the 3 data sources from Yoshi’s link.

Connect to the Bookshop excel file. Drag Checkouts onto the canvas first – this is one of the base tables.

Drag Book onto the canvas and verify the tables are related by the Book ID field from each table.

Drag Author onto the canvas after Book and verify the tables are related on the Auth ID fields.

Drag Award onto the canvas after Book and verify the tables are related on the Title fields.

Drag Edition onto the canvas after Book and verify the tables are related on Book ID

Drag Info onto the canvas after Book. This time you’ll need to join Book ID from Book to a relationship calculation from Info of [BookID1]+[BookID2]

Drag Ratings onto the canvas after Book and verify the tables are related on Book ID

Add a new connection to the BookshopLibraries excel file and then drag in Catalog after Edition and verify the tables are related on ISBN

Drag in Library Profile after Catalog and verify the tables are related on Library ID

Next from the Bookshop excel data source, add Sales Q1 onto the canvas and drop when the New Base Table option appears

Multi select the Sales Q2, Sales Q3 and Sales Q4 tables then drag onto the canvas and drop onto the Sales Q1 table when the Union option appears

Right click on the Sales Q1 unioned object and rename to Sales. Add a relationship from Sales to Edition verifying the tables are related on ISBN.

Add a new connection to the Months csv file, then add the Month.csv object onto the canvas so it is related to the Sales table. This time you’ll need to create a relationship calculation on Sales as DATEPART(‘month’, [Sale Date]) and join to Month

Finally also add a relationship from Checkouts to Month.csv, creating a relationship on Checkout Month to Month.

Now we have a model, we can start building the vizzes.

Building the scatter plot

To start, to verify I’m getting the data I expect, I’ll build out a table

Add BookID (Book) and Title to Rows. We need to indicate if the book has won an award.

Awarded?

COUNT([Award])>0

Add this to Rows.

We will be plotting the average number of checkouts per month against the average number of orders per month., so create

Avg Checkouts per Month

SUM([Number of Checkouts])/COUNTD([Checkout Month])

Then create

Avg Orders per Month

COUNT([Order ID])/COUNTD(MONTH([Sale Date]))

Add into the table. The number won’t be correct though, as we are displaying data from the Checkouts base table and the Sales base table without including any field from any tables that link them. To resolve this add BookID (Edition) onto Rows, and if you spot check some of the numbers against the scatter plot solution, they should match (or be very close). – figures for Portmeirion matched for me.

We also need to highlight which book is selected, so create a parameter

pSelectedBookID

string parameter defaulted to <emptystring>

Then create field

Is Selected Book

[BookID (Edition)] = [pSelectedBookID]

Show the parameter and enter the string PP866 (for Portmeirion). Add Is Selected Book to Rows to verify true is displayed against this row.

On a new sheet, add Avg Checkouts per Month to Columns and Average Orders per Month to Rows. Add Book ID (Edition) to Detail. Add Awarded to Shape and adjust shapes as required. Add Awarded to Size and adjust. Add Is Selected Book to Colour and adjust to suit. Make sure True is listed before False for the Colour legend to ensure the selected book mark is always ‘on top’. Hide the null indicator

Create a new field

Label: Selected Title

IF [Is Selected Book] THEN [Title] END

Add this to Label, adjust the font style, match mark colour and align top centre. Adjust the tooltip. Update the title and name the sheet Scatter or similar.

Building the Ratings Bar

Add Rating as a discrete dimension (blue disaggregated field) to Rows. Add Is Selected Book to Columns and add Ratings (Count) to Text. Exclude the Null column that appears (Filter for Is Selected Book). Add a percent of total quick table calculation to CNT(Ratings) and adjust to compute using Rating.

Duplicate the sheet. Move CNT(Ratings) to Columns and Is Selected Book to Colour. Adjust colours to suit. Unstack the marks (Analysis menu > stack marks off). Add Is Selected Book to Size. Adjust so that True is listed first on the size legend, so it is both narrower than false, and will also now be ‘in front’. Update the tooltip and hide the Rating label heading (right click > hide field labels for rows).

Create a new field

Selected Book Ratings

IF [Is Selected Book] THEN
{FIXED [Title]: COUNT([Ratings])}
END

and add to Detail. Then update the title of the sheet adding a reference to this field. Exclude the Null option that now appears (Rating is added to the Filter shelf). Name the sheet Ratings Bar or similar.

Build the trend line

We need to show the average number of books ordered and the average number of books checked out each month. For this we need

Avg Orders per Book

COUNTD([Order ID])/COUNTD([BookID (Edition)])

and

Avg Checkouts per Book

SUM([Number of Checkouts])/COUNTD([BookID (Edition)])

On a new sheet, add Month (from Months.csv) to Rows as a discrete dimension (blue pill) and add Avg Checkouts per Book and Avg Orders per Book into the table text. Then add Is Selected Book to Columns.

This is where some of my numbers don’t align with the values in Yoshi’s solution, but it’s not clear why… Exclude the Null columns (Is Selected Book added to Filter).

We’re going to want to display the month numbers as the month names, so create a new field to create a ‘date’ out of the number

Month Name

//convert to a date, then use month/formatting functions in display
MAKEDATE(2000, [Month],1)

On a new sheet add Month Name to Columns as a continuous pill at the month (May) level (green pill). Add Avg Orders per Book and Avg Checkouts per Book to the Rows and add Is Selected Book to the Colour shelf of the All marks card. Adjust colour if needed, and ensure True is listed first (on top).

Adjust the y-axis titles, and update tooltip if required. Remove the title from the x-axis. Format the x-axis so the scale uses abbreviated month names

Add Is Selected Book to Filter and exclude Null. Update the sheet title, Name the sheet Monthly Trend or similar.

Building the Library Bar

Add Library to Rows and Number of Copies to Columns. Add Is Selected Book to Filter and set to True. Show mark labels, and adjust to match mark colour. Hide the x-axis. Hide the Library row header. Apply row banding so every other row is shaded. Update the tooltip and sheet title. Name the sheet Library Bar or similar.

Creating the title sheet

On a new sheet add Is Selected Book to Filter and set to True. Then add Title, First Name, Last Name and Genre to Text. Set the mark type to shape and select a transparent shape (see here for info on how to set this up). Set the sheet to Entire View. Organise the text as required and align middle left. Stop the tooltip from showing. Name the sheet Title or similar.

Building the dashboard and interactivity

Use layout containers to position the objects onto the dashboard, and use padding to provide the relevant spacing between objects.

The Title, Ratings Bar, Monthly Trend and Library Bar sheets should all be in a single vertical container which has a dotted blue border surrounding it.

Create a dashboard parameter action

Set Book

on select of the Scatter sheet, set the pSelectedBookID parameter with the value from the BookID (Editiion) field. When the selection is cleared, retain the value.

The layout of my dashboard including the item hierarchy is below

My published viz is here.

Happy vizzin’!

Donna

Can you create Stars & Reference lines?

Donna ColesLeave a comment

Lorna provided this week’s challenge, drawing on some tips she’d picked up from Nhung Lee at #TC25. We’re building 2 charts, but for the second we need some supplementary data, and for this we need to start with some data modelling.

Modelling the data

I downloaded the ChatGPT Android reviews data from Kaggle into a csv file called chatgpt_reviews.csv. I also then copied the additional data Lorna provided in the challenge into another csv called WOW2025_19_Additional Data.csv.

I chose to model the data using a physical model rather than relationships, as I needed to ensure all the review data would always exist in the view. To do this I started by connecting to the chatgpt_reviews.csv. From the context menu of the sheet added to the datasource canvas, I selected Open to ‘open’ the physical model ‘window’

I then added a new connection to the WOW2025_19_Additional Data.csv file And added a Left Join to it, joining from the chatgpt_reviews.csv using a join calculation of

DATE(DATETRUNC(‘day’,[At]))

and joining this to the Release Date field from the WOW2025_19_Additioanal Data.csv file

Now with this set up, we can start to build.

Creating the bar chart

Add Score as a discrete dimension (blue disaggregated pill) to Rows and chatgtp_reviews.csv (Count) to Columns.

Add Score as a discrete continuous (green disaggregated pill) to Colour and adjust to use a grey colour palette. Make each row a bit wider, and show labels

Create a new field

Star Rating

REPLACE(SPACE([Score]), ‘ ‘, ‘★’)
+
REPLACE(SPACE(5-[Score]),’ ‘, ‘☆’)

and add this to Rows after Score.

Hide the Score column (uncheck show header), increase the size of the font for the Star Rating field, hide the Count of chatgpt_reviews axis (uncheck show header), hide all gridlines/axis rulers/ zero lines and row and column dividers. Adjust the font style of the label, hide the ‘Star Rating’ column label (right click > hide field labels for rows). AAjust the Tooltip as desired and update the sheet title. Name the sheet star Rating Bar or similar.

Building the line chart

On a new sheet, add At as a continuous pill at the ‘1st May 2025‘ day level (green pill) to Columns and add chatgpt_reviews.csv (Count) to Rows. Change the Colour of the line to dark grey.

For the ‘reference lines’ we’re actually adding bars all at the same height. The height we’re going to use is the maximum number of reviews, so create

Max Review Count

IF NOT ISNULL(MIN([Release Date (WOW2025 19 Additional Data.csv1)])) THEN WINDOW_MAX(COUNT([chatgpt_reviews.csv])) END

Add this to Rows and hide the null indicator (right click > hide indicator). Change the Mark type to bar.

Add Product to the Label of the Max Review Date marks card, and align as below

Adjust the table calculation of the Max Review Count field so it is computing by both the At and Product fields.

Make the chart dual axis and synchronise the axis. Adjust the Label to explicitly add a couple of carriage returns after the Product text. This has the effect of shifting the text to the left of the bars.

Fix the Count of chatgpt_reviews axis from -100 to 10,800

Ensure mark labels are set to allow labels to overlap other marks.

Tidy up by

  • hiding the right hand axis (uncheck show header)
  • Updating the title of both axis (right click > edit axis)
  • remove all gridlines, zero lines, row & columns dividers
  • show axis rulers
  • update tooltips as required
  • update the sheet title
  • name the sheets Trend or similar.

Then add both charts to a dashboard. My published viz is here.

Happy vizzin’!

Donna