Tag: INDEX

Can you build a colour-coded filter?

Donna ColesLeave a comment

Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.

Defining the parameters

We need 3 parameters for this challenge

pMeasure

strig parameter that lists the two options Profit and Sales; defaulted to Profit.

pProfitThreshold

integer parameter that lists the specified values, defaulted to 2,000

pSalesThreshold

integer parameter that lists the specified values, defaulted to 30,000

Building the core scatter plot

Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.

When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:

Ref – Profit Threshold

IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END

Ref – Sales Threshold

IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END

We also need to define the average per measure for each region:

Sales Avg Per Region

{FIXED [Region]: SUM([Sales])} / {FIXED: COUNTD([Sub-Category])}

Profit Avg Per Region

{FIXED [Region]: SUM([Profit])} / {FIXED: COUNTD([Sub-Category])}

and then we can create

Ref – Profit Avg

IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END

Ref – Sales Avg

IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END

Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.

Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.

Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.

Colour

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0)
ELSE
IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0)
END

Set this to be discrete and then add to the Colour shelf and adjust accordingly.

Creating the colour-coded filter

The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.

We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)

Region Indicator

{FIXED [Region]: (

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟒’,’🟑’)
ELSE
IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟒’,’🟑’)
END
)}

I then created

Filter – Region

[Region Indicator] + ‘ ‘ + [Region]

Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.

Format the Tooltip

The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:

Tooltip – Measure Value

IIF([pMeasure]=’Profit’,[Profit],[Sales])

Tooltip -Threshold

IIF([pMeasure]=’Profit’, [pProfitThreshold], [pSalesThreshold])

Tooltip – Measure Value Above Avg

IF [Colour] = 1 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Measure Value Below Avg

IF [Colour] = 0 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Above text

IF [Colour] = 1 THEN ‘above’ END

Tooltip – Below text

IF [Colour] = 0 THEN ‘below’ END

Label – Region

IIF(COUNTD([Region])>1, ‘All regions’, MIN([Region]))

Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.

Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.

Building the Overall Indicator

For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.

On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.

We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.

Region Indicator – Is Below

IIF( [Region Indicator] = ‘🟑’,1,0)

Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.

Create

Overall Region Indicator

IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟑’, ‘🟒’)

This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.

Create a field

Filter – Index = 1

INDEX() = 1

Add to Filter shelf and set to True

Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).

Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.

Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).

Finally create

Tooltip – Overall Indicator

IIF([Overall Region Indicator]=’🟒’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)

Add this to Tooltip and then set the background colour to pale blue.

Building the dashboard & adding dynamic zone visibility

Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.

Then create 2 new boolean calculated fields

Is Profit

[pMeasure] = ‘Profit’

Is Sales

[pMeasure] = ‘Sales’

Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)

Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.

Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.

Happy vizzin!

Donna

Let’s Practice RegEx in Tableau with Generative AI

Donna ColesLeave a comment

Yoshi set this week’s challenge which focused on 2 areas : the use of tools such as CHAT GPT to help us create Tableau RegEx functions (which are always tricksy), and the build of the chart itself.

Creating the REGEX calculated fields

I chose to use CHATGPT and simply entered a prompt as

“give me a regex expression to use in Tableau which extracts a string from another string in a set format. The string to interrogate is”

and then I pasted in the string I’d copied from one of the Session Html fields. I got the following response

along with many more code snippet examples. I used these to create

Session Title

REGEXP_EXTRACT([Session Html], '<div class="title-text">(.*?)</div>')

Description

REGEXP_EXTRACT([Session Html], '<div class="description"[^>]*><div>(.*?)</div>')

Location

REGEXP_EXTRACT([Session Html], '<span class="session-location"[^>]*>(.*?)</span>')

Session Level

REGEXP_EXTRACT([Session Html], 'session-level-([a-zA-Z]+)')

Alias this field to show the values in proper case (ie ‘Advanced’ rather than ‘advanced’) and display Null as ‘All Levels’

Session Date

REGEXP_EXTRACT([Session Html], '<span class="semibold session-date">(.*?)</span>')

Session Time

REGEXP_EXTRACT([Session Html], '<span class="semibold session-time">(.*?)</span>')

Start Time

REGEXP_EXTRACT([Session Html], '<span class="semibold session-time">([0-9: ]+[AP]M)')

End Time

REGEXP_EXTRACT([Session Html], '- ([0-9: ]+[AP]M)')

Creating the additional fields

All of the RegEx functions return string fields. To build the viz, we need actual date fields and additional information

AM|PM

IIF(CONTAINS([Start Time],’AM’), ‘AM’, ‘PM’)

Date

DATE(DATEPARSE(“MMMM d yyyy”, SPLIT([Session Date], “, “, 2) + ” 2023″))

returns the day of the session as a proper date field, all hardcoded to 2023, since this is when the data is from.

Start Date

DATETIME(STR([Date]) + ” ” + [Start Time])

returns the actual day & start time as a proper datetime field.

End Date

DATETIME(STR([Date]) + ” ” + [End Time])

Duration

DATEDIFF(‘second’, [Start Date], [End Date])

Add fields to a table as below

Firstly, when we build the viz, we’ll need to sort it based on the Start Date, the Session Level (where Advanced is listed first, and All Levels last) and the Duration (where longer sessions take higher precedence) . For this we need a new field which is a combination of information related to all these values.

Sort

STR([Start Date]) + ‘ – ‘ + STR(
CASE [Session Level]
WHEN ‘advanced’ THEN 4
WHEN ‘intermediate’ THEN 3
WHEN ‘beginner’ THEN 2
ELSE 1
END
) + STR([Duration]/100000)

(this just took some trial and error)

Add this to the table after the Session ID pill and then apply a Sort to the Session ID pill so it is sorting by the Minimum value of the field Sort Ascending. You should see that when the start time and session level match, the sessions are sorted with the higher duration first.

The viz also lists the AM & PM sessions as separate instances, where the 1st session of the PM session is displayed on the same ‘row’ as the 1st session of the AM session. To handle this we need

Session Index

INDEX()

Make this discrete and add to the table after the AM|PM pill. Change the table calculation so it is computing by all fields except AM|PM and Session Date. The numbering should restart at each am/pm timeslot

Finally, the last field we need to build this viz, is another date field, so we can position the gantt chart bars in the right places. As the viz already segments the chart by Session Date and AM|PM, we can’t reuse the existing Start Date field as this will display the information as if staggered across the 3 days. Instead we want to create a date field, where the day is the same for al the sessions, but just the time differs

Baseline Date

DATETIME(STR(#2023-01-01#) + ” ” + [Start Time])

the date 01 Jan 2023 is arbitrary and could be set to any day. Add this into the table, so you can see what it’s doing. You will probably need to adjust the table calculation on Session Index again to include Baseline Date in the list of checked fields.

Building the viz

On a new sheet, add Session Date to Columns and manually re-order so Tuesday listed first. Then add AM|PM to Columns . Add Baseline Date as a continuous exact date (green pill) to Columns . Add Session Id to Detail then add Session Index to Rows and adjust the table calculation so it is computing by all fields except Session Date and AM|PM.

Create a new field

Size

SUM([Duration])/86400

and add this to the Size shelf. 86400 is the number of seconds in 24hrs, so adjusts the size of the gantt bars to fit the timeframe we’re displaying.

Add Session Level to Colour and adjust accordingly. You will need to readjust the Session Index table calc to include Session Level in the checked fields. Add a border to the bars. Edit the Baseline Date axis so the axis ranges are independent

Add Description and Session Title to Detail and again adjust the Session Index table calc to include these fields too. Apply a Sort to the Session Id field so the data is sorted by the Sort field descending

Add Location, Start Time and End Time to Tooltip, and update accordingly. Then apply formatting to

  • change font of the Session Date and AM|PM header values
  • remove the Session Date/ AM|PM column heading label (right click > hide field labels for columns)
  • Hide the Session Index field
  • Hide the Baseline Date axis
  • Add column banding so the Wednesday pane is coloured differently

Add the ability to highlight the Session Title and Description by selecting Show Highlighter from the context menu of each pill

Then add the sheet to a dashboard, using containers to display the colour legend and the highlighter fields.

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

Happy vizzin’!

Donna

Can you create this seemingly simple line chart?

Donna ColesLeave a comment

Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.

After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….

Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too πŸ™‚

What didn’t work

The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.

This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.

Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!

Now back to the solution guide…

Creating the calculations

We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.

Create a parameter

pTop

Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20

Show this parameter on the sheet, and then create a calculated field

Order Date (Quarters)

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

Format to the YYYY QX style.  Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.

We need to identify customers who are in the top x for each quarter. Create a new calculated field

Is Top X Customer?

RANK(SUM([Sales]))<=[pTop]

Add to Rows and adjust the table calculation so it is computing by Customer Name only. 

We now want the Sales just for those customers who are in the top x, so create

Top X Sales

IF [Is Top X Customer?] THEN SUM([Sales]) END

Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.

We now need the total of these sales per quarter so create

Total Top X Sales

WINDOW_SUM([Top X Sales])

Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter 

We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create

Total Sales per Quarter 

WINDOW_SUM(SUM([Sales]))

Add this to the table and again adjust the table calculation to compute by Customer Name only.

Now we have these two figures we can calculate the percentage. Create a new calculated field

Sales % per Quarter 

[Total Top X Sales]/[Total Sales per Quarter]

Format this as a % to 1dp. Add to the table.

Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.

Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain 

Create a new field

Customer Index

INDEX() 

Convert this field to discrete (right click on the field).

Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales 

Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values). 

If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.

We’ve now got the core fields we need to build the viz.

Building the Viz

Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.

Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.

Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.

Make the chart dual axes and synchronise axes .

Finally, tidy up the chart by 

  • Adjusting the Tooltip 
  • Removing gridlines, zero lines and row/column dividers
  • Hide the right hand axis 
  • Fix the left hand axis to end at 1 (so the axis goes to 100%)
  • Edit the left hand axis title
  • Update the sheet title to reference the pTop parameter

Then add the viz to a dashboard. 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

Top 5 Product Sales vs Other Products

Donna Coles1 Comment

Erica set this week’s challenge and provided multiple levels specifically aimed a newer users of Tableau. My solution is for Level 3.

Setting up the calculations

First, create a parameter to capture the Sub-Category we care about

pSelectedSubCat

string parameter defaulted to Tables

Create a new field

Is Selected SubCat

[pSelectedSubCat]= [Sub-Category]

then create another field

Product to Display – Step 1

IIF([Is Selected SubCat], [Product Name], ”)

On a new sheet add Sub-Category and Product to Display – Step 1 to Rows. Show the pSelectedSubCat parameter. You will see that the Product rows only show for the Sub Category entered in the pSelectedSubCat parameter

We want to show the average of the product sales for each Sub-Category, so we can create

Sales by Sub Cat & Product

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

Add this to Text. By default it will aggregate this value to SUM, change it to AVG. For the rows associated to the selected Sub-Category the value of this field is the same whether its SUM or AVG, as it has been calculated at the level of detail being displayed on the row (Sub-Category and Product Name). For the other rows, by changing the aggregation to AVG we are getting the required value, which is essentially the sum of all sales associated to the Sub-Category divided by the number of distinct products. Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending

Additionally, sort the Product to Display – Step 1 field the same way

We need to identify the top 5 records for the products associated to the selected Sub-Category. We will use a set for this. Right click on Product to Display – Step 1 > Create > Set

Product to Display Set

Select the Top tab and select the top 5 by formula

AVG(IF [Is Selected SubCat] THEN [Sales by Sub Cat & Product] END)

Add this to Rows and you should get In displayed against the product rows with the highest values

With this we can start to think about the ‘other’ text we need to display, but for this we need a handle on the number of products in each Sub-Category. Create

Count Products Per Sub-Category

{FIXED [Sub-Category]: COUNTD([Product Name])}

Add to Text so you can see the value, and then subsequently we can create

Product to Display – Step 2

IF NOT([Is Selected SubCat]) THEN ”
ELSEIF [Product to Display Set] THEN [Product Name]
ELSE ‘Other: ‘ + STR([Count Products Per Sub-Category] – 5) + ‘ Products’
END

Add to Rows to see the behaviour

The viz also needs to show an index value against the top 5 rows, so create

Index to Display

IF MIN([Is Selected SubCat]) AND MIN([Product to Display Set]) THEN STR(INDEX())
ELSE ” END

Add this to Rows as a blue discrete pill in front of the Product to Display – Step 2 field. Adjust the table calculation setting so the Sub-Category field is unchecked.

Next, we’re going to need to display a reference line that is the overall average product sales for the Sub-Category. This may sound like it’s what we already have, but that field is at the Sub-Category and Product Name level of detail, and we need to aggregate this back up to be at the Sub-Category level, so we create

Avg Sales by Sub Cat and Product

{FIXED [Sub-Category]: AVG([Sales by Sub Cat & Product])}

which is the average of the field we previous created but per Sub-Category. Pop this into the table to see what is happening. For the rows where the Products aren’t showing, the values match, but for the rows where the Products are displayed, you get the overall average, which is the same for all the rows.

If we now remove Product to Display – Step 1 from Rows (and amend the Index to Display table calc so it is not longer computing by this field too), we should have the data we expect. Format the Sales by Sub Cat & Product field to be $ with 0 dp.

Building the Viz

On a new sheet add Sub-Category, Product to Display Set, Index to Display and Product to Display – Step 2 to Rows and Sales by Sub Cat & Product to Columns and aggregate to AVG.

Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending and apply the same sort to the Product to Display – Step 2 field. Edit the table calculation of the Index to Display field so it is not computing by Sub-Category.

Create a new field

Colour

IF [Is Selected SubCat] AND [Product to Display Set] THEN ‘Dark’
ELSEIF [Is Selected SubCat] THEN ‘Light’
ELSE ‘Grey’
END

Add this to Colour shelf and adjust accordingly.

Add Avg Sales by Sub Cat & Product to the Detail shelf, then add a reference line based on the Average of this field

Widen each row and from the Label shelf check Show mark labels. From the Tooltip shelf uncheck Show tooltips.

Hide the In/Out Product to Display Set field in Rows (uncheck show header). Format the font and style of the header columns, then hide the header field labels and hide the axis. Adjust the row banding and set all gridlines, zero lines, axis rulers and column dividers to none. Change the title of the sheet.

Test the behaviour by manually changing the value of the parameter.

Adding the interactivity

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

Set SubCat

On select of the viz, update the pSelectedSubCat parameter passing in the value from the Sub-Category field.

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 expand this segmented bar chart?

Donna Coles2 Comments

For this week’s challenge I expanded on my challenge from week 13. So for this solution guide, I’ll be starting with the workbook I built for week 13 and adjusting as required. You can either build on your own solution if you took part, start with my workbook, or rebuild from scratch (my solution guide to week 13 is here).

Based on the hint I provided in the challenge, I’m going to build this with 3 sheets; one sheet to display the bar charts per year for the specific Sub-Category, then one sheet to display the segmented bars up to the selected Sub-Category and one sheet to display the segmented bars below the selected Sub-Category.

For this we need to assign an index to each row of data.

Sub-Cat Index

INDEX()

Make this discrete and add it to Rows in front of Sub-Category and adjust the table calculation so that it computes using Sub-Category only. Each row should be numbered from 1 – 17.

Creating the top & bottom bar chart

We will need to be able to identify the name of the Sub-Category selected and the associated index value. We’ll use parameters to store this information

pSelectedSubCat

string parameter defaulted to ” <empty string>

and

pIndex

integer parameter defaulted to a very large number, in this case I chose 10,000 (basically a number higher than the number of dimensions listed.

Show the parameters, and manually type into pSelectedSubCat the word ‘Copiers’ and into the pIndex type 8 (the index associated with Copiers).

We want to display an arrow indicator based on whether a Sub-Category is selected or not. We need

Arrow Indicator

IF [pSelectedSubCat] = [Sub-Category] THEN ‘β–Ό’ ELSE ‘β–Ί’ END

Add this onto the Rows after the Sub-Category pill. Readjust the table calculation of the Sub-Cat Index so it is computing by Arrow Indicator as well.

We only want to show up to the selected Sub-Category

Show Top

[Sub-Cat Index]<=[pIndex]

Add this to the Filter shelf and set to True. Verify the table calculation is also computing by Sub-Category and Arrow Indicator only. Hide the Sub-Cat Index column and format the arrows so they are coloured brown and aligned top centre. Name this sheet Top or similar

Duplicate the sheet.

To show the bottom half of the chart create

Show Bottom

[Sub-Cat Index]>[pIndex]

Remove Show Top from Filter and add Show Bottom instead. Set to True. Again verify the table calculation is computing by Sub-Category and Arrow Indicator only. Call this sheet Bottom or similar.

Building the year bar chart

On a new sheet add Order Date to Rows and Sales to Columns. Change mark type to bar. Adjust Colour and size

Create a new field

Is Selected Sub-Cat

[Sub-Category] = [pSelectedSubCat]

Add to Filter and set to True.

Add another copy of Sales to Columns, then double click into the pill, and manually wrap the text with WINDOW_MAX(….. )

On the Window_Max marsk card, reduce the size to as small as possible and add a white border (via the Colour shelf). Add Sales to the Label shelf and adjust the font to be smaller and coloured brown.

Make the chart dual axis and synchronise the axis. Remove measure Names from the All marks card, and right click on the top axis and move marks to back. Hide both axes, remove row/column dividers and gridlines/zero lines. Adjust the font colour and size of the row labels, and remove the row header.

Add Sales to the Tooltip shelf of the All marks card, and add a quick table calculation of percent of total. Format to 1 dp. Add Sub-Category to the Tooltip shelf too, and then adjust the Tooltip of the All marks card to suit. Name the sheet Years or similar.

Building the dashboard

On the existing dashboard, add a vertical container, and then add the Top, Years and Bottom charts within it.

Remove the chart titles.

You’ll notice that the bar for bookcases in the bottom chart is longer than the bar for copiers in the top chart. We don’t want this. To correct we need

Max Sales Axis

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

Navigate to the Top sheet and add Max Sales Axis to the Detail shelf of the All marks card

Create a new parameter

pMaxAxis

float parameter defaulted to 500,000

On the Bottom sheet, show the Sales axis, then edit the axis and adjust so the axis range is Custom from 0 to pMaxAxis

Hide the Sales axis again

Adding the Interactivity

We will use parameter actions to set the various parameters on click, but we need to ensure the viz will ‘expand’ but also fully ‘collapse’. So we need

Sub-Cat to Pass

IF [Is Selected Sub-Cat] THEN ” ELSE [Sub-Category] END

Index to Pass

IF MIN([Is Selected Sub-Cat]) THEN 10000 ELSE [Sub-Cat Index] END

ie if we’re selecting the Sub-Category that’s already been selected then we need to ‘reset’ back to the start state.

Add both of these fields to the Detail shelf of the All marks card on both the Top and Bottom sheets.

Change Sub-Cat to Pass to be an Attribute so that it doesn’t impact the existing table calc settings, and verify Index to Pass is computing by Sub-Category and Arrow Indicator.

Back to the dashboard, and add the following dashboard action

Select SubCategory

On select of the Top or Bottom sheets, set the pSelectedSubCat parameter, passing in the value from the Sub-Cat To Pass field. When clearing the selection, reset to ” <empty string>

Set Index

On select of the Top or Bottom sheets, set the pIndex parameter, passing in the value from the Index To Pass field aggregated at the minimum level. When clearing the selection, reset to 10,000

Set Axis

On select of the Top sheet, set the pMaxAxis parameter passing in the value from the Max Sales for Axis field. When cleared reset to 50,000

You should now have a functioning chart. You may need to adjust padding of the objects and modify width of headers on the charts to get the alignment as required. Update the chart title as required as well.

My published viz is here.

Happy vizzin’!

Donna

Can you filter a small multiples chart?

Donna ColesLeave a comment

Erica set the challenge this week, and I’m not gonna lie, I found this tough. On the face of it, it looks like something I felt I should be ok at, but nuances cropped up as I was building that meant I often had to change tact and try something different.

My intention was to build each version – Beginner, Intermediate then Advanced, adding to my solution each time, but decisions I made early on, then caused me grief later. For example, I did choose to utilise a lot of table calcs, but that meant when it came to applying the sorting mechanism that I wanted, I couldn’t reference the sort field I needed, as it contained a table calc. So I had to unpick the logic and build with LODs instead which took me a while to get right. Getting single lines to display in each ‘state’ cell also proved tricky at times, and that was even before I’d got to the requirement to pad out the ‘missing values’ with 0s. I also seemed to find that some things only seemed to work if I added pills and applied settings in a particular order. All in all, quite a challenge, and while I did get there in the end, I did have to peek at the solution at times to figure out if I was going nuts, but I found trying to scale back Erica’s solution to the beginner/intermediate version also suffered the same issues I was experiencing. I built with Desktop v2024.1 and there were times I was wondering if something had “broken” in that version, although having finally reached the end, I’ve yet to test that theory.

So, I’m blogging this guide with several caveats – Going from beginner to intermediate is ‘okay’, but when it gets to advanced I had to start again(ish). Some of the calcs I provide will just be ‘as is’. I will do my best to explain what’s going on, but there are times that I just don’t get it, and it’s just been trial and error that got me the results I needed – sorry!

So with all that in mind, let’s get building.

Initial steps

After connecting to the provided hyper file, I found I did have to create the modified sales value

Sales Modified

IF SUM([Sales])<5000 THEN SUM([Sales])*10 ELSE SUM([Sales]) END

I also decided to add a data source filter (right click data source > edit data source filters) to restrict the data to just Country/Region = USA.

The later versions of superstore have Canadian Provinces included too, and I don’t think these were listed in Erica’s solution. It just felt easier all round to exclude these records from source.

Beginner challenge

When building a trellis chart, we need to determine which Row and which Column our specific dimensions (in this case State/Province) will sit in. As the requirements already stated it was to be a 3 column grid, our calculations for this didn’t need to be so complex.

Cols

(INDEX()-1)%3

INDEX() returns an incremental number starting at 1 for whatever dimension or set of dimensions we’re counting over. In this case we’re counting the number of State/Provinces. %3 returns the remainder when the index is divided by 3, so we get values of 0, 1 and 2.

Change this field to be discrete (right click -> convert to discrete)

Add State/Province to Rows then add Cols to Rows. Edit the table calculation so the field is computing by State/Province. You can see that the first 3 rows will be positioned in columns 0, 1,2 respectively and so on.

Create a new field

Rows

INT((INDEX()-1) / 3)

This takes the index value (minus 1), divides by 3 and ’rounds’ to a whole number. Make this discrete too and add to Rows, setting the table calculation as described above. Now we can see the first 3 rows will all actually be in the same row (row 0), then next 3 rows in row 1 and so on.

Shift the pills around so Cols is on Columns, Rows is on Rows and State/Province is on Detail. Add Sales Modified to Rows.

Create a new field

Quarter Date

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

and add this to Columns setting it as a continuous exact date (green pill). We’ve got a bit of unexpected ‘spaghetti’ going on…

To fix it, do the following ..

Add Quarter Date to Detail as a discrete exact date (blue pill). Change Quarter Date on Columns to be a continuous attribute (green pill – first change to attribute, then change to continuous). Edit the table calculation settings for both the Rows and the Cols fields to be computing by both State/Province and Quarter Date at the level of State/Province.

I had to reference previous challenges and blog posts I’d written to manage this… maybe there is something simpler, as this is pretty taxing for the ‘beginner’ part of the challenge.

Add another instance of ATTR(Quarter Date) to Columns and make dual axis and synchronise the axis. This will create an axis at the top of the chart as well as the bottom.

Format the ATTR(Quarter Date) pill so the axis format is custom formatted to “Q”q “‘”yy

Edit all the axis (top/bottom and left) and update the title. Adjust the Tooltip. Hide the Cols and Rows fields (right click and uncheck show header). Change the Colour of the line to grey.

This should be the Beginner solution. I have published this here.

Intermediate challenge

For this part of the challenge, we need to set up lots of new calculations, so let’s do this first. As usual, I’ll manage this in a tabular format. So on a new sheet, add State/Province and Quarter Date as a discrete exact date (blue pill) to Rows. Add Sales Modified to Text.

We need to get the threshold value for each State/Province, which is the average of the numbers all listed above, multiplied by 2. We’ll use LODs for this

Threshold

{FIXED [State/Province]: AVG({FIXED [State/Province], [Quarter Date]: ([Sales Modified])})} * 2

working inside out… get the value of the Sales Modified value for each State/Province and Quarter Date (which is the same as the values you see listed above) and then average this at the State/Province level and multiple the final result by 2. Add this to the table. This is the field we’ll be using for the horizontal reference line.

Next we need to identify the rows where the Sales Modified value exceeds the threshold, and then return the Sales Modified values for only these rows. I’ll do this in 2 stages

Is Above Threshold?

INT([Sales Modified] > SUM([Threshold]))

This returns a 1 or 0 depending on whether the statement is True or False. Using actual numeric values rather than boolean helps later on. Set this field to be discrete and add to the table.

Above Threshold Sales

IF [Is Above Threshold?]=1 THEN [Sales Modified] END

Add this to the table too. For the rows where we have 1’s, a value is displayed. This is the field we’ll be using for the red circles.

Finally we need to determine some fields to help us define a reference band. These need to be dates as they’ll be applied to the date axis, but the band doesn’t stretch to the previous/next quarter, and is only present if the last value is over the threshold.

Again using LODs let’s get the final date in the quarter

Max Quarter Per State

{FIXED [State/Province]: MAX([Quarter Date])}

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

Now we need to know if the value associated with the final quarter is above the threshold or not

Final Quarter Above Threshold

INT(MIN([Quarter Date]) = MIN([Max Quarter per State]) AND [Is Above Threshold?]=1)

Again this will return a 1 or 0. Change to discrete and pop that into the table too. We can see Colorado is the first state listed where this is true.

Now we want to ‘spread’ that value across every row associated to the state

State Has Final Quarter Above Threshold

{FIXED [State/Province]: MAX(
{FIXED [State/Province], [Quarter Date]: ([Final Quarter Above Threshold])})
}

For each State/Province and Quarter Date, get the Final Quarter Above Threshold value and then get the maximum value of this for each State/Province. This is where having the values as 1’s and 0s helps.

Make discrete and add this to the table. Every row for Colorado has this set to 1

Now we can work out some dates

Ref Band Min

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, -1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month less than the final quarter for that state.

Similarly

Ref Band Max

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, 1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month more than the final quarter for that state.

Add both of these of the table as discrete exact dates (blue pills).

Now we have all the building blocks to build the next bit of the challenge.

Start by duplicating the Beginner viz.

Add Above Threshold Sales to Rows and make Dual axis and synchronise the axis.. Remove the 2nd instance of the Quarter Date from Columns, so we now have marks cards relating to the 2 measures rather than the 2 dates. Remove Measure Names from the All marks card.

Change the mark type on the Above Threshold Sales marks card to circle and change the colour to red. Adjust size to suit.

Add Threshold to the Detail shelf of the All marks card. Right click on the Sales axis and add reference line. Set it to be per pane and use the Threshold field. Display the value as the Label. Don’t show a Tooltip. Display a dotted black line.

Update the Tooltip to now have a reference to the threshold value too.

Add Ref Band Max and Ref Band Min to the Detail shelf of the All marks card. Set them both to be continuous attributes (green pills).

Right click on the Quarter Date axis and add reference line. Set it to be a band per pane that goes from the Ref Band Min to the Ref Band Max. Don’t display labels or tooltips or a line. Fill with a pale shade ot red/pink.

Add back in the additional Quarter Date dual axis as described in the Beginner section to get the dates listed the top again.

Hide the right hand axis (uncheck show header) and hide the NULL indicator.

This completes the Intermediate challenge. My version is published here.

Advanced challenge

Go back to the tabular sheet we were using to check the calculations needed for the Intermediate challenge, as we’ll build on this.

Firstly the sorting. We need to sort the states based on whether the final quarter was above the threshold or not, and then by the number of times the state was above the threshold. Let’s get the count to start with

Above Threshold Count Per State

{FIXED [State/Province]: SUM(
{FIXED [State/Province], [Quarter Date]: ([Is Above Threshold?])})
}

For each State/Province and Quarter Date, get the Is Above Threshold? value and then sum these up for each State/Province. This is where again having the values as 1’s and 0s helps.

Make this discrete and add to the table

then create a field we’ll use for the sort. This is going to be a numeric field

Sort

IF [State Has Final Quarter Above Threshold] = 1 THEN
100000 + [Above Threshold Count Per State]
ELSE
[Above Threshold Count Per State]
END

We’re just using a very large arbitrary number to force those states where the final quarter is over the threshold to be higher in the list. Make this discrete and add to the table too.

We can now apply a Sort to the State/Province field to sort by the Sort field descending

This results in Colorado moving to the top of the list followed by Minnesota, which also has 3 quarters above the threshold, including the last quarter, but alphabetically falls after Colorado so is listed 2nd.

To filter the data, I created another field, just for ease

Filter

IIF([State Has Final Quarter Above Threshold]=1,’Urgent’,’Non-Urgent’)

Add this to the Filter shelf and set to Urgent. The states should now be restricted to just those where the final quarter is above threshold.

For the final requirement of this challenge, we’ll build out another table on another sheet to demonstrate, as we need to work with a different instance of the quarter date.

On a new sheet, add State/Province to Rows and add Order Date at the Quarter (month year) level as a discrete field (blue pill) to Rows. Add Sales Modified to Text

For Alabama, we don’t have a 2020 Q3 or a 2023 Q3. Click on the Quarter(Order Date) pill and select Show Missing Values. These quarters appear but with no Sales Modified value.

We’ve had to use this different way to define the date quarter as if we tried to ‘show missing values’ against the Quarter Date field that is set to ‘exact date’, we send up getting every day that is missing, not just the dates relating to the quarter. Also, the ATTR(Quarter Date) field we’ve used on the previous vizzes, doesn’t allow the Show Missing Dates option.

Anyway, we need to get 0s in to these dates.

Show Modified with 0

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

Apart from the Rows/Cols calcs needed for the trellis, this is the only table calculation I ended up using. It’s basically looking up it’s own row (LOOKUP([field],0)) and if it can’t find a value (as it’s missing) it’s returning 0 (the ZN() function). Add that to the table.

Ok so now we have the components needed, let’s build the viz. We can use the Intermediate version as a starting point, but will need to reapply some of the features.

Duplicate the Intermediate viz.

Remove the 2nd instance of the ATTR(Quarter Date) pill on Columns. Drag the Sales Modified with 0 pill and drop it directly over the Sales Modified pill so it replaces it. Hopefully the chart should still look the same.

Right click the Order Date field from the left hand data pane, and drag it directly onto the ATTR(Quarter Date) pill. Release the mouse and select the continuous quarter/year option from the dialog that displays.

Things will start to look a bit odd… you’ve lost your lines… Remove the Quarter Date pill from the Detail shelf on the All marks card. Fix the Rows and Cols table calc fields by just updating them to compute by State/Province only. Adjust the table calc of the Sales Modified With 0 field to compute by Quarter of Order Date and State/Province in that order.

Things still look crazy…. but just one more step… Show Missing Values on the Quarter(Order Date) pill.

Now every cell should be associated to a single State/Province with no broken lines, and Wyoming, right at the bottom, should show more than a single dot. This was A LOT of trial and error to fathom all this out.

Add back in the reference band following the instructions above (you should find the reference line for the threshold value will just then appear) and re-update the Tooltip.

Add another instance of Order Date at the quarter/year continuous level (green pill) to Columns and make dual axis and synchronise the axis. Edit the axis titles, and format them to the “Q”q “‘”yy custom format.

Apply the sort to the State/Province field on the Detail shelf so it is sorting by Sort Descending. Add Filter to the Filter shelf and select Urgent.

Add this to a dashboard, and that should be the completed Advanced challenge which I’ve published here.

There really was some black magic going on here at times. Tough one this week!

Happy vizzin’!

Donna

Can you create donut charts exceeding 100%?

Donna ColesLeave a comment

In 2024 the #WOW coaching crew has extended to include Yusuke Nakanishi (@YusukeNakanish3) and Yoshitaka Arakawa (@yoshi_dataviz) from the Japanese #datafam. This week Yusuke set this challenge asking us to build donut charts that represented when percentages were > 100%.

Building out the calculations

In the data set provided the Self-sufficiency ratio for food in calorie base [%] is a number that represents the actual % value ie 90 means 90%, 196 means 196%. To help me remember that, I formatted the field to be a number with 0 decimal places which had a % suffix.

But building the donut charts we need to ‘normalise’ the figures to represent a percentage out of 100. ie, if the value is 90%, we want 90%, but if the value is 196%, we want 96%. So I created

Self Sufficient %

([Self-sufficiency ratio for food in calorie base 【%】] %100) / 100

and then formatted this to a % with 0 dp.

but I can’t build a donut chart with just this value, I need to know the non self sufficient % too

Not Self Sufficient %

1-[Self Sufficient %]

and formatted this to be a % with 0 dp too.

Let’s put these into a tabular view. Add Prefecture to Rows and Measure Names to Columns. Add Measure Values to Text. Add Measure Names to Filter and restrict to the fields Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient %. Add Fiscal Year to Filters and restrict to FY2018. Sort the data by Self-sufficiency ratio for food in calorie base [%] descending.

You can see those rows where the Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient % is over 100% have a different value for the Self Sufficient %.

Showing the Fiscal Year as a filter the user can select, I can change to FY2019 and also see how these fields are behaving; ie when Self-sufficiency ratio for food in calorie base [%] is also over 200%, I’m getting the ‘remainder’ over 200 displayed ie 216% has a Self Sufficient % of 16%, which is what is needed for the ‘bonus’ challenge.

However, due to the way I’m building, I’m going to able to get the display working for the top & bottom 7 records regardless of year (making the build of the bonus challenge a bit easier).

So with this in mind, I now what to categorise the Self-sufficiency ratio for food in calorie base [%] based on what percentage range the values fall into.

% Bracket

FLOOR([Self-sufficiency ratio for food in calorie base 【%】] / 100)

This gives me values of 0, 1 and 2. By default this field will be created within the ‘measures’ section of the data pane (ie below the line). Drag it into the top section to convert it to a discrete dimension. Alias the values (right click -> Aliases) and set as below:

Then add to Rows.

and with the filter still set for FY2019, we can see the rows are categorised into 3 brackets.

Set the filter back to 2018. Now we want to restrict to the top and bottom 7 records only. For this I use Sets.

Create a set against Prefecture (right click the field > create > set).

Top 7

Top 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]

Then create another set, this time for the bottom 7

Bottom 7

Bottom 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]

Then create a Combined set (right click on one of the sets > create Combined set), that includes all values from both sets

Records to Include

Add this to the Filter shelf. By default it will show the records ‘in’ the set. However based on how the order of operations in Tableau works, this will apply the conditions based on the set first before it considers the year being filtered. Ie it will get the top and bottom 7 records based on the total sum of the Self-sufficiency ratio for food in calorie base [%] field for all the data in the data set, then filter to 2018. It means it could show some records that were in the top 7 overall, but not in the top 7 for 2018. To resolve this, and to ensure the data gets filtered by the Fiscal Year first, we need to add the Fiscal Year pill on the Filter shelf to context (right click pill and Add to Context). The pill will go grey.

Next we need a way to ‘categorise’ which rows are the top and which are the bottom. Add Top 7 to Rows which will split the rows into In or Out. Alias these values so In displays as Top 7 and Out displays as Bottom 7 (right click the text > Edit Alias).

Finally, when we build the viz, we need to ensure the 7 entries for each section align with each other. For this create

Top | Bottom Index

INDEX()

and convert the field to Discrete, then add to Rows before the Prefecture pill. Edit the table calculation so that it is computing based on the Prefecture and % Bracket pills only. This gives us an index from 1-7 for each set.

Building the Top & Bottom 7 Donut chart

On a new sheet, add Fiscal Year to Filter and set to 2018. Add the pill to context. Show the filter. Also add Records to Include to Filter.

Add Top 7 to Rows, then double click into rows and manually type MIN(0) to create a ‘fake axis’. Change the mark type to Pie.

Add Prefecture to Detail and Measure Values to Angle. Ensure only Self Sufficient % and Not Self Sufficient % are the only measures displayed (remove any others by dragging them out of the Measure Values box). Add Measure Names to Colour.

Add Top | Bottom Index to Columns and edit the table calculation so it is just computing by Prefecture. This should now give 7 columns of pie charts.

Add a Sort to the Prefecture pill on the Detail shelf, so it is sorting by the Self-sufficiency ratio for food in calorie base [%] field descending.

Add % Bracket to the Detail shelf, then click on the 3 dot icon to the left, and select the colour icon to add this pill to the Colour shelf as well as the Measure Names pill.

Re-edit the table calculation associated to the Top | Bottom Index pill so it is now also computing by the % Bracket field, so you get back to your correct top & bottom 7.

Adjust the order of the pills on the Colour shelf so that the % Bracket pill is listed before the Measure Names pill. Also adjust the order of the pills in the Measure Values box so Self Sufficient % is listed before Not Self Sufficient %.

Then adjust the colours on the colour legend as below and add a dark border to the marks (via the colour shelf).

Change the year filter to FY2019, and you can adjust the colours for the 200% entries too.

To make the ‘hole’ in the donut, double click into the Rows and add another instance of MIN(0). This will create a 2nd MIN(0) marks card.

On that marks card, move Prefecture from Detail to Label. Remove % Bracket, Measure Names and Measure Values. Add Self-sufficiency ratio for food in calorie base [%] to Label. Change the mark type to circle. Set the Colour of the circle to white. Align the label middle centre, and adjust the format/font to suit.

Set the chart to be dual axis and synchronise the axis. Adjust the Size of the pie chart mark independently from the size of the circle mark, so one is slightly larger than the other.

Finally tidy up the display by hiding the axis (uncheck show header), and the Top | Bottom Index field. Remove all gridlines, zero lines, axis lines/ticks and row/column dividers. Hide the In/Out Top 7 field label heading (right click label and hide field labels for rows. Update the title of the sheet so it references the Fiscal Year field. Change the filter back to 2018. Hide all tooltips.

Building the Legend

On a new sheet double click into Columns and type MIN(0.0). Add % Bracket to Rows. Change the mark type to square and add % Bracket to Colour and Label. Adjust colours to suit and add dark border to shape.

From the donut chart sheet, click on the Fiscal Year pill in the Filter shelf and set to apply to worksheets > selected worksheets and select the sheet you’re building the legend on. This will add the pill to this sheet too and changing the value on one sheet will impact the other.

Edit the axis so it is fixed to start at -0.1 and end at 1. This will shift the display to the left.

Hide the axis, and the % Bracket header column; remove all gridlines, zero lines, row/column dividers. Hide the tooltips.

Both the legend and the donut can now be added to a dashboard, and you’ve completed the main challenge. I chose to add the Fiscal Year filter to the display so the user could switch years if they wished.

The bonus challenge – Building the trellis chart

Because of how I’ve built the above, we’ve already done the hard work to handle the 200%+ data. The challenge here now is just to display all the 47 Prefectures for a given year in a 10 x 5 grid – a trellis.

So to build this, I started with the donut chart already built and duplicated the sheet.

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)

Take the index of each Prefecture, decrement by 1 and find the remainder when divided by 10. This means the Prefecture with the highest % value at at position (rank) 1 will be positioned in column (1-0)%10 = 0. The Prefecture at position 11 will also be positioned in column (11-1)%10 = 0.

We also need

Rows

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

Take the index of each Prefecture, decrement by 1 and divide by 10.

Convert both fields to be Discrete.

From the duplicated sheet, remove Top | Bottom Index from Columns and In/Out Top 7 from Rows. Remove Records to Include from Filter. Don’t panic if things look odd!

Add Cols to Columns. Adjust the table calculation to compute by both %Bracket and Prefecture. Specify the sort order to be a custom sort on the field Self-sufficiency ratio for food in calorie base [%] descending.

Now add Rows to Rows and apply the same settings on the table calculation. If all is well, you should have all 47 donuts displaying in the correct order.

Hide the Cols and Rows headings, and you can then add this to another dashboard.

My published viz is here: Top & Bottom 7 | Trellis

Happy vizzin’!

Donna

Can you create a dynamic small multiple chart?

Donna ColesLeave a comment

For this week’s #WOW2023 challenge, Lorna asked us to recreate this small multiple (or trellis) chart which organises the time series charts per Sub-Category into a grid format, where the number of columns is determined by the user.

Whenever I need to build these types of charts, I often end up referencing this blog post by Chris Love from 2014, as this has the basis for the calculations required.

To get started, we need to capture the number of columns based on a parameter

pCols

integer parameter ranging from 1 to 5 with a step size of 1, that is defaulted to 5

On a new sheet, display the parameter, and add Sub-Category to Rows. Apply a sort to Sub-Category based on the field Sum of Sales descending.

Based on the pCols parameter, we need to determine which column and subsequently which row each Sub-Category should be positioned in. We will make use of the index of each entry in the list. Double click into the Rows shelf and manually type in INDEX(). Change the field to be discrete (blue). This will number every Sub-Category row from 1 upwards. To be explicit, edit the table calculation, to explicitly set it to compute using the Sub-Category dimension.

To determine the column for each sub-category

Column

(INDEX()-1)%[pCols]

the % symbol, is the modulo and returns the remainder when the INDEX()-1 is divided by pCols – ie if INDEX() = 12, then 12-1 = 11 and 11 divided by 5 is 2 with 1 left over, so the result is 1.

Add this to the sheet, set it to be discrete (blue) and also edit the table calculation to compute using Sub-Category. You can see that Chairs and Machines are in the same column. If you adjust pCols, the values will adjust too.

To determine which row each Sub-Category will be positioned in we need

Row

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

This divides INDEX()-1 by pCols and just returns the whole number. ie if INDEX() = 8, then 8-1 = 7, and 7 divided by 5 = 1.4. The integer part of 1.4 is 1.

Add this to Rows and set to be discrete, and adjust the table calculation as before. You can see Chairs and Phones are in the same row (but different columns), which Chairs and Machines are in the same column, but different rows.

Let’s rearrange – Move Column to Columns, Sub-Category to Text and remove INDEX() altogether, and you’ll get the basic grid layout we need.

Create a new field to store the date part we’re going to present

Month Order Date

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

Add this to Columns, and set as exact date and add Sales to Rows and move Sub-Category to Detail. At first gland this may look ok, but if you look closely, you’ll notice that there are multiple lines on some of the charts.

This is because there are some states that didn’t sell some of the sub-categories on the month, and this affects the index() calculation when the Month Order Date is set to be a continuous (green) pill (the viz below highlights this better – Accessories is now indexed with 6 and 7…

So to resolve this, add Month Order Date as a discrete (blue) exact date to the Detail shelf underneath the Sub-Category field. Then change the Month Order Date field in the Columns shelf to be a Continuous (green) attribute. Then adjust the table calculation on both the Column and the Row fields, so they are computing over both Sub-Category and Month Order Date, but at the level of Sub-Category.

Format the ATTR(Month Order Date) field on Columns to be the custom format of yyyy, so the axis just display years

and then format the Month Order Date field on the Detail shelf, to be the custom format of mmmm yyyy, so the information in the Tooltip will display the date as March 2001 etc. Adjust the Tooltip to match.

The label for each Sub-Category needs to be positioned based on the y-axis at the maximum sales across the whole display, and on the x-axis at the last point in the date scale ie December 2023. For this we need

Max Sales in Table

WINDOW_MAX(MAX([Sales]))

Label Position

IF LAST()=0 THEN [Max Sales in Table] END

Add Label Position to Rows and adjust the table calculation so the Max Sales in Table nested calculation is computing by both Sub-Category and Month Order Date, and the Label Position nested calculation is computing by Month Order Date only. This should result in a single mark per Sub-Category displaying.

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. On the Label Position marks card, change the Mark type to shape and select a transparent shape (see this post for details on how to get this set up). Move Sub-Category to Label and align top right.

Finalise the display by hiding the Column and Row fields (uncheck show header), hiding the right hand axis (uncheck show header). Format to remove all gridlines & zero lines and hide the null indicator. Remove the axis title.

You should then be able to just add this to a dashboard. My published viz is here.

Happy vizzin’!

Donna