This week, Kyle set the challenge to test our use of functionality released in 2024.3 – spatial parameters. I hadn’t used these before, so did have a watch of the video link posted in the challenge, which was really useful.
Accessing the data
Kyle provided two links to web pages for the data. You need to download the zipped Shapefile from each
Once downloaded, extract each zip file. There will be a SHP (shape) file in each set of unpackaged files. In Tableau desktop, you will need to create 2 connections to a spatial file, one to each shp file
Build the State Map
From the School_District_Characteristics data source, double click on Geometry to automatically create a map.
Add Lea Name to the Detail shelf and add Statename to the Filter shelf, and select Washington.
From the Map menu, select Background Maps > Dark to set the background.
Name the sheet State or similar.
Building the District Map
On a new sheet, double click on Geometry from the School_Neighbourhood_Poverty_Estimates data source to automatically create another map.
Add Name to Detail.
On the Map menu, select Background Layers, and then set the Style to dark and tick the Street,Highways etc option.
Name the sheet District or similar.
Create the spatial parameter and filter
Create a new parameter to store the geometric data
pLea
a spatial parameter, defaulted to Empty
In the School_Neighbourhood_Poverty_Estimates data source, create a calculated filed
Filter
INTERSECTS([pLea],[Geometry])
this will return true if the geometric data that represents the state(s) selected from the state map overlap with the geometric data that represents the schools.
Adding the interactivity
Create a dashboard, placing the State and the District sheets side by side in a horizontal layout container.
Create a dashboard parameter action
Set Lea
On select of the State sheet, set the pLea parameter passing in the value from the Geometry field. Set the value to Empty when the selection is cleared.
Click on the State map (which will populate the pLea parameter). Then navigate back to the District sheet and add the Filter calculation to the Filter shelf and select True, which will result in the District map being filtered
Return to the dashboard and click on the State map to see the District map change.
When no states are selected though, the District map is empty, which is the desired behaviour, but we want to fill the display with the State map (ie hide the space where the District map is).
To do this, create a calculated field (in either of the data sources)
State Selected
NOT ISNULL([pLea])
This will return True if there is something in the pLea parameter.
Back on the dashboard, select the District object on the dashboard, then from the Layout tab, check the Control visibility using value checkbox, and select the State Selected field
Remove the title from the District sheet on the dashboard, and now when all states are unselected the State sheet fills the view.
Finalise the dashboard by adding the title and any other information required. Set the background of the dashboard to dark grey and show the State parameter.
Erica set the #WOW2024 challenge this week, asking us to recreate this ‘drillable’ functionality within a single dashboard.
To achieve this, I used 4 sheets
A large map
A small map
A table of data
A navigational control
along with parameter actions to drive the interactivity, and dynamic zone visibility to control what displayed when.
Building the large map
On a new sheet, double click on State/Province to automatically generate a map of the United States – if this doesn’t display by default, then make sure the location of your map settings are referencing the USA (Map menu > Edit Locations). Hide the ‘unknown’ indicator displayed bottom right. Change the mark type to map to create a filled map. Change the colour to a grey (I used #c0c0c0). Add State/Province to the Filter shelf and select Arkansas. Show the filter.
Click on the Tooltip button, and delete all the text in the dialog.
Now drag City onto the map and drop it on the section that appears labelled Add a Marks Layer. On the ‘City’ marks card that now appears, change the mark type to circle. Move City to the Label shelf and then also add State/Province to Detail. Change the colour of the circles to dark grey (I used #767f8b).
Update the Tooltip on the City marks card. Turn off all the map options to stop the pan/zoom map control options from appearing (map menu -> map options -> deselect all the options). Remove all row/column dividers.
Name the sheet Map-Large or similar.
Building the Small Map
Duplicate the Map-Large sheet, and name the new sheet Map-Small. Remove all background layers so just the state outline remains (Map menu -> background layers -> set washout to 100%).
On the Label shelf of the City marks card, uncheck show mark layers so the name of the city is not displayed and click on the Tooltip shelf and uncheck show tooltips, so they aren’t displayed either.
On this map, we need to highlight a selected City. For this we need a parameter to capture the City, and a calculated field to identify which City has been selected. We will also need a parameter to capture the State/Province to ensure the interactivity works as required.
pSelectedState
string parameters defaulted to ” (ie empty string)
pSelectedCity
string parameter defaulted to ” (ie empty string)
Is Selected City
[City] = [pSelectedCity]
Add Is Selected City to the Colour shelf of the City marks card.
Show the pSelectedCity parameter and type in ‘Fayetteville’. The colour legend should display values for True & False. Adjust colours to suit.
Also add Is Selected City to the Size shelf and adjust the sizes accordingly.
Building the table
On a new sheet, add
State/Province to Filter and filter to ‘Arkansas’
Add Is Selected City to Filter and filter to True
Add Customer Name, Order ID and Product Name to Rows
Right-click on the State/Province field in the Filter shelf and apply to worksheets > selected worksheets and select the Large Map & Small Map sheets. This makes the filter shared between all those sheets.
Create a new field
Unit Price
SUM([Sales])/SUM([Quantity])
and add to Text.
Drag Quantity onto the canvas and drop on the ‘unit price’ column when you see show me appear. This will automatically add Measure Values to Columns and Measure Names to Filter.
Do the same for Sales. Reorder the pills in the MeasureValues box so they are arranged as required.
Add subtotals (Analysis menu > Totals > Add all subtotals), then display at the top (Analysis menu > Totals > Column Totals to Top).
Right-click on the Customer Name pill in Rows and uncheck Subtotals from the context menu.
Format the table: set the worksheet background colour to light grey and set the total (pane & header) background colour to a shade darker.
Adjust the width and height of the columns and rows to suit.
Uncheck show tooltip from the Tooltip shelf, and name the sheet Table or similar.
Building the Navigation sheet
The ‘back to main map’ navigational display is actually a worksheet rather than a navigational object on a dashboard, as we need to apply parameter actions to it in order to reset some parameters.
On a new sheet, double click into the space on the marks card below the shelf buttons, and type ‘X’
Change the mark type to Text and move the X field to the Text shelf. Update the text to include the additional words, adjust the font size and then align middle centre. Uncheck Show tooltip.
Create the following fields
City – Reset
”
State – Reset
”
and add both to the Detail shelf. Name the sheet Navigation or similar.
Building the dashboard
Using horizontal and vertical layout containers, arrange all the objects on a dashboard. You will need to adjust the background colour of some objects and the padding to get everything looking as expected. This can be quite tricky and is very hard to explain in a blog. The item hierarchy in the image below will help give you an idea
To draw horizontal or vertical lines, add a blank object to the layout container, change the background colour, reduce the padding to 0 and then adjust the height or width of the object so it is very narrow.
These are the properties associated with the red ‘tab’ in the heading – the background colour is red, inner and outer padding is 0, width is 4px and the object is contained within a horizontal container
For the City/State title, reference the pSelectedState and pSelectedCity parameters, along with a unicode symbol which I copy and past from here.
Adding the interactivity
Add the following dashboard actions
Set State
on select of the Map-Large sheet, set the pSelectedState parameter passing in the value of the State/Province field. When the selection is cleared, retain that value.
Set City
on select of the Map-Large sheet, set the pSelectedCity parameter passing in the value of the City field. When the selection is cleared, retain that value.
Reset State
On select of the Navigation sheet, set the pSelectedState parameter passing in the value of the State – Reset field. When the selection is cleared, retain that value.
Reset City
On select of the Navigation sheet, set the pSelectedCity parameter passing in the value of the City- Reset field. When the selection is cleared, retain that value.
Click around on the dashboard and sense check the parameters appear to be behaving – ie if you click on a city on the large map sheet, the table and small map should update, and if you then click the navigation sheet, the table shouldn’t show anything, and the small map shouldn’t show anything highlighted.
Hide and show the objects
Finally, we need to only show the required objects depending on the actions taken. For this we need some additional calculated boolean fields. Go back to any sheet, and create the following calculated fields
City is Selected
[pSelectedCity]<>”
City Not Selected
[pSelectedCity]=”
Note, these fields just have to exist, they don’t need adding to any sheet.
Navigate back to the dashboard.
Click on the Map-Large object, and on the Layout tab, check the control visibility using value option, and choose the City Not Selected value.
This means that when City Not Selected is true (ie the pSelectedCity parameter is empty), this object will display. When City Not Selected is false (ie the pSelectedCity parameter has a value), this object will be hidden.
Depending on what you’ve been clicking, this object might disappear immediately.
Click on the Table object, and do the same steps, but this time choose the City is Selected field. This means the table will only show then the pSelectedCity parameter has a value.
Apply the same settings so the small map, the table title, and the navigation sheet only show when City is Selected. Note – depending on how you have built the dashboard, you may find you can apply the setting once against a container which contains all the objectsyou want to hiderather than against each individual object.
I also chose to only display the Select a State filter control when the City Not Selected was true.
And hopefully, that should be it. My published viz is here.
For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.
Building the Funnel Chart
I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.
Let’s start by getting the core data into a table, so we can see what we’re aiming for
The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.
There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M
As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.
If we amend the Value field to be a Running Total quick table calculation (and add Value back into the view too), we get
The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.
To resolve this, edit the table calculation and change the Sort order to custom sort by Minimum Stage No Descending
This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the Value column.
However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.
While excluding the Stage 6 from the view (Stage No on Filter shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows
So, we need some additional calculations to help resolve this.
Amount Lost
{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}
This just captures the amount of Stage 6 and ‘spreads it across every row of data.
Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs
Cumulative Value Per Stage
RUNNING_SUM(SUM([Value]))
Replace the Value table calc field with this one, ensuring the table calculation settings are identical to those described above.
Now let’s add the Amount Lost onto the Cumulative Value Per Stage unless we’re at Stage 5, Closed Won
Total Amount Per Stage Inc Lost
IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage] ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END
Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.
Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.
Total Value
{FIXED:SUM([Value])}
meant I could determine
Proportion of Total
[Total Amount Per Stage Inc Lost] / SUM([Total Value])
I formatted this to percentage with 0 dp.
Adding these into the table
If we plotted this information on a bar chart, we’d get this
but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the Proportion of Total.
Position to Plot
(1 – [Proportion of Total])/2
We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.
Stage No to Rows
Stage No to Filter and exclude Stage 6
Stage to Detail
Position to Plot to Columns, adjusting the table calculation as previously described
Change mark type to Gantt bar
Add Proportion of Total to Size (and verify the table calc is set properly)
Hey presto! A funnel!
To finalise
add Stage to Label and align centrally. Make the font bold and match mark colour.
add Cumulative Value per Stage to Colour and reverse the colour range (via the Edit Colours dialog)
Widen each row a bit.
Fix the Position to Plot axis to start at 0 and end at 1 to ensure the funnel is centred exactly.
Add Value and Total Amount Per Stage Inc Lost to the Tooltip and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!).
Hide the Position to Plot axis, and the Stage No column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.
Building the KPIs
We need a few calculated fields to store the required numbers
Won
{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Lost
{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Outstanding
1-([Lost] + [Won])
formatted to a percentage with 0dp.
On a new sheet add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and select Total Value, Won, Lost, Outstanding. Manually re-order the columns.
Format Total Value to be a number displayed in millions with 1 decimal place & prefixed with $.
Add Measure Names to Text and adjust the text as required. Align the text to be centred.
Remove the row banding, and hide the column heading.
Then arrange the two sheets on the dashboard, ensuring both are set to fit entire view.
Erica Hughes set her first #WOW challenge this week, which is all about reference lines. The intention by the #WOW crew is to use this initial challenge as the basis for the next few weeks, so it’s going to be interesting to see how that works out.
But back to this week. We’re building a basic Sales vs Profit Ratio scatter plot by State, but then need various calculated fields to plot the reference lines and the reference box.
Building the basic chart
Adding the dotted reference lines
Adding the reference box
Making the y-axis symmetrical
Highlighting the selected state
Building the basic chart
First up we need to create our favourite calculated field
Profit Ratio
SUM([Profit]) / SUM( [Sales])
format to percentage with 0 dp.
Create the basic scatter plot as below
Remove all gridlines, zero lines and axis rulers and tick marks.
Adding the dotted reference lines
These lines are the median values for the Profit Ratio and Sales.
Sales – Median
WINDOW_MEDIAN(SUM([Sales]))
format to Custom Currency with 1 dp, $ prefix and display units to Thousands (K).
PR- Median
WINDOW_MEDIAN([Profit Ratio])
format to percentage with 0 dp.
Add both these fields to the Detail shelf, and adjust the table calculation settings of both fields so they are computing by State
Add a reference line to the Sales axis (I do this by right clicking on the axis > Add Reference Line, but you can also drag from the Analytics pane).
Set the reference line to be against the Entire Table, using the Sales – Median field, no label, and the tooltip set to custom as below. Set the line to be dotted.
Apply a similar reference line to the Profit Ratio axis, using the PR – Median value instead.
Adding the reference box
I have to admit, this bit took a bit of trial and error. I knew it was going to involve a combination of bands and lines and colouring above and below, but things didn’t always go to plan. It was this post by Jonathan Allenby that helped.
The block is basically bounded by the 25th & 75th percentile of the Sales and Profit Ratio values. So lets create them
Sales – 25th Percentile
WINDOW_PERCENTILE(SUM([Sales]),0.25)
Sales – 75th Percentile
WINDOW_PERCENTILE(SUM([Sales]),0.75)
Add these fields to the Detail shelf and set the table calculation on both to compute by State.
Add a reference line again to the Sales axis, and this time select Band that goes from the Sales – 25th Percentile to the Sales – 75th Percentile and fill based on the colour stated in the requirements.
Now add a reference line to the Profit Ratio axis. This time select Distribution.
Select the computation to be Percentiles and enter 25 as the value. Set Label, Tooltip and Line to None. Now this is the odd bit… set the Fill to the colour white, then check the Fill Below box, which then seems to set the Fill option to ‘No Fill’. Doing this seems to have the desired effect. Trying this in any other order, or manually selecting the Fill option to be No Fill, just doesn’t seem to work…
Now repeat the process adding another reference line on the Profit Ratio axis, and this time create a distribution for the 75th percentile. This time, after setting the Fill to white, check the Fill Above checkbox. Weirdly this time, the fill on my laptop got set the Grey Light, although it was correctly displaying as white on the screen. I manually changed it to ‘No Fill’.
It feels like there’s something a bit ‘buggy’ with all this, which might explain while my initial attempts were failing. I’ll be interested in knowing if you see this behaviour too (I created on v 2021.4.2).
Making the y-axis symmetrical
This was a ‘bonus’ feature, but is again achieved with a reference line. What we’re looking for here is to understand what is the maximum absolute value so we can determine where to place a hidden reference line – ie if the maximum profit ratio is 20% and the minimum is -30%, the maximum absolute value is 30%, and we’d need a reference line +30%, so the axis extends from -30% to +30%. Conversely if the maximum profit ratio is 40% and the minimum is -25%, the maximum absolute value is 40% and we need a reference line at -40% for symmetry.
I’ve encapsulated all this logic within one field below
Ref Line – Profit Ratio Symmetry
IF MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) = WINDOW_MAX([Profit Ratio]) THEN -1*WINDOW_MAX([Profit Ratio]) ELSE -1*WINDOW_MIN([Profit Ratio]) END
The statement MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) is returning the maximum of the max and min values (ie MAX(20, ABS(-30)) = MAX(20,30) = 30, MAX(40, ABS(-25)) = MAX(40,25) = 40.
Add this onto the Detail shelf and add another reference line to the Profit Ratio axis, setting it to use the Minimum of the Ref Line – Profit Ratio Symmetry field
Test how the field works, by selecting a few of the State marks towards the top and exclude; the axis should change, but still be symmetrical.
Highlighting the selected state
For this we need a parameter
pSelectedState
string parameter defaulted to Virginia
Then we need a field to identify which state has been captured
Selected State
[State]=[pSelectedState]
which returns a boolean. Change the mark type to Circle and add Selected State to the Colour shelf, and adjust accordingly (add a border to the circle too via the Colour shelf). Also add Selected State to the Size shelf and again tweak.
Once the sheet has been added to the dashboard, create a dashboard parameter action that passes the State field into the pSelectedState parameter on Select of the chart.
The final step to add is to stop all the other marks from ‘fading’ out when a State mark is selected. This is achieved by creating the following calculated fields
True
True
False
False
Add both of these to the Detail shelf of the scatter chart.
On the dashboard, create a dashboard filter action as below, which passes selected fields setting True = False, which can never be true and prevents the mark from being highlighted.
And with that, you should have the desired dashboard. I’m interested to see if this matches Erica’s solution (it’s likely I’ll start with the provided workbook next week, rather than mine, just in case there are some discrepancies – eg I’ve used a lot of table calcs… LoDs may have been possible…)
Continuing on from last week’s challenge, Candra Mcrae set this #WOW2021 challenge to introduce another new feature of v2020.4 (so you’re going to need this version as a minimum to complete this challenge). Predictive modelling isn’t something I use at all, so I was quite reliant on the help documentation Candra referenced. I’m also not overly familiar with the various different models, and when they should be used, so this isn’t something I’m likely to use personally, without careful consideration and thought – it’s worth reiterating Candra’s warning :
Viz Responsibly: While predictive analysis in Tableau has never been easier, we encourage you to be intentional in how you use this powerful feature because not doing so (e.g., selecting a model type not appropriate for your data) could lead to inaccurate results.
The focus of this blog will be
Building the main chart
Creating the tooltips
Determining the data for the title
Building the measure selector
Adding the measure selector interactivity
Building the main chart
The data provided has some records related to years before 1993, but the requirement is just to use data from 1993 onwards, so the first thing I did was to set a data source filter (right click on data source -> Edit Data Source Filters) to restrict the whole data source to Year 1 >=1993
Next I created the measures we need to display
Total Enrollment
[Total enrollment 2 All students]
simply references the existing measure.
% Black Students
SUM([Total enrollment 2 Black students]) / SUM([Total Enrollment])
% Non-Black Students
1- [% Black Students]
These 3 measures need to be displayed on a single chart where the actual measure displayed is determined by user selection. This selection will be driven by the use of a parameter, that will be set by a parameter action. For now, we just need to establish the parameter.
pSelect_Measure
This is simply a string parameter which will store the value of Total Enrollment by default.
Using this parameter, we can now decide which value we’re going to plot on the chart
Actual Value
CASE [pSelect_Measure] WHEN ‘Total Enrollment’ THEN SUM([Total Enrollment]) WHEN ‘% Black Students’ THEN [% Black Students] ELSE [% Non-Black Students] END
Add this to a view by placing
Year 1 as continuous (green) pill on Columns
Actual Value on Rows
and you get the basic shape, although it’s not as ‘peaky’. This is resolved by editing the Actual Value axis (right click axis -> edit) and unchecking the Include zero checkbox.
Now change the text in the pSelect_Measure input parameter that’s displayed to % Black Students, and the chart will change. Verify it changes with the text % Non-Black Students too.
In reading through the Tableau KB documentation about Predictive Modeling Functions in Time Series Visualizations, I came to learn of the feature to Extend Date Range, something I’ve never come across before, and I’m not sure what version it first appeared in. Anyway, for this, you need to be working with a field which is a date datatype. The Year 1 field provided is an int.
I’m not entirely sure what I’ve done next is the optimum method, but it worked for me… some of it involved a bit trial and error when I came to defining and invoking the modelling feature later on. In writing this up, I’m essentially helping you to avoid the ‘back and forth’ steps I took in getting it all to work.
Anyway, I needed a date field to represent the year
Year
MAKEDATE([Year 1],1,1)
This resolves to a field containing 1st Jan YYYY for each Year in the data set.
Replace Year 1 on the chart with this field, and changing it to the ‘continuous’ instance of the date by choosing the second ‘Year’ option from the context menu
This changes the pill to green and then the option to Extend Date Range is visible on the menu. Set this to 5 years using the custom option
After doing this you’ll get a 5 nulls indicator displayed, which is basically saying there’s some years without any data. This what we expect for now.
Now onto the modelling part. We need a new calculated field to contain the predicted value using the Gaussian process regression.
again, this took a bit of trial and error to get the date field in the required format.
Add this to the Rows and again edit the axis to ‘exclude zero’. You should now see the data extending for the predicted value beyond 2018. You can now hide the 5 nulls indicator (right click -> hide indicator)
You can now combine to be a dual axis chart (don’t forget to synchronise axis), and apply the relevant formatting to change the marks, apply relevant colours, hide axis & gridlines etc. Note, I set the area chart to 25% opacity, rather than the 75% stated in the requirement, as this gave me the colour most similar to the solution.
Creating the tooltips
Hovering over the chart, the tooltips display the Actual, Predicted and Residual (Actual-Predicted) value for each point. But depending on the measure selected, the format differs – Total Enrollment is in K and the others are in %.
We can’t use the number formatting feature of a field to resolve this, so we need to be a bit more creative. I confess I started creating individual fields for each measure (Actual, Predicted, Residual) based on the measure type selected (Total Enrollment, % Black Students, % Non-Black Students), but this meant I was creating loads of calculated fields, which just seemed a bit unnecessary.
So after a bit of a think, I realised there was a better way.
First up, let’s get our residual
Prediction Residual
[Actual Value]-[Predicted Value]
Now, if we put the values in a tabular form, you can see what the precision of the values are depending on the measure stated
We need to format appropriately. Both displays required the values to be formatted to 1 decimal place, and both have a suffix, either a K or a %.
To get the value in the required display format
Tooltip – Actual Value
IF [pSelect_Measure]=’Total Enrollment’ THEN [Actual Value]/1000 ELSE [Actual Value] * 100 END
Format this to 1 dp.
Create a similar field for the predicted value, which should also be formatted to 1 dp.
Tooltip – PredictedValue
IF [pSelect_Measure] = ‘Total Enrollment’ THEN [Predicted Value]/1000 ELSE [Predicted Value] * 100 END
And finally Tooltip – Residual
[Tooltip – Actual Value] – [Tooltip – Predicted Value]
This needs to be custom formatted to +#,##0.0;-#,##0.0 which ensures a + symbol is always displayed for positive values.
Pop these onto the tabular display we built earlier, and you can see the values are now displaying in the way we need
Finally we need to create a field to store the required suffix
Tooltip – Value Suffix
IF [pSelect_Measure] = ‘Total Enrollment’ THEN ‘K’ ELSE ‘%’ END
We can now add these 4 fields onto the Tooltip shelf of the ‘All’ marks card, and create the tooltip as required
Determining the data for the title
As we need to only use 2 sheets to build this solution, and 1 sheet will be required for the measure selection, we have to incorporate the summary data displayed at the top of the dashboard as part of the title of the chart viz.
In the title, we need to display the following :
The latest year in the provided data set (ie 2018)
The latest year including the extended date range (ie 2023 – 5 years later)
The actual value from 2018 based on the selected measure
The predicted value from 2023 based on the selected measure
An indicator to show whether the values were likely to increase or decrease
The requirement was to ensure there was no ‘hardcoding’. And as we’re working on getting information related to a specific row (ie year) in a set of data that consists of multiple rows (years), then we’re going to need to make use of table calculations for this.
Let’s start with the ‘easier’ bits first. We want the Year of the latest in the actual data set, and we want this value to be essentially stored against every row in the data
Latest Year
{FIXED: MAX([Year])}
This returns the value of 2018.
Latest Year + 5
DATE(DATEADD(‘year’,5,[Latest Year]))
This simply adds 5 years to the Latest Year, so returns 2023.
Now when I’m using table calculations, I often prefer to see what the data is doing in the table itself, so I can be sure I’m doing things correctly. With the ‘extended year’ stuff, it’s a bit fiddly creating the table from scratch, so I simply started by duplicating the chart sheet ‘as crosstab’ (right click on the sheet name tab, -> Duplicate as Crosstab). Rearrange the fields so Measure Names is on Columns and Year is on Rows and the ‘Tooltip’ named fields are visible. Add Latest Year and Latest Year+5 to Rows, and you can see how these fields show the same value against every row.
Now, remove these fields, as by adding them, we’ve lost the additional ‘extended dates’ rows (ie the ‘fake’ rows that don’t actually exist in the data). Ok, so now we want to get the Actual Value associated to 2018, but then perpetuate this across every row in the data.
Latest Year – Actual
WINDOW_MAX(IF MIN([Year]) = MIN([Latest Year]) THEN [Tooltip – Actual Value] END)
If the Year is the same as Latest Year, then display the value from the Tooltip – Actual Value field. The WINDOW_MAX table calc, then spreads this same value across all rows displayed. Format to 1dp and add this to the table.
We need to do something similar to get the Predicted Value for 2023
Latest Year +5 – Predicted
WINDOW_MAX(IF LAST()=0 THEN [Tooltip – Predicted Value] END)
If we’re at the last row in the data, then display the value from the Tooltip – Predicted Value field. Again the WINDOW_MAX spreads the value across all the rows. Set this to 1 dp and add to the table.
And now we just need to get the increase/decrease indicator
Increase | Decrease
IF ([Latest Year – Actual])-[Latest Year +5 – Predicted]>0 THEN ‘decrease’ ELSE ‘increase’ END
So now we know we’ve got the correct values we need, we can add these fields to the Detail shelf of the chart sheet, so we can reference them in the Title of the chart.
We also need the Latest Year and Latest Year +5 fields added to the Detail shelf, but when you add these, you’ll notice that you lose the ‘extended years’. You can fix this by wrapping the fields in an ATTR function. Double click on the field, which will allow you to ‘type in’ to the field.
You should now be able to create the text in the chart title
Building the measure selector
Phew! Are you still with me… there’s a fair bit going on already, and now we’ve got to build the chart that will drive the user selection.
On a separate sheet, add
Measure Names to Rows
Measure Values to Detail
Measure Names to Text
Measure Names to Filter, and restricted to the 3 original measures – Total Enrollment, % Black Students, %Non-Black Students
Uncheck Show Header on the pill on the Rows, then format
Set background colour of the pane to a navy blue
Set row & column borders to be white
Set the text label to be white text, centred, and increase the font
Turn off the tooltip
Adding the measure selector interactivity
Create the dashboard and add both the charts. To add the interactivity so that on click of a row in the Measure Selection sheet, it changes the measure being displayed, we need to add a dashboard action, that changes a parameter (Dashboard menu -> Actions -> Add Action -> Change Parameter). Set the action to run on Select when the Measure Select sheet is clicked. The target parameter is pSelect_Measure and the Measure Names field should be passed into this.
And with all that, you should hopefully now have a working solution. My published viz is here (note, my Measure Selection sheet is slightly different from what I’ve described above). The above is a bit simpler I think.
Donna + DataViz 