Can you calculate a percent difference between a range of dates?

Donna Coles1 Comment

This week’s challenge, set by Lorna, made use of the newly released ‘dotted line’ format in v2023.2, so you’ll need that version in order to achieve the dotted line that spans the 2 marks being compared. If you don’t have this version, you can still recreate the challenge, but you’ll just have a continuous line.

Building the calculations

This challenge involves table calculations, so my preferred startig point is to build out all the fields I need in a tabular format.

So start by adding Category to Columns and Order Date at the discrete (blue) week level to Rows. Add Sales to Text (I formatted Sales to $ with 0 dp, just for completeness).

Apply a Moving Average Quick Table Calculation to the Sales pill, then edit the table calculation to it is averaging over the previous 5 values (including the current value – ie 6 weeks in total), and it is computing by Order Date only.

Add another instance of Sales back into the table, so you can check the values.

The ‘moving average’ Sales pill is what will be used to plot the main line chart.

But we need to identify 2 points on the chart to compare – the values associated to a start date determined by the user clicking on the chart, and the values associated to an end date determined by the user hovering on the chart. We’ll make use of parameters to store these dates

pDateClick

date parameter defaulted to 27th Dec 2020

pDateHover

date parameter defaulted to 28 Nov 2011

We can then determine what the moving average Sales values were at these two dates

Sales to Compare

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] OR DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN
WINDOW_AVG(SUM([Sales]), -5, 0)
END

Add this to the table, and the values for the moving average will only be populated for the two dates stored in the parameters

This is the field we will use to plot the points to draw the lines with.

But we also need to work out the difference between these values so we can display the labels.

Sales to Compare Start

WINDOW_MAX(IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END)

If the date matches the pDateClick (start) date then return the moving average and then spread that value over every row.

Sales to Compare End

WINDOW_MAX(IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END)

If the date matches the pDateHover (end) date then return the moving average and then spread that value over every row.

Add these into the table, and you can see how the table calculations are working

The moving avg value for the start date is displayed in every row against the Sales to Compare Start column, while the moving avg for the end date is displayed in every row for the Sales to Compare End column.

With these values now displayed on the same row, we can calculate

Difference

[Sales to Compare End]-[Sales to Compare Start]

formatted to $ with 0 dp

and

% Difference

[Difference]/[Sales to Compare Start]

formatted to a custom number format of ▲0.0%;▼0.0%;0.0%

Popping these into the tables, the values populate over every row, but when it comes to the label, we only want to show data against the comparison date, so I created

Label Difference

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [Difference] END

formatted to $ with 0 dp, and

Label Difference %

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [% Difference] END

formatted to a custom number format of ▲0.0%;▼0.0%;0.0%

With all these fields, we can now build the chart

Building the viz

On a new sheet, add Order Date set to the continuous (green) week level to Columns and Category to Rows. Add Sales to Rows and apply the moving average quick table calculation discussed above, adjusting so it is computing over 5 previous weeks and by Order Date only

Add Category to Colour and adjust accordingly, then reduce the opacity to around 40%

Add pDateClick to Detail then add a reference line to the Order Date axis the references this field. Adjust the Label and Tooltip fields, and set the line format to be a thick grey line.

Format the reference line text so it is aligned top right.

Then add pDateHover to Detail and add another reference line, but this time set the line so it is formatted as a dashed line.

Next, add Sales to Compare to Rows. Adjust the colour of the associated marks cards so it is 100% opacity.

Right click on the Sales to Compare pill and Format. On the Pane tab on the left hand side, set the Marks property of the Special Values section to Hide (Connect Lines). Click on the Path shelf and choose the dashed display option.

Set the chart to be Dual axis and synchronise the axis. Remove Measure Names from the All marks card. Hide the right hand axis.

Add Label Difference and Label Difference % to the Label shelf. Set the font to match mark colour and adjust font size and positioning. I left aligned the font but added a couple of spaces so it wasn’t as close to the line. I also added a couple of extra carriage returns so the text shifted up too.

Finally adjust tooltips, remove column dividers and hide the Category column title. Adjust the axis titles.

Adding the interactivity

Add the sheet onto a dashboard, then add 2 parameter actions

Set Start on Click

On select of the Viz sheet, pass the Order Date (week) into the pDateClick parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).

and

Set Comparison on Hover

On select of the Viz sheet, pass the Order Date (week) into the pDateHover parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).

And that should be it.

My published viz is here.

Happy vizzin’!

Donna

Can you build a mobile KPI app?

Donna Coles1 Comment

For week 24 of #WOW2023, Kyle set this challenge involving dynamic zone visibility to build a mobile friendly KPI visual.

The charts being displayed are relatively simple, and use techniques applied several times in other challenges. Let’s tackle the charts for each ‘page’ one by one.

The Home page

For the Sales KPI

  • Double click into Columns and type MIN(1)
  • Add Sales to Label.
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog to include the word Sales and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Format the Sales measure to be $ with 0 dp.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

Duplicate this sheet and create equivalent ones for Profit and Orders – you’ll need to create a field

Count Orders

COUNTD([Order ID])

for the orders KPI.

For the Sales Sparkline

  • Add Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Duplicate the sheet and create equivalent ones for Profit and Orders.

You should have 6 sheets for the home page.

The Category Page

The Category page is displaying values for the last 12 months based on ‘today’. If building this in a business environment, I would make use of the TODAY() function. But to ensure the viz doesn’t break in future, I’ll hardcode today within a parameter

pToday

date parameter defaulted to 14 June 2023

I then need a field to restrict the records to report over

Last 12 months only

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

this will return true if the Order Date associated to the record is greater than or equal to the 1st of the month, 12 months ago, based on the pToday parameter, and the Order Date is less that the 1st of next month, based on pToday.

For the Category KPI sheet

  • Add Last 12 months only to the filter shelf and set to true
  • Add Category to Rows
  • Double click on Columns and type in MIN(1)
  • Add Sales and Category to Label
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Hide the Category column
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

For the Category trend

  • Add 12 months only to filter and set to true.
  • Add Category and Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Add Sales to Label and show the min & max labels for each Category pane
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

The Segment Page

Repeat the same steps as described above for the Category Page, but replace any reference to Category with Segment.

Sales by State Page

  • Add State/Province to Rows
  • Add Sales to Columns
  • Sort by Sales descending
  • Adjust the Colour
  • Click on the Label shelf and Show mark labels
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Building the navigator

I went down a slightly longwinded route for this, but its still an acceptable method. I knew deep down it could be done in 1 sheet, but my brain just wasn’t quite wired up properly when I built it.

I basically ended up building 2 sheets per symbol.

Firstly, you’ll need to add the symbol images into your shapes palette.

Create a new field

Selection – Home

“Home”

Also create fields

True

TRUE

and

False

FALSE

  • Add Selection – Home to the Text shelf.
  • Change the mark type to Shape and select the ‘home’ shape from your custom shape palette.
  • Set to Entire View, then adjust the Label alignment to be bottom centre.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Format the background of the worksheet to medium grey
  • Add True and False to the Detail shelf.
  • Name the sheet Home – Unselected or similar

Duplicate sheet and change the background colour to teal or similar. Name this sheet Home – Selected or similar.

Repeat the process building 2 sheets for each image – you’ll need to create a Selection– Category field, a Selection – Segment field and a Selection – State field.

Building the calcs for Dynamic Zone Visibility

In order to hide and show various content ‘on click’ we will be making use of dynamic zone visibility. For this we need several boolean fields created along with a parameter

pSelection

string parameter, defaulted to Home

We then need

Is Home Selected

[pSelection] =’Home’

Is Home Not Selected

[pSelection] <>’Home’

Is Category Selected

[pSelection] =’Category’

Is Category Not Selected

[pSelection] <>’Category’

Is Segement Selected

[pSelection] =’Segment’

Is Segement Not Selected

[pSelection] <>’Segment’

Is State Selected

[pSelection] =’State’

Is State Not Selected

[pSelection] <>’State’

Building the Dashboard

We need to make use of multiple (nested) containers in order to get all the content positioned in the right place. I’m not going to go through step by step which containers to place where, but just summarise the key points.

For the ‘navigator’ strip, all 8 sheets need to be placed side by side in a horizontal container, and should be ordered so the ‘home’ sheets are first, then the ‘category’ ones etc. I adjusted the padding around each object to be 1px, and obviously didn’t show the title.

For each sheet, determine whether it should display or not by using the control visibility using value option on the layout tab, and selecting the appropriate field based on which ‘page’ the sheet relates to , and whether it’s the ‘active’ / selected sheet or not.

Eg for the teal Home – Selected sheet, the control visibility using value option should be driven based on the value of the Is Home Selected field, while the grey Home – Unselected sheet should be based on the value of the Is Home Not Selected field.

If all these are set correctly, only 4 of the 8 sheets should be visible at any one time – 1 teal and 3 grey.

For the ‘pages’ ie the set of sheets visible based on the selection in the navigator, a Horizontal Container should be used which in turn consists of 1 vertical container (for the sheets relating to the Home page), 2 horizontal containers (1 containing the 2 sheets side by side for the Category page, and 1 containing the 2 sheets side by side for the Segment page), and finally the Sales by State sheet should be added to the main horizontal container.

The Sales by State sheet should be visible based on the Is State Selected field. Each of the other containers should be visible based on their relevant field.

When putting all this together, the dashboard might look crowded and disorganised, but once the settings have been applied, only 1 page’ should be visible and then you can tweak padding and positioning if need be.

Capturing the selection

We need parameter actions to determine which card should display

Select Home

This parameter action should be applied when the Home – Selected or Home- Unselected sheets are clicked on, and it should set the pSelection parameter, passing in the Selection – Home field.

Equivalent parameter actions should then be created for each of the other Selected/Unselected sheets, passing in the appropriate Selection – xxx field.

Finally to ensure the navigation options don’t remain ‘selected’ on click (the images look darker) we need to apply filter actions to set the true field to false on each of the navigation buttons – this means 8 filter actions, which should look similar to this…

The source sheet selected on the dashboard should target the actual sheet itself (not the one on the dashboard).

Add a title and any other content onto the dashboard. Finally to ensure the viz works properly on a mobile, delete the phone layout option that is automatically listed on the dashboard tab.

My published instance is here. Check out Kyle’s solution to see the 1-sheet navigator.

Happy vizzin’!

Donna

Can you compare state sales – Part 2?

Donna Coles3 Comments

This week’s #WOW challenge is an extension of this challenge which I blogged about here. The premise is to mimic a real world scenario – a dashboard has been built and used, but now, a few months after use, additional functionality is required. I am going to be building on top of the solution I delivered for week 11. If you participated in that week, your solution may not have matched mine, so this guide may not be as coherent.

Identifying the selected states

In part 1, we captured the single state selected within a parameter pSelectedState via a parameter dashboard action. We now need to be able to capture multiple states. We’re still going to use the same parameter and a parameter action, but we need to adapt what will be stored within in it.

Instead of a single state being stored in the parameter, we’re now going use this parameter to build up a delimited list of the states selected. We can then interrogate that string to determine the first and the second state selected, and identify if any additional states have been selected.

When using delimited strings, you need to choose a character to separate the field values that isn’t going to appear within the field value itself; ie choose a character that won’t appear within a State name. In this instance I’m choosing | (pipe), and I’m going to build up the parameter as follows

ActionpSelectedState
No states selected<empty string>
1 state selected eg Arizona|Arizona|
2 states selected eg Arizona then Utah|Arizona||Utah|
3 states selected eg Arizona, then Utah, then Colorado|Arizona||Utah||Colorado|
Existing state is selected again (trigger to reset the view)<empty string>

Based on the logic above, we need to modify the existing field

State for Param

IF CONTAINS([pSelectedState], [State]) THEN ” //selected state is already in the parameter, so reset back to ”
ELSE [pSelectedState] + ‘|’ + [State] + ‘|’ //append current state selected to the existing parameter string
END

To see how this is working, go the the dashboard and display the pSelectedState parameter. Click on states and see how the parameter is changing.

As we interact with the hex maps, we can see the list of states building up in the parameter. We’ve obviously lost some of the other functionality now, but that’s ok, we have more things to adapt.

We can now manipulate this string to identify the first two states selected

First Selected State

SPLIT([pSelectedState],’|’,2)

returns the string that comes before the 2nd instance of the ‘|’ (and after the 1st)

Second Selected State

SPLIT([pSelectedStateString],’|’,4)

returns the string that comes before the 4th instance of the ‘|’ (and after the 3rd).

We can then use these fields to set the colour for the map

State Colour

IF [State] = [First Selected State] THEN ‘First’
ELSEIF [State] = [Second Selected State] THEN ‘Second’
ELSE ‘Other’
END

Replace the Is Selected State field that is currently on the Colour shelf and the Shape shelf of the Column (2) marks card, with this new State Colour field. Ensure you have at least 2 states listed in the pSelectedState parameter, so you have options for First, Second, Other all listed in both the colour and shape legends.

Adjust the Shape legend and set the First and Second values to use the same hexagon shape used initially, but set the Other option to use a transparent shape.

Adjust the colours. Increase the opacity on this mark to 100% Set the colours as you wish – I used:

  • First – teal : #66b3c2
  • Second – light teal : #bce4d8
  • Other – pale grey : #d3d3d3

Click around on the dashboard. You should find as you click the first state it is coloured dark teal. Click a second, it is coloured light teal. Click either selected state again, and the map resets. Click more than 2 states, and only the first 2 are coloured, whilst the parameter continues to capture all the states being clicked.

Alerting if more than two states are selected

We will need a field to identify if more than 2 states have been selected.

More than 2 Values Selected?

SPLIT([pSelectedStateString], ‘|’, 6) <> ”

This is looking for a 3rd instance of a value within the delimited string, and returns true if there is one.

As this is a boolean field, we can use it to control the visibility of the message text box we want to display.

We also need to ensure that when the user clicks on the message, it disappears, and the hex map is re-set, ie the pSelectedState parameter gets set back to empty string. Another field will help us with this

State Reset

”

On a new sheet, add State Reset to Text. Then edit the text to the relevant message, and align centrally. Change the tooltip to say ‘click here to reset’. Set the background colour of the sheet to a pale grey. Name the sheet ‘Alert’ or similar.

On the dashboard, delete the contents of the pSelectedState parameter and remove it from the dashboard. Add the Alert sheet as a floating object. Set to fit entire view and remove the title. Add a border to the object.

Select the object, then in the Layout pane, set the Control visibility using value option to the More than 2 Values Selected field.

Then add a dashboard parameter action to reset the parameter when the message is clicked

Reset States

On select of the Alert object, set the pSelectedState parameter to the value stored in the State Reset field.

Click around, selecting more that 2 states and test the behaviour. Now we can move on the adapting the other charts.

Adapting the calculations

To sense check what is going on, on a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter. Add State Label to Rows, and show the pSelectedState parameter.

When only 1 state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ . When 2 states have been selected we just want the state names against the 2 relevant rows. Otherwise we want NULL to show. We need to adapt the State Label field as follows

State Label

IF [First Selected State] <> ” AND [Second Selected State] = ” THEN
IF [State] = [First Selected State] THEN [State] ELSE ‘Other States (Avg)’ END
ELSEIF [First Selected State] <> ” AND [Second Selected State] <> ” THEN
IF [First Selected State] = [State] OR [Second Selected State] = [State] THEN [State] END
END

Now we need to display a different value for the sales measure depending on whether we have 1 or 2 states selected. So we need to adapt the existing Sales To Display field

Sales To Display

IF CONTAINS([pSelectedState], MIN([State Label])) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the parameter string contains the State Label value, then use the sales, otherwise average the sales over the number of states that make up the sales. Format this to $ with 0dp.

Add this to the table, and remove State from the display

Add State Label to the Filter shelf and exclude NULL. Now remove the second state from the parameter and check the result

Adapting the bar chart

On the existing bar chart sheet, verify the pSelectedState parameter is displayed. Replace Is Selected State on the Colour shelf with the State Colour field and remove Is Selected State from the Rows shelf.

Add another state to compare against. Exclude the NULL values that display (add State Label to Filter and exclude Nulls)

We always want to make sure that the first state selected is listed first.

Sort- State Label

IF [State Label] = [First Selected State] THEN 1
ELSEIF [State Label] = [Second Selected State] THEN 2
ELSE 3
END

Add this field on to Rows as a discrete (blue) pill, between the existing two pills and then hide it.

Adapting the line chart

On the existing line chart sheet, verify the pSelectedState parameter is displayed

Add State Label to Filter and exclude null. Replace Is Selected State on the Colour shelf with the State Colour field.

Displaying the additional charts on click.

The display of the bar and line chart are based on the dynamic zone visibility functionality, and the field Show Viz (if you have downloaded my original solution workbook to build onto, you may need to unhide fields in the data pane for this field to be visible).

Amend Show Viz to

[First Selected State] <> ”

Amending the Chart Title sheet

On the Title sheet, replace the Is Selected State on the Filter shelf with Show Viz = TRUE.

Remove State Label from the Text shelf and add First Selected State and Second Selected State to Text. Additionally create a calculated field

Label:All State

IF [Second Selected State] = ” THEN ‘Other States (Avg)’ ELSE ” END

Add this to Text too.

Adjust the text as below and apply matching colour coding to the fonts. Align the text centrally.

Finally update the title and instructions on the dashboard.

My published viz is here.

Happy vizzin’!

Donna

Filtering and Highlighting

Donna Coles1 Comment

It was Sean’s turn to set the challenge this week which included a lots of little details to provide a wealth of different ‘views’ on the same data within a single chart, specifically the change of measure and the change of date granularity.

Building a viz showing the timeline at different levels of granularity based on user selection, is something I’ve done several times in the past, both for other WOW challenges and also in real work situations. So I immediately headed down my ‘usual route’, creating a parameter to identify the level of the date to report at, and a calculated field to display the appropriate level based on the parameter. However, then I noticed that in Sean’s viz, if I changed the date level, the date axis display also changed, sometimes showing <Month Year> format, sometimes <Year Q> etc. Having the axis change like this isn’t something that would work with my ‘usual’ solution. I did ponder this for some time, and the only thought I had was multiple sheets – one for each date part. Sean hadn’t stated how many sheets were needed for the challenge, and it made me wonder if the omission was perhaps deliberate…

Building the Month chart

To start we need to define a parameter to identify the level of date to select

pDateGranularity

string parameter defaulted to ‘month’ with a list of options, which have an alternative display name

We also need to identify the measure we want to show. Before we can define this, we need

Profit Ratio

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

and

#Orders

COUNTD([Order ID])

We also need to be able to capture the selected measure in a parameter

pMeasure

string parameter, defaulted to PROFIT (note case). A list isn’t needed as the value will be set via an action on the dashboard

so we can then build

Measure to Display

CASE [pMeasure]
WHEN ‘SALES’ THEN SUM([Sales])
WHEN ‘PROFIT’ THEN SUM([Profit])
WHEN ‘PROFIT RATIO’ THEN [Profit Ratio]
WHEN ‘ORDERS’ THEN [#Orders]
END

On a new sheet, add Order Date to Filters and set as a relative date filter to the last 4 years. Add Order Date to Columns as a continuous (green) pill set at the month level, and add Measure to Display to Rows.

Add the Order Date filter to ‘context’ (right click > Add to context) and set it to apply to worksheets > all using this data source. The context is necessary as when it comes to the option to select a date to highlight, only the dates within the timeframe selected should be visible. Show the pMeasure and pDateGranularity parameters.

Edit the Measure to Display axis to change the title to reference the pMeasure parameter instead (right click and edit axis).

Test the values change as expected by changing the text in the pMeasure parameter to SALES or ORDERS or PROFIT RATIO.

To identify a month to highlight, I created

Order Date – month

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

and formatted this to a custom format of yyyy-mm

I then created a set off of this field (right click > create > set) and selected a single date 2021-11

Order Date – month Set

In order to make use of the set and its values, the set needs to be on the view. Add Order Date – month Set to the filter shelf. If it doesn’t happen by default, change the option on the pill to Show In/Out of set and then select both In and Out as the filter options (ie all possible values so nothing is actually being filtered).

Then click on the Order Date – month Set pill on the filter shelf and select show set. The list of values in the set should display.

Change the input type of the Set control to be a single value drop down, where the (All) option doesn’t show (via customise) and the option All values in Context is selected

We need to capture the value associated with the date selected

Month Measure to highlight

IF ATTR([Order Date – month Set]) THEN [Measure to Display] END

Add this to the Rows shelf, then make the chart dual axis and synchronise the axis. Adjust the colours of the Measure Names and then make the mark type of the Month Measure to Highlight marks card, a circle, and increase the size.

Hide the right hand axis, remove the title of the bottom axis and remove all row & column dividers. Right click on the nulls indicator and hide indicator.

Call this sheet month chart or similar.

Building the Quarter chart

Go through similar steps to build a chart to display the information at a quarterly level. The field on the Columns shelf should be Order Date set at the continuous (green) quarter level.

You will need a field

Order Date – quarter

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

and format this to a custom format of yyyy-“Q”q

Use this to create a set off of this field (right click > create > set) and Order Date – quarter Set

You’ll also need

Quarter Measure to highlight

IF ATTR([Order Date – quarter Set]) THEN [Measure to Display] END

Building the week chart

Repeat again.

The field on the Columns shelf should be Order Date set at the continuous (green) week level.

You will need a field

Order Date – week

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

and format this to a custom format of yyyy-“W”ww

Use this to create a set off of this field (right click > create > set) and Order Date – week Set

You’ll also need

Week Measure to highlight

IF ATTR([Order Date – week Set]) THEN [Measure to Display] END

Creating the BANs

To start with, we need to create some calculated fields to store the total values for each measure and the ‘highlighted’ vallues

Total Sales

{FIXED: SUM([Sales])}

format to $ with 0 dp

Total Profit

{FIXED: SUM([Profit])}

format to $ with 0dp

Total Profit Ratio

{FIXED: [Profit Ratio]}

format to % with 0 dp

Total Orders

{FIXED:COUNTD([Order ID])}

format to number with 0 dp

and we’ll also need values for the highlighted date

Highlighted Sales

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Sales])
END

format to $ with 0dp

Highlighted Profit

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Profit])
END

format to $ with 0 dp

Highlighed Profit Ratio

SUM([Highlighted Profit])/SUM([Highlighted Sales])

format to % with 0 dp

Highlighted Orders

COUNTD(IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN [Order ID]
END)

format to number with 0 dp.

On a new sheet, double click into columns and manually type MIN(0.0). Repeat this 3 more times, so there are 4 instances of MIN(0.0) on the Columns shelf.

On the All marks card, add Measure Names to the Label shelf.

Now right click on Measure Names in the left hand data pane and select Aliases. Alias each of the MIN(0.0) measures to SALES, PROFIT, PROFIT RATIO, ORDERS

The labels on the viz should change

Create a new field

Sales Selected

[pMeasure] = ‘SALES’

On the 1st MIN(0.0) marks card, change the mark type to shape and add Sales Selected to the shape shelf. Use a transparent shape (see here for details) for the False value, and use the provided sparkline image (which you need to add to your shape palette) for the True value (change the pMeasure parameter to SALES for the True option to show). Increase the Size of the shape to around the 3/4 mark.

Add Total Sales and Highlighted Sales to the Label shelf, and then adjust the font position, size and colour accordingly. This is what my label dialog looked like – Measure Names is centred, while the other two fields are right aligned, and the overall alignment is right too.

Adjust the MIN(0.0) axis to be fixed from -0.2 to 1 to give the mark and the text enough space to breathe.

Now repeat the exercise for the other 3 marks cards. You will need the fields

Profit Selected

[pMeasure] = ‘PROFIT’

Profit Ratio Selected

[pMeasure] = ‘PROFIT RATIO’

Orders Selected

[pMeasure] = ‘ORDERS’

and you’ll need to change the value in the pMeasure parameter to get the relevant shape to show or not for each measure

Finally, create fields

True

TRUE

False

FALSE

and add these both to the Detail shelf of the All marks card. We’ll use these to stop the BANs from being ‘highlighted’ on click on the dashboard.

Then remove all row/column dividers and gridlines & zero lines

Building the dashboard

Create the dashboard, and arrange all the objects so the controls/filters are listed in a row at the top, and the 3 charts are arranged side by side underneath.

If you’ve got dates selected for your quarter & week, then in the data pane, right click on the relevant set, edit set and uncheck any option. This should then give you (None) in the dropdowns.

We will use dynamic zone visibility to control which charts & highlight date controls to display. For this we need fields

Show Monthly

[pDateGranularity] = ‘month’

Show Quarterly

[pDateGranularity] = ‘quarter’

Show Weekly

[pDateGranularity] = ‘week’

On the dashboard, select the monthly chart, and then from the Layout tab, select Control visibility using value and choose the Show Monthly field. Set the control visibility for the highlight a month object to also use the Show Monthly field.

Set the control visibility for the Quarterly chart and highlight a quarter object to use the Show Quarterly field, and set the control visibility for the Weekly chart and highlight a week object to use the Show Weekly field.

Create a dashboard parameter action to set the pMeasure parameter on click of the BANs sheet, passing in the Measure Names to the parameter

Set measure

Create a dashboard filter action to stop the BANs from being highlighted ‘on click’

Deselect BANs

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

Happy vizzin’!

Donna

Profit vs Target (with Tolerance)

Donna Coles1 Comment

Erica set this challenge this week, an extension of the classic actual vs target visual that is very common in business dashboards. She provided a customised data set based on Superstore Sales which included some target values.

Building the basic viz

Add Order Date to Filter shelf and restrict to the Year 2023 only. Then add Order Date to Columns and set to the discrete (blue) month level. Add Profit to Rows. Change the mark type to bar.

Add Target Profit to Rows. Change the mark type on the Target Profit marks card to Gantt Bar. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. Adjust the colour of the gantt bar to grey.

Note – it is possible to add the Target Profit as a reference line. However the width of the line will span the whole width of the ‘space’ allowed for a single month, and can’t be adjusted. Putting Target Profit on its own axis means the width of the bars can differ from the width of the gantt bar which in turn differs from the profit tolerance area we’ll add next.

Adding the tolerance bands

Create a new parameter

pTolerance

Float value defaulted to 0.05 that is displayed as % to 0 dp. Set the range from 0 to 1 with 0.01 increments.

Then create

Target Tolerance Min

SUM([Target Profit]) * (1-[pTolerance])

and

Target Tolerance Max

SUM([Target Profit]) * (1+[pTolerance])

Add both these fields to the Detail shelf on the All marks card. Right click on the Profit axis and Add Reference Line. Add a reference band per cell, which gors from the Target Tolerance Min to the Target Tolerance Max field. Set all Labels & Tooltips to None. Fill the band with a light grey.

Colouring the bars

Create a calculated field

Colour – Bar

IF SUM([Profit]) < [Target Tolerance Min] THEN ‘red’ ELSEIF SUM([Profit]) > [Target Tolerance Max] THEN ‘blue’
ELSE ‘grey’
END

Add this the Colour shelf of the Profit marks card, and adjust the colours to suit. Reduce the opacity of the colour to 85%.

Reduce the Size of the bars slightly, so they are narrower than the Gantt lines.

Finalising the chart

Create a new field

Profit Diff From Target

(SUM([Profit])-SUM([Target Profit]))/SUM([Target Profit])

format to % with 0 dp, and add this field to the Detail shelf of the All marks card.

In addition, format Profit and Profit Target to be $ with 0 dp.

Add Profit, Profit Target, Order Date as a discrete (blue) pill set to the Year level, to Detail too.

Adjust the Tooltip accordingly.

The right click on the right hand Target Profit axis and uncheck show header to hide the axis. Remove all column and row dividers. Remove the title from the Profit axis, and hide the Order Date header label (right click > hide field labels for columns).

Show the pTolerance parameter and test the functionality.

Building the legend

This is a sneaky way to build a legend using the data source available. It relies on using a field that isn’t in use, that has at least 3 dimensions. In this case I chose Region.

Add Region to Columns and exclude West. Right click on the Region field in the data pane and select Aliases. Add an alias for each of the other values to marry up to the legend names.

Manually resort the Regions on the sheet so they are listed in the correct order.

Double click into the Rows shelf and type MIN(1). Edit the axis to fix it to run from 0-1. Add Region to colour and adjust colours accordingly. Reduce opacity to 85% to.

Adjust the height and width of the display and you can see how it starts to look like the required legend.

Hide the axis and remove all gridlines. Adjust the font of the header labels. Hide the Region label heading and stop tooltips from displaying.

The final step is just the then add the two sheets onto a dashboard with the pTolerance parameter displayed too.

My published viz is here.

Happy vizzin’!

Donna

Can you build a heat map with bathymetry lines?

Donna Coles2 Comments

What??? That was the first thought that went through my mind when Luke set this challenge. Bathy..huh… ??? What’s that all about. Well read the challenge to find out more 🙂

The what?? was quickly followed by errr…..? The indicator on the challenge overview page said ‘hard’ and this was a Luke challenge after all, so certainly not for the faint hearted! I wasn’t sure how this was going to go, so just started with a basic tabular view and went from there. (hint – it might be worth reading the blog to the end before you start building.. it could save some time 😉 )

I quickly built a basic heat map. I added Order Date at the discrete (blue) month level to Columns and Sub-Category to Rows. I added Sales to Colour and changed the mark type to square then added Sales to Label.

We ultimately need the cumulative Percent of Total Sales per Sub-Category, to display as the label. Click on the Sales pill on the Label shelf and Add Table Calculation. From the dialog window, choose Running Total and verify the calculation is computing Table (across) (or amend and select Specific Dimensions and ensure Month Order Date is selected). Then check Add secondary calculation and in the secondary calc dialog, select Percent of Total, again ensuring Table (across).

If applied correctly, the values in the December column should all be 100%.

I wanted to ensure this calculation was stored so I could reuse, so while pressing shift key, I dragged the Sales pill from the label shelf into the data pane, and renamed the calculation

Cumulative % of Total

RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))

Format the field to be % with 0 dp

Editing the field should show the details above. Of course, you can just create the calculated field manually and type in the syntax. If you do that, then replace the Sales field on the label field, with this one. Centre align the text.

With this field, we can create the calculation Luke provides for the colouring

Colour

ROUND([Cumulative % of Total]*50, -1)

Replace the Sales field on the Colour shelf with this field, and adjust the colour to use the Blue sequential colour palette. You should now have the basic structure and colouring of the heat map.

Adjust the font style and alignment of the Sub-Category and Order Date Month label headings, and change the month labels to be abbreviated. Hide the Order Date column label and the Sub-Category title (right click hide field labels for columns/rows). Remove all gridlines, row/column dividers etc.

Looking good.. but what next.. how to get those divider lines…. I wasn’t too sure at this point, but I knew I had to identify the cells when a ‘change in colour’ happened both horizontally and vertically.

So the first thing I did was to duplicate the above sheet into a basic crosstab (right click sheet > duplicate as crosstab), and I removed Cumulative % of Total from the display, so I just had the values used for the colour.

I decided I wanted to flag each cell with a 1 or 0 depending on whether the next cell was different or not. I started horizontally. So for each row, and starting with January, I wanted to compare the colour value for Jan with the colour value for Feb. If they were the same, I wanted to record 0 against Jan. If they were different I wanted to record 1 against Jan. I used the following calculation

Horizontal – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 0.99 END

If the Colour value for the current cell (eg Jan) matches the Colour value of the next cell (eg Feb) OR, the current cell is the last month (ie Dec), then return 0 otherwise there is a mismatch so return 0.99. Originally I used 1, but later found I had to adjust the calc to make the line show as I wanted.

Add this field to the tabular display and verify the table calculations used within the field are running Table (across).

You can see that for Accessories, the Colour value for Feb is not equal to the Colour value for Mar, and so the Horizontal – Next Value Diff value for Feb is 0.99. Whereas as the Colour value for Apr matches May, the Horizontal – Next Value Diff for Apr is 0.

I created a similar calculation to test the vertical settings

Vertical – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 1 END

This is essentially exactly the same calculation, but when added to the tabular view, the table calculation for the Vertical – Next Value Diff calc should be set to Table (down) instead

You can see that in Feb, the Colour value for Paper is equal to the Colour value for Phones, so the Vertical – Next Value Diff calc for Paper is 0. But in Mar, the Colour value for Storage is different from the Colour value for Supplies, and so the Vertical – Next Value Diff calc for Storage is 1.

Using these markers, I now want to ‘plot’ them on a viz.

So back to the heat map sheet we built above, we need some axis.

Double click into the Columns shelf and type MIN(1.0) and change the mark type to bar.

You can see a hole has appeared. To fix this, on the Analysis menu, select Infer Properties from Missing Values. The cell will populate.

Confession – I didn’t get this bit initially. I had the chart built with the lines in the correct places, but with a hole. I’d tried all sorts of combinations of ZN, and LOOKUP(expression,0) in calculations to try to make the number appear, but couldn’t get anything to work. I showed my colleague Sam Parsons, who mentioned the above setting. I have honestly never ever used it and was completely unaware of it’s existence. But it solved the problem, so massive hi-five to Sam 🙂

Double click into the Rows shelf and again type MIN(1.0). Having an axis on both rows and columns, means we can now fix the Size of the bar. Click on the Size shelf and select Fixed, width in axis units to 1 and alignment right.

Now we have both an x and y axis, we can add additional detail to get the white bar dividers displaying.

First, adjust the label so it is aligned middle centre.

Add Horizontal – Next Value Diff to Columns. Ensure the table calc settings are Table (across).

Remove both the Colour and the Cumulative % of Total fields from the marks card, and then add Horizontal – Next Value Diff as a discrete (blue) pill to Colour. Remove all gridlines, zero lines, axis ticks and row/column dividers. Change the Size to be Manual and reduce size. Make the chart dual axis and synchronise the axis.

You should have something similar to the above, assuming the colours for the Horizontal – Next Value Diff are distinguishable enough.

I adjusted the colours to set 0 to be a transparent colour (refer to this blog to learn how to add a transparent colour hex code into your custom colour palette, and then 0.99 to be white. Edit the x-axis and fix to be from 0 to 1. The white dividers between the relevant months should now be noticeable.

To set the dividers between the Sub-Category, I decided I just needed to use a constant 0 reference line. For this I needed

Vertical Ref Line

IF [Vertical – Next Value Diff] = 1 THEN 0 END

Add this to the Detail shelf on the All marks card, and verify the table calc setting for the Vertical – Next Value Diff calc is set to Table (down).

Right click on the y-axis and Add Reference Line. Set it per cell using the Vertical Ref Line field, with no label or tooltip displaying. Set the Line properties to be white, 100% opacity and a thick width.

Then edit the y-axis to also be fixed between 0 and 1. The lines separating the Sub-Category should now be prominent.

Then it’s just a case of tidying up – hide the axis, stop tooltips from displaying, and then add to the dashboard, setting as fit to entire view.

If need be you may want to tweak either the size of the bar on the Horizontal – Next Value Diff marks card, or change the thickness of the reference line to get similar sizes.

I have no idea when I’d ever need to use this type of display, but I enjoyed the puzzle and discovered something new which is what I love about using Tableau!

This version of my published viz is here.

BUT….

after writing all this up, and stepping away for a bit, I suddenly realised I’d over-complicated things! I didn’t need a dual axis after all. I could just use a reference line to show the dividers between the months, exactly like I did for the 0 constant reference line – doh! So I made some adjustments..

Amend

Horizontal – Next Value Diff

IF ([Colour] = LOOKUP([Colour],1) OR LAST()=0) THEN 0 ELSE 1 END

Then create

Horizontal Ref Line

IF [Horizontal – Next Value Diff] = 1 THEN 1 END

Then remove the Horizontal – Next Value Diff field from the Rows shelf, so there is no longer a dual axis. Add Horizontal Ref Line to Detail , and display the x-axis MIN(1.0) axis, and add a reference line per cell which references Horizontal Ref Line. Apply the same settings to the reference line as detailed above.

And this resulted in a much simpler looking viz

I know I could reduce the number of calcs used, but I like to have ‘building blocks’ to follow my thought process. I have published the simpler version within the same workbook on a separate tab, here.

Happy vizzin’!

Donna

Can you create a jitterfly chart?

Donna Coles1 Comment

This week it’s the Tableau Conference edition – and as I was at #data23, I’m a bit delayed in getting the challenge and solution posted – too much going on and too much sleep to catch up on over the weekend 🙂

It was great to catch up with the #WOW crew in person and to meet so many participants at the social nights out and at the hands on live session too. And thank you for all those who complimented me on this blog – it really does make the time and effort I put into it worthwhile, when I get to hear how much it has helped you all!

Here’s a few pics from the Tableau Conference, and a massive shout out to Chris McClellan, who made me a custom WOW t-shirt!

Ok, enough of the pre-amble, onto the challenge and solution. Lorna set this challenge to recreate what she referred to as a ‘jitterfly’ chart.

Modelling the data

The data needs to be downloaded from data world. This contains multiple files and it was expected to connect to the central_trend_2017_base.xslx file. This file contains multiple sheets. The Population – Females and Population – Males sheets need to be unioned together in the data source canvas.

Drag Population – Females onto the canvas, then drag Population- Males on too, and drop it when the Union option appears beneath the Population – Females

You’ll know you’ve done it right, if you only have a single object in the canvas and the union symbol is displayed

The data displays all years in separate columns – we need to transpose this, so we have a column for Year and a column for the population value.

Click on the first year column (2011), then scroll across to the last year column (2050). Hold down shift and click the last column, and all the columns in between should be highlighted/selected. Right-click on any of the selected columns and select Pivot.

Rename the Pivot Field Names column to Year and rename Pivot Field Values to Population.

To tidy things up a bit, hide the following fields (right click on column and hide) : Sheet, Table Name, Gss Code, Component. Also, change the data type of the Year field to be a number rather than a string. This should leave you with 5 columns

Navigate to Sheet 1 and in the left hand data pane, drag Age from the lower ‘measures’ section to above the line and into the dimensions section. Age isn’t going to be something we aggregate (sum/avg) etc – it is simply a categorical property of the record. This step isn’t critical, but I just like things to be neat :-).

Finally, though it isn’t stated in the requirements, the data relating to District = London needs to be excluded, as it is a summarised total of the other rows. To handle this I added this exclusion as a Data Source Filter, so once applied, I didn’t have to worry about it when I built the chart. Right click on the data source listed on the top left, and select Edit Data Source Filters, then add a condition to exclude the London District.

Creating the calculations

The chart requires a user to select two years to compare – I’ll refer to these as the Primary and Secondary year. We’ll create parameters to enable the selections.

pPrimaryYear

Integer parameter, defaulted to 2033 and displayed in ‘2033’ format (ie no commas). It should allow all other years to be selected (easiest way to get this populated is to right-click on the Year field in the data pane, and Create > Parameter). If you don’t do this, then use the Add values from button to select the Year field to populate the list.

pSecondaryYear

as above, but default to 2023 (the easiest way to create this parameter is to duplicate the first and just amend the name and default value).

With these parameters, we can then create the calculated fields needed to present the relevant values for each year and gender (as hinted by Lorna).

Population – Primary Male

IF [Year] = [pPrimaryYear] AND [Sex] = ‘male’ THEN [Population] END

format this to K with 1 dp

Population – Primary Female

IF [Year] = [pPrimaryYear] AND [Sex] = ‘female’ THEN [Population] END

format this to K with 1 dp

Population – Secondary Male

IF [Year] = [pSecondaryYear] AND [Sex] = ‘male’ THEN [Population] END

format this to K with 1 dp

Population – Secondary Female

IF [Year] = [pSecondaryYear] AND [Sex] = ‘female’ THEN [Population] END

format this to K with 1 dp

popping these into a table as below, you can see the results

The final calculation we need is for the age banding. We can’t use the in-built ‘bins’ function as the final ‘bin’ contains the ‘rest’ and not just 10 values. Also if we used ‘bins’ the ‘labels’ would be based on the data values and not a custom display as we have here.

Age Bracket

IF [Age] <= 10 THEN ‘<=10’
ELSEIF [Age] <=20 THEN ’11-20′
ELSEIF [Age] <=30 THEN ’21-30′
ELSEIF [Age] <=40 THEN ’31-40′
ELSEIF [Age]<=50 THEN ’41-50′
ELSEIF [Age]<=60 THEN ’51-60′
ELSEIF [Age]<=70 THEN ’61-70′
ELSEIF [Age]<=80 THEN ’71-80′
ELSE ’80+’
END

Note – I altered the calculation and label to be <=10 rather than just <10 as is shown in the solution

Building the Viz

On a new sheet, add Age Bracket to Rows, Population Primary Male to Columns and District to Detail. Change the mark type to circle. Manually re-sort the Age Bracket values so <=10 is listed at the top (just drag the value from the bottom to the top).

Drag Population Primary Female onto the canvas and drop it on the Population Primary Male axis when the double green column symbol appears

This will make the values for both male & female display on the same axis, and Measure Names and Measure Values automatically gets added to the viz. But the values are all displaying in the same direction (the positive axis).

To resolve this, double click into the Population Primary Female pill that is in the Measure Values box underneath the marks card and type in *-1 to the end of the pill and press return

The female values will then be displayed in the opposite direction. Adjust the colours of the Measure Names legend to suit.

To make the dots ‘jitter’, that is appear in a random vertical position, we need a measure (green pill) on the Rows so we generate a y-axis.

Now typically when I am creating jitter plots I use the undocumented RANDOM() function which generates a random number between 0 and 1. The function is undocumented, as it only works for some data sources (excel being one). Using RANDOM() was something I mentioned to several attendees of the Live WOW session at Tableau Conference.

However, due to a later requirement, you’ll need to use a different function instead – in this case INDEX(). For clarity I created an explicit calculated field for this

Jitter

INDEX()

INDEX()is a table calculation that creates a unique sequence number from 1 to n for each record in the table partition. In this case the partition is each Age Bracket. Add Jitter to Rows and adjust the table calculation setting so it is computing by District only. Set the fit of the chart to Fit Width to ensure you can see the display better.

Adjust the colour of the marks to 50% opacity.

To handle the detail displayed on the Tooltip we need to create some additional fields for the population values, these ones not split based on gender.

Population – Primary Year

IF [Year] = [pPrimaryYear] THEN [Population] END

format to K with 1 dp

Population – Secondary Year

IF [Year] = [pSecondaryYear] THEN [Population] END

format to K with 1 dp

Then

Population Change

(SUM([Population – Primary Year]) – SUM([Population – Secondary Year]))/SUM([Population – Primary Year])

custom format this as ▲0.0%;▼0.0%;0.0%

Add Sex, Population – Secondary Year, Population – Primary Year and Population Change to the Tooltip and adjust accordingly.

The coloured bands are based on the average for the ‘primary’ year and sex. Add Population Primary Female and Population Primary Male to the Detail shelf. Double click on Population Primary Female and type in *-1 to ensure you get the relevant negative value.

Right click on the ‘value’ x-axis and Add Reference Line.

Change the option to be a reference band per pane, and set a band to go from Population Primary Female *-1 : Average to a Constant of 0. Ensure no labels/tooltips display, and set the fill colour of the band accordingly.

Add another reference band for the which goes from constant 0 to the Average of Population Primary Male. Adjust fill colour to suit.

Add Population Secondary Male and Population Secondary Female to the Detail shelf. Double click into the Population Secondary Female pill and add *-1 to the end.

Right click on the ‘value’ x-axis and Add Reference Line.

Add a reference line that is the Average of the Population Secondary Female * -1 field. Adjust colour and thickness of line to suit (I used the middle thickness and line coloured at 80% transparency).

Repeat the same to add an average reference line for the Population Secondary Male field.

The final step is to add the age banding label into the centre of the viz.

Double click into the Columns shelf and type MIN(0). This will create a secondary axis with a new marks card. Remove all the pills except District from the MIN(0) marks card. Add Age Bracket to the Label shelf. Adjust the label properties as below – to label the Min/Max per pane; at the District field level, and label the maximum value only.

This positions the label in the same place on each age banding. This works because we have used INDEX() to control the jittering which means the maximum value is always the same for each bracket. If we had used RANDOM() to define the jittering, there would be no guarantee the same maximum value would have existed for every banding.

Reduce the opacity to 0% and size of the circle to be as small as possible on the MIN(0) marks card, and then make the chart dual axis and synchronise the axis.

Finally format the chart by

  • Hide the Age Bracket column (uncheck show header)
  • Hide the Jitter axis (uncheck show header)
  • Hide the MIN(0) axis (again uncheck show header)
  • Format the Value axis so the title is Population, and the scale is in 0K format and positive in both directions (custom format ,##0,”K”;#,##0,”K”) NOTE wrapping the K in “” ensures the display is retained when publishing to Tableau Public – thank you Deborah for the tip!)
  • Hide the nulls indicator (right click – hide indicator)
  • Remove all gridlines, zero line, axis ticks etc
  • Remove column dividers
  • Set Row dividers to white with the widest thickness
  • Set the chart to fit entire view

Add the sheet to a dashboard. Use a text object to display the title and sub-tile which should reference the pPrimaryYear and pSecondaryYear parameters. Float the parameter controls and resize to give the appearance of just the drop down option – this will take a bit of tweaking to get just right, and you may need to edit via Tableau Public to get the positioning right.

My published viz is here.

Happy vizzin’!

Donna

Can you combine relative and custom date filters?

Donna Coles1 Comment

For this week’s challenge, Kyle revisited a previous challenge from 2020, but remade it over utilising some of the newer features of Tableau, specifically Dynamic Zone Visibility. I blogged my solution to the original challenge here, so this blog will lift some of the techniques (and documentation) I employed directly.

Define the parameters

The first step in this challenge is to define all the parameters needed, these being :

pDateSelector

A string parameter just set to contain the value Last 30 Days

This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.

pDays

An integer parameter defaulted to 120

pStartDate

A date parameter defaulted to 01 Jan 2023

pEndDate

Another date parameter defaulted to 01 Aug 2023

The chart needs to adjust based on a measure selected, so we need to capture the measure option selected

pSelectedMeasure

string parameter defaulted to Sales

This parameter will be set by a parameter action, so again there is no need to actually list the possible values.

Finally, I also created a parameter

pToday

Date parameter defaulted to 02 May 2023.

The requirements indicate the information displayed should be restricted to ‘today’, using the TODAY() function. However given the data set is static, if I use TODAY() and look at this in a year’s time, nothing will show. So instead I have ‘hardcoded’ ‘today’ using this parameter.

Using this parameter I then created

Order Date < Today

[Order Date] <= [pToday]

and added this as a data source filter set to True. This ensures that all the sheets I build is then automatically ignoring and data where the Order Date is 3rd May 2023 onwards.

Building the time-series chart

We need to determine which measure to show based on the selection made by the user and stored in the pSelectedMeasure parameter. This will be Sales, Profit or a count of the number of orders. First we need to create

Count Orders

COUNTD([Order ID])

and then we can create

Measure to Display

CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Orders’ THEN [Count Orders]
END

Add Order Date set to the continuous day level (green pill) to Columns and Measure to Display to Rows. This gives us all data from the earliest day in the data set up to 2nd May 2023.

We need to restrict this based on the ‘date selector’, so create

In Timeframe

CASE [pDateSelector]
WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[pToday])
WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[pDays],[pToday])
ELSE [Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate]
END

If ‘last 30 days’, get the data that is greater than 30 days ago; if ‘last n days’, get the data that is greater than the last ‘n’ days ago, otherwise get the data between the start & end dates specified.

Add this to the Filter shelf and set to True.

Add another instance of Measure to Display to Rows, make dual axis and synchronise axes. Change the mark type of the first marks card to be Area and set the other to explicitly be a line.

Add pSelectedMeasure to the Colour shelf of the All marks card. Adjust the colour of the ‘Sales’ value accordingly.

If you’re not already displaying it, show the pSelectedMeasure parameter input, and manually change the value by typing in ‘Profit’. The chart will change colour, so again adjust accordingly. Repeat the process by typing in ‘Orders’.

Set the parameter back to ‘Sales’.

The tooltip needs to display different formatted values, so we need a couple of fields to handle this

Tooltip- Sales or Profit

IF [pSelectedMeasure] <> ‘Orders’ THEN [Measure to Display] END

Format this to $ with 0 dp, and set to use ( ) when value is negative.

Tooltip – Orders

IF [pSelectedMeasure] = ‘Orders’ THEN [Measure to Display] END

Format this to a number with 0 dp.

Add both these fields to the Tooltip shelf of the All marks card, and adjust the tooltip accordingly, positioning the two ‘Tooltip’ fields directly adjacent to each other

Hide the right hand axis (right click, uncheck show header), and edit the left hand axis to set the axis title to be sourced from the pSelectedMeasure parameter

Remove all gridlines, zero line, axis ticks etc and row/column dividers, and edit the date axis to remove the axis title. Name the sheet ‘Chart’ or similar.

Building the BANs

  1. Add Measure Names to Columns
  2. Add Measure Names and Measure Values to the Text shelf
  3. Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
  4. Reorder the columns to match the requirement

  1. (Optional) Change the mark type to shape and set the shape to be a transparent shape (see this blog post for more details)
  2. Change the formatting of the Measure Names and Measure Values on the Text shelf to set the size of the font to suit, and align middle, centre
  3. Format the Sales and Profit measures to be $ with 0 dp and ( ) for neg values.
  4. Add Measure Names to the Colour shelf and adjust colours to match the ones used on the line chart. (If you’ve used the transparent shape, edit the label to set the font to match mark colour).
  5. Format the display to remove the row lines
  6. Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
  7. Add In Timeframe = True to the Filter shelf to restrict the data.
  8. Uncheck the show tooltip option from the Tooltip shelf to stop the tooltip from displaying.
  9. Name the sheet ‘BANs’ or similar

Building the Date Selector

On a new sheet, double click into the Columns shelf and type in MIN(0.0) Change the mark type to Shape.Create a new field

Label: Last 30 Days

‘Last 30 Days’

And add this to the Label shelf.

Create a new field

Is Selected Date Option – Last 30 Day

[pDateSelector] = [Label:Last 30 Days]

And add this to the Shape shelf. Adjust the shape for the ‘True’ value to be a filled circle.

Show the pDateSelector parameter and change the text in some way. This should change the ‘shape’ to ‘False’. Set this shape value to be an open circle.

Change the colour to be a dark grey, and increase the font size. Uncheck ‘Show Tooltip’.

Now type in another instance of MIN(0.0) to Columns. Create the following fields

Label:Last N Days

‘Last N Days’

and

Is Selected Date Option – Last N Days

[pDateSelector] = [Label:Last N Days]

ON the 2nd MIN(0.0) marks card, replace the fields on the shape and label shelves with these ones.

By default, the ‘last n days’ shape should be set to false, so make sure it’s an open circle. Change the value in the pDateSelector parameter to ‘Last N Days’ and the shape should now be ‘true’ – set to a closed circle.

Create a 3rd instance of MIN(0.0) on Columns and create

Label:Custom Dates

‘Custom Dates’

and

Is Selected Date Option – Custom Dates

[pDateSelector] = [Label:Custom Dates]

and repeat the process above

Edit the axis of each to be fixed from -0.2 to 2. This has the effect of ‘left aligning’ the marks.

Hide the axis, and remove the row & column dividers, all gridlines and zero lines and axis lines.

Add Measure Names to the Detail shelf of the All marks card. The right click on Measure Names in the left hand data pane and select Alias. In the dialog box that presents, alias the 3 MIN(0.0) fields as per the 3 date selector options.

This step is the key to enabling the parameter action that will be set up to pass the appropriate ‘value’ into the pDateSelector parameter

Finally format the background of the whole worksheet to be grey.

Controlling the visibility of the selections

On the dashboard, I used a vertical layout container to add my title, the BANs and the Chart. Between the title and BANs, I added a horizontal container. On the left side of that I added the Date Selector sheet. On the right I then had another vertical container which contained other containers to display the start & end data parameters. It took a bit of ‘twiddling’ to get everything where I wanted.

I added the pDays parameter as a floating object and positioned it below the Last N Days option. I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Last N Days calculated field.

Similarly, for the date input fields, I had the section all within a single container, so I set the visibility at the container level, rather than the object level (although you could repeat the step against all the objects you need to hide individually). For the container, I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Custom Dates calculated field.

Adding the interactivity

To change the timeframe displayed in the BANs and chart, create a dashboard parameter action

Set Date Selection

On select of the Date Selector sheet, target the pDateSelector parameter, passing though Measure Names. When the selection is cleared, reset to ‘Last 30 Days’.

To change the measure displayed in the chart, create a dashboard parameter action

Select Measure

On select of the BANs sheet, target the pSelectedMeasurew parameter, passing through Measure Names. When the selection is cleared, reset to ‘Sales’.

Prevent the BANs and Selected Date option from being selected/highlighted

By default, clicking on one of the BAN numbers, or selecting an option in the date selector, will leave the option chosen ‘highlighted’ or ‘selected’ while the other options are ‘faded out’. To

Create two calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of the BANs sheet and the All marks card on the Date Selector sheet.

Then on the dashboard, create a dashboard filter action

Deselect BANs

On select of the BANs sheet on the dashboard, target the BANs sheet directly, passing through selected fields where True = False. Show all Values when the selection is cleared.

Repeat the exact process for the Date Selector sheet, creating a dashboard filter action called Deselect Date Selector.

You should now have a complete dashboard. My published viz is here.

Happy vizzin’!

Donna

Custom Map Analysis

Donna Coles1 Comment

For the final week of Community Month, my colleague, Nik Eveleigh, posed this challenge to apply some tricky filters to a map. Let’s dive straight in!

Identifying the States and Profitability

The core functionality of the map display is driven by a parameter allowing the user to select which set of states they want to analyse, so let’s set this up first.

Select a grouping

string parameter containing 6 entries in the list and defaulted to ‘Top 20 States by Sales’

To identify the top 20 States by Sales, we need to create a Set. Right click on State/Province > Create > Set and use the ‘Top’ option to create the set based on top 20 sum of Sales.

Top 20 States by Sales

To verify this is working as expected, add State/Province to Rows, Sales to Text and sort descending. The add Top 20 States by Sales to Rows and you should see the first 20 rows with In and the rest listed as Out.

To identify the states selected when the ‘Custom States List’ option is selected, we’ll also need another set to store the selections. Right click on State/Province > Create > Set, leave the list of states all unselected and rename the set

Selected States

To verify this is working, on a new sheet add State/Province to Rows and Selected States to Rows too. Then on the context menu of the Selected States pill, choose Show Set to get the list of States displayed. Select the first couple in the list and see the value in the table change from Out to In.

It is this set control filter list that will be used to provide the selection when the appropriate value in the Select a grouping parameter is chosen.

While the sets display In or Out when shown in the table, they are actually booleans with the equivalent of a True or False. We now need to build a boolean field which will encapsulate all the relevant states included based on the parameter option selected.

Filter States

CASE [Select a grouping]
WHEN ‘Top 20 States by Sales’ THEN [Top 20 States by Sales]
WHEN ‘Central Region’ THEN IF [Region] = ‘Central’ THEN TRUE ELSE FALSE END
WHEN ‘Southern Region’ THEN IF [Region] = ‘South’ THEN TRUE ELSE FALSE END
WHEN ‘Eastern Region’ THEN IF [Region] = ‘East’ THEN TRUE ELSE FALSE END
WHEN ‘Western Region’ THEN IF [Region] = ‘West’ THEN TRUE ELSE FALSE END
WHEN ‘Custom States List’ THEN [Selected States]
END

Let’s sense check the workings of this too. On a sheet add State/Province, Region and Filter States to Rows. Show the Select a grouping parameter. Also click on the Selected States field in the left hand data pane and Show Set, so the list of states shows. Ensure all are unchecked. Depending on the option selected in the Select a grouping parameter, then Filter States column should display true or false.

If you select a state from the list, this should only present as True when the Custom States List option is selected

Right, now we know how to identify the states we want, we can start to look at understanding their profitability. Firstly we need to get the profit ration

Profit Ratio

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

format this to % with 0 dp.

Profitability

IF ATTR([Filter States]) THEN

IF [Profit Ratio] < -0.25 THEN ‘Highly Unprofitable’

ELSEIF [Profit Ratio] >= -0.25 And [Profit Ratio] < 0 THEN ‘Unprofitable’

ELSEIF [Profit Ratio] >=0 AND [Profit Ratio] < 0.25 THEN ‘Profitable’

ELSE ‘Highly Profitable’

END

ELSE ‘Not Included’
END

If the states is one of the filtered ones then work out isn’t profitability ‘bracket’ otherwise report as ‘Not Included’.

Building the Map

On a new sheet double-click on State/Province to automatically generate the map. If the map doesn’t display with US and Canada Edit Locations (via the Map menu) and ensure your settings are as below

Display the Show a grouping parameter and have it set to Top 20 States by Sales. Add Profitability to Colour and adjust colours accordingly.

Click on the Selected States field in the left hand data pane and Show Set, so the list of states shows, then test changing the values in the Select a grouping parameter and see the display change.

Clean the map up by clicking Map -> Background Layers, and then unchecking all the options in the Background Map Layers section displayed on the left hand side.

To label the highlighted state we need

Label – PR

IF ATTR([Filter States]) THEN [Profit Ratio] END

format to % with 0 dp and then add to the Label shelf and set the font to bold.

Add Profit Ratio to the Tooltip shelf and adjust the tooltip.

To display the summary Profit Ratio values for all the filtered states vs those not selected, we need

PR by Filter State

{FIXED [Filter States]: [Profit Ratio]}

and then

PR Selected States

ZN({FIXED:AVG(IF [Filter States] THEN [PR by Filter State] END)})

Custom format this with 0%;-0%;–

This formatting with show values with 0dp for positive and negative values and — when no values exists.

Repeat the process to create

PR Non Selected States

ZN({FIXED:AVG(IF NOT([Filter States]) THEN [PR by Filter State] END)})

and apply the same formatting above.

Add both PR Selected States and PR Non Selected States to the Detail shelf and change the aggregation to Average.

Then click on a state on the bottom left of the map (I chose California) and select Annotate > Mark. Add the reference to the PR Selected States and supporting text into the annotation dialog. The when completed, manually move the annotation to the space to the left of the map. Format the annotation to add a border, round the edges and remove the line. You many need to re-edit the annotation to rec-centre the text.

Repeat a similar process, by annotating a state on the right hand side and referencing the PR Non Selected States field instead.

Finally remove all row & column dividers and hide the map options (Map -> Map Options -> uncheck all selections)

Hiding the Selected States control

In order to control visibility of the Selected States list, we need a boolean field

Custom States Selected

[Select a grouping] = ‘Custom States List’

Once all objects have been added to the dashboard and arranged where you want, click on the Selected States control, so it is selected via a grey border, then on the left hand Layout pane, select the Control visibility using value checkbox and choose the Custom States Selected field

The State list will now only display when the Select a grouping parameter contains the ‘Custom States List’ value.

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

Happy vizzin’!

Can you build an insightful text table?

Donna Coles2 Comments

Regular #WOW participant, Caroline Swiger, set the guest challenge for community month this week, to recreate a visual table in a single sheet. She was heavily inspired by this Super Advanced Tables viz built by my colleague Sam Parsons which he discusses in this YouTube video. This was a concept I’d been meaning to try for a while, so having this set was ideal, as I now get to try it out and blog about it 🙂

I think the easiest way to approach this blog is simply column by column. When I tackled this initially, I ticked off as much as I could remember to do initially, and then referenced Sam’s video when it came to building the bars. As a consequence of that, I did then have to add a field that meant I had to adjust all the existing columns I’d made. If you follow this blog from start to finish, you shouldn’t need to do that.

This table revolves around utilising what I refer to as ‘fake axis’ to allow you to use different mark types other than text within each cell of the table. All of the columns in this table make use of a MIN(0) measure to act as the ‘fake axis’. Whilst we can just type MIN(0) into the Columns shelf each time, we’ll give these measures a specific name relating to the data being presented in each column, so we can easily find them on the Marks card if ever we need to make an adjustment.

Initial Set Up

Create a new field

Y-Axis Position

MIN(0.5)

Add Sub-Category to Rows and add Y-Axis Position to Rows as well. Having a measure on the Rows shelf is necessary for when we come to build the bar chart columns, and I’ll explain why in that section. Edit the Y-Axis Position axis to be fixed from -1 to 2.

Sales Rank Column

The main focus of the data is for the latest year values. So we need to identify various measures relating to just this year.

Latest Year

{MAX(YEAR([Order Date]))}

For the data I’m working with, this returns the year 2023, and from this we can then determine

Current Sales

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

Format this to $ with 0 dp.

Apply a Sort to the Sub-Category pill on Rows based on this field.

Create a new field

Sales Rank

RANK(SUM([Current Sales]))

and a field for the ‘axis’

Sales Rank Axis

MIN(0)

Add Sales Rank Axis to Columns. Change the mark type to circle, increase the size a bit. Change the Colour to dark grey.

Add Sales Rank to the Label shelf and edit the table calculation so that it is computing by Sub-Category. Align the text middle centre and bold the text.

Current Sales Bar

Create a new field

Current Sales Axis

MIN(0)

and add to the Column shelf. Change the mark type of this to a bar. Remove Sales Rank from the Label shelf, and replace with Current Sales. Change the alignment of the label to be top left, and ‘un-bold’ the font. Add Current Sales to the Size shelf, then click on the Size shelf button, and change the size from Manual to Fixed, aligned left. This action will make the bars look like proper horizontal bars, with the same starting position, and a length proportionate to the value of Current Sales.

The manual vs fixed sizing option only becomes available when there is a measure (green pill) on both the Rows and the Columns, which is why we needed to create the Y-Axis Position field. Without this, we would only have had a slider size option which wouldn’t have achieved the desired result. The ‘height’ or depth of the bar is based around the position on the Y-Axis (ie 0.5) and the scale of the axis. Fixing the axis from -1 to 2 as mentioned at the start, positions the bar roughly central to where we want it and with a relatively narrow height. If you adjust the axes, you will see how this impacts the bar chart.

YoY Sales Column

For this column, we need to know what the previous year’s sales were, the % YoY difference and whether that difference was positve, negative or didn’t change

PY Sales

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

YoY Sales Diff

(SUM([Current Sales]) – SUM([PY Sales]) ) / SUM([PY Sales])

format this to a % with 0 dp

YoY Sales

SIGN(ROUND([YoY Sales Diff],2))

The SIGN() function is a more efficient way than saying IF value is >0 THEN… ELSEIF value < 0 THEN…. ELSE … END. SIGN() returns +1, 0 , -1 depending whether difference is positive, negative or the same.

We also need

YoY Sales Axis

MIN(0)

Add this field to the Columns shelf. Change the mark type to shape and add YoY Sales as a blue discrete pill to the Shape shelf. Use the arrow shapes and assign to the 1, 0 -1 values accordingly. The arrow shapes are available on the challenge page if they’re not already available for you (you might find them in the Arrows shape palette if that exists). Refer to this blog to understand how to add custom shapes.

Add YoY Sales as a discrete blue pill to the Colour shelf and adjust the colours using the blue, red and light grey options referenced in the requirements.

Add YoY Sales Diff to the Label shelf and align middle right. Unbold the text.

Profit Rank Column

Create new fields

Profit Rank Axis

MIN(0)

and

Current Profit

IF YEAR([Order Date]) = [Latest Year] THEN [Profit] END

formatted to $ with 0dp, and then create

Profit Rank

RANK(SUM([Current Profit]))

Add Profit Rank Axis to Columns and then add Profit Rank to the Label shelf. The mark type should already be set to a circle and coloured correctly to dark grey. Align the label middle centre (if it’s not already), and adjust the table calculation so it is computing by Sub-Category.

Current Profit Column

Create

Current Profit Axis

MIN(0)

Add to Columns and change the mark type to square. Add Current Profit to the Label shelf – align middle centre and un-bold. Add Current Profit to Colour. Edit the diverging colour legend. Click on the dark orange coloured square at the left side of the colour scale and change the colour to match the red hex code provided. Similarly click on the blue colour square at the right side and change the colour to match the blue provided. Tick the Use Full Colour Range option.

Create a new field

Current Profit – Size

MIN(1)

and add to the Size shelf, then increase the Size slider to as large as possible.

Change in Profit Columns

The positive and negatives values indicating the change in profit from the previous year is built as two separate columns. So we need

Change in Profit Neg Axis

MIN(0)

Change in Profit Pos Axis

MIN(0)

We need to determine the profit for the previous year

PY Profit

IF YEAR([Order Date]) = [Latest Year]-1 THEN [Profit] END

and with this we can work out the change

Change in Profit

SUM([Current Profit]) – SUM([PY Profit])

We then also need to explicit fields to plot on each axis.

Profit Change -ve

IF SIGN([Change in Profit]) = -1 THEN [Change in Profit] END

This field will only contain a value if the Change in Profit field is negative. Format this to $ with 0dp

Profit Change +ve

IF SIGN([Change in Profit]) = 1 THEN [Change in Profit] END

This field will only contain a value if the Change in Profit field is positive. Format this to $ with 0dp

Add Change in Profit Neg Axis to Columns and change the mark type to bar. Add Profit Change -ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change -ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.

Now repeat. Add Change in Profit Pos Axis to Columns and change the mark type to bar. Add Profit Change +ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change +ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.

Then right click on the Change in Profit Pos Axis and Add Reference Line. Create a Constant reference line of 0 value. This line won’t be that visible at this point, but once we remove all the other formatting, it will display.

Profit Ratio Column

Create the field

Profit Ratio Axis

MIN(0)

and

Current Profit Ratio

SUM([Current Profit])/SUM([Current Sales])

format this to % with 1 dp.

and

Profit Ratio – Colour

SIGN([Current Profit Ratio])

Add Profit Ratio Axis to Columns and change the mark type to Shape. Select the rounded shape from the shape palette (again this is provided on the requirements page and needs to be added as a custom shape).

Add Profit Ratio – Colour to the Colour shelf as a blue discrete pill and adjust accordingly to use the red and light grey options provided. Increase the Size of the shape as necessary, and then add Current Profit Ratio to the Label shelf.

Tidying up

So we’ve built the table, just need to clean it up by

  • Right click the y-axis and uncheck show header
  • Right click one of the x-axis and uncheck show header
  • Right click on the Sub-Category label at the top of the column and select hide field labels for rows
  • Format the chart and remove all column dividers
  • Remove all grid lines, zero line, axis rulers and axis ticks.
  • Select the All Marks card and click on the Tooltip button and uncheck show tooltips

The sheet can now be added to a dashboard. Use a horizonal container positioned above the chart and add text objects to create the column labels. My published viz is here.

Happy vizzin’!

Donna