It was Lorna’s turn to set the challenge this week – to compare YTD with Previous YTD while also filtering date ranges which updated based on the year of focus. She hinted it was a great use case for an INCLUDE LoD and I have to admit that seemed to fry my brain! I spent a couple of hours trying various concepts and then put it to bed as I wasn’t progressing.
When Rosario Gauna published her solution, I had a quick look and realised that there was no need for an LOD after all, and I shouldn’t have got hung up on the hint. So thanks Rosario!
Setting up the data
The data set provided contains 4 complete years worth of data from 1st Jan 2021 to 31st Dec 2024. In order to simulate ‘YTD’ for 2024 and ensure my workbook would always show the concept of a partial year, I created a parameter
pToday
date parameter defaulted to 3 July 2024
and then I created
Records to Keep
[Order Date]<= [pToday]
In a typical business scenario, the pToday parameter would simply be replaced by the TODAY() function.
I then added this as a data source filter (right click data source > add data source filter) and set to True to restrict the data from 01 Jan 2021 through to 03 July 2024.
Setting up the calculations
Before building the Viz, we’re going to work through what calculations are needed to drive the behaviour we require. We’ll do this in a tabular view. First up we will need a couple of parameters
pYear
integer parameter defaulted to 2024 containing a list of values from 2021 to 2024. The display value should be formatted to a whole number without the , separators.
pDatePart
string parameter defaulted to Month containing a list of 2 values Month and Week.
Show these parameters on a sheet.
On the sheet, add Order Date as a discrete exact date (blue pill) to Rows.
The first thing we will do is identify the set of Order Dates that we need to consider based on the pYear parameter. That is if pYear is 2024, we want to consider the Order Dates in 2024 and the Order Dates in 2023. But if the pYear is 2023, we want to consider all Order Dates in 2023 and 2022. When we do identify these dates, we will ‘baseline’ them to all align to the focus year (ie the pYear value).
Add this as a discrete exact date (blue pill) to Rows.
If you scroll through, you should see how the Date Baseline field is behaving as you change the pYear value.
Now we only want to include rows with dates, and in the case of 2024 v 2023, we only want dates up to ‘today’.
Filter Dates
IF [pYear] = YEAR([pToday]) THEN [Date Baseline] <= [pToday] ELSE [Date Baseline] <= MAKEDATE([pYear], 12, 31) END
If the pYear parameter matches the year of ‘Today’, then only include dates up to ‘Today’, otherwise include dates up to 31 Dec of the selected year.
Add this to the Filter shelf and set to True.
When pYear is 2024, you can see that we have Order Dates from 01 Jan 2023 to 03 Jul 2023 and then 01 Jan 2024 to 03 Jul 2024. But changing pYear to 2023, you get all dates through from 01 Jan 2022 to 31 Dec 2023.
Add Date Baseline to the Filter shelf, and select Range of Dates, then choose the Special tab and verify All dates is selected and select Apply to commit this option.
Show the Date Baseline filter to display the range control filter.
Changing the pYear parameter will change the start & end dates in the Date Baseline filter to match the year selected. But with 2024 selected, the range ends at 31 Dec 2024. We don’t want this – we only have data up to 3rd July 2024. To resolve this, set the property of the filter control to Only Relevant Values
Adjust the date ranges and the records in the table should adjust too. If you change the pYear parameter after you’ve adjusted the date range, you’ll need to reset/clear the date range filter.
The next thing we need to handle is the switch between months and weeks. For this create
Date to Display
DATE(CASE [pDatePart] WHEN ‘month’ THEN DATETRUNC(‘month’,[Date Baseline]) ELSE DATETRUNC(‘week’, [Date Baseline]) END)
Add this as a discrete exact date to Rows and you can see how this field works when the pDatePart field is altered.
So now we have the core filtering functionality working, we need to get the measures we need
YTD Sales
IF YEAR([Order Date]) = [pYear] THEN [Sales] END
PYTD Sales
IF YEAR([Order Date]) = [pYear]-1 THEN [Sales] END
On a new sheet, double click into Columns and type MIN(0), then repeat, so there are 2 instances on MIN(0) on Columns and 2 marks cards.
Change the mark type on the All marks card to shape, and select a transparent shape (see here for details). Set the sheet to Entire View.
On the 1st MIN(0) marks card, add YTD Sales to Label. Then adjust the label font and text and align middle centre.
On the 2nd MIN(0) add PYTD Sales and % Diff to Label and adjust the layout and formatting as required. Align middle centre.
On the tabular worksheet we were using to test things out, set both the filters to ‘apply to worksheets > all using this data source’, so persist the changes made on one sheet to all others.
On the All marks card, add Date Baseline to Tooltip and adjust to use the MIN aggregation. Then add another instance of Date Baseline to Tooltip and adjust to use the MAX aggregation. Update the Tooltip accordingly.
Remove all row/column dividers, gridlines, zero lines. Hide the axis. Name the sheet KPI or similar.
Building the line chart
ON a new sheet add Date to Display as a continuous exact date (green pill) to Columns and YTD Sales to Rows. Show the pYear and pDatePart parameters. The Date Baseline and Filter Date fields should have automatically been added to the Filter shelf. Show the Date Baseline filter.
Drag the PTYD Sales field from the data pane onto the YTD Sales axis and drop it when the cursor changes to a double green column icon.
This will automatically adjust the pills to include Measure Names and Measure Values onto the view. Adjust the colours of the lines and ensure the YTD Sales line is displayed on top of the PYTD Sales line (just reorder the values in the colour legend if need be).
Add YTD Sales and PYTD Sales to Tooltip and adjust accordingly. Edit the Value axis and adjust the title of the axis to Sales. Remove the title from the Date to Display axis.
Building the dashboard
Use a combination of layout containers to add the objects onto a dashboard. I started with a horizontal container, with a vertical container in each side.
For the next few Workout Wednesdays, the coaches will be revisiting old challenges – retro month! Erica kicked the month off with this challenge, to recreate Ann Jackson’s challenge from 2018 Week 38. I completed this challenge at the time (see here), but the additions and changes to the visual display Erica incorporated meant I couldn’t just republish it 🙂
So I built it from scratch using the data source from the google drive Erica referenced in the requirements (which I believe may be why my summary KPIs didn’t actually match Erica’s).
There’s a heck of a lot going on in this challenge – it certainly took some time to complete, which may mean this blog becomes quite lengthy… I will endeavour to be as succinct as I can, which may mean I don’t explicitly state every step, or show lots of screen shots.
Setting up the parameters
I used parameters and subsequently dashboard parameter actions to build my solution. Erica mentions set actions, but I chose not use any sets.
As a result there’s lots of parameters that need creating
pAggregate
This is a string parameter that contains the list of possible dimensions to define the lowest level of detail to show on the scatter plot (ie what each dot represents). Default to Sub-Category. Note how the Display As field differs from the Value field.
pColour Dimension
This is a string parameter that will contain the value of the dimension used to split the display into rows (where each row is coloured). This will get set by a parameter action from interactivity on the dashboard, so no list of options is displayed. Default to Segment.
pSplit-Colour
boolean parameter to control whether the chart should be split with a row per ‘colour’ dimension, or just have a single row. The values are aliased to Yes/No
pSplit-Year
another boolean parameter to control whether the chart should be split with a column per year or just have a single column. The values are aliased to Yes/No (essentially similar to above)
pX-Axis
string parameter that contains the value of the measure to display on the x-axis. This will be set by a dashboard parameter action, so no list is required. Default to Sales.
pY-Axis
Similar to above, a string parameter that contains the value of the measure to display on the y-axis. This will be set by a dashboard parameter action, so no list is required. Default to Profit.
pSelectedDimensionValue
string parameter that contains the dimension value associated to the mark that a user clicks on when interacting with the scatter plot, and then causes other marks to be highlighted, or a line to be drawn to connect the marks. This will be set by a dashboard parameter action, so no list is required. Default to <nothing>/empty string.
Building the basic Scatter Plot
The scatter plot will display information based on the measures defined in the pX-Axis and pY-Axis parameters. We need to translate exactly what the measures will be based on the text strings
X-Axis
CASE [pX-Axis] WHEN ‘Profit’ THEN SUM([Profit]) WHEN ‘Sales’ THEN SUM([Sales]) WHEN ‘Quantity’ THEN SUM([Quantity]) END
Y-Axis
CASE [pY-Axis] WHEN ‘Profit’ THEN SUM([Profit]) WHEN ‘Sales’ THEN SUM([Sales]) WHEN ‘Quantity’ THEN SUM([Quantity]) END
We also need to define which field will control the lowest level of detail based on the pAggregate dimension
Dimension Detail
CASE [pAggregate] WHEN ‘Category’ THEN [Category] WHEN ‘Sub-Category’ THEN [Sub-Category] WHEN ‘Product’ THEN [Product Name] WHEN ‘Region’ THEN [Region] WHEN ‘State’ THEN [State] WHEN ‘City’ THEN [City] END
Similarly we need to know which field to split our rows by (the colour)
Dimension Row
CASE [pColour Dimension] WHEN ‘Segment’ THEN [Segment] WHEN ‘Category’ THEN [Category] WHEN ‘Region’ THEN [Region] WHEN ‘Ship Mode’ THEN [Ship Mode] END
but as we need different behaviour depending on whether the pSplit-Colour field is yes or no, we need
Row Display
IF [pSplit-Colour] THEN [Dimension Row] ELSEIF [pColour Dimension] = ‘Category’ THEN ‘All Categories’ ELSE ‘All ‘ + [pColour Dimension] + ‘s’ END
If the parameter is true, then just show the value from the Dimension Row, otherwise display as ‘All Categories’ or ‘All Segments’ or ‘All Regions’ etc.
Similarly, as the columns can be split by years or not, we need
Years
IF [pSplit-Year] THEN STR(YEAR([Order Date])) ELSE ” END
Add the fields to a sheet with
Years & X-Axis on Columns
Row Display & Y-Axis on Rows
Dimension Detail on Detail
Dimension Row on Colour
Set the mark type to circle and reduce colour opacity
Edit the axes, so the titles are sourced from the pX-Axis and pY-Axis parameters
Show all the parameters and manually edit the values/change the selections to test the functionality.
Highlighting corresponding marks
Show the pSelectedDimension parameter and hover over a mark in the scatter plot to read the value of the Dimension Detail field. Enter than value into the pSelectedDimension parameter (eg based on what is displayed above, each mark is a Sub-Category, so I’ll set the field to ‘Phones’).
We need to determine whether the value in the parameter matches the dimension in the detail
Highlight Mark
[pSelectedDimensionValue] = [Dimension Detail]
This returns True or False. Add this field to the Detail shelf, then add it as second field on the Colour shelf.
Adjust the default sorting of the Highlight Mark field, so the True is listed before False (sorted descending) – right click on the field > Default Properties > Sort. And ensure the colour fields on the shelf are listed so Dimension Row is above Highlight Mark. If all this is done, then the colour legend show look similar to below, where the Dimension Row is listed before the True/False, and the Trues are listed above the Falses, so the True is a darker shade of the colour.
Add Highlight Mark to the Size shelf and then edit the size legend to be reversed and adjust the sizes so the smaller ones aren’t too small, but you can differentiate (you may need to adjust the overall size slider on the size shelf too).
Making a connected dot plot
Add Order Date at the Year level (blue discrete pill) to the Detail shelf of the scatter plot.
To make the lines join up when the viz isn’t split by year, we need a field
Y-Axis Line
IF NOT [pSplit-Year] AND [pSelectedDimensionValue] = MIN([Dimension Detail]) THEN [Y-Axis] END
This will only return a value to plot on the Y-Axis if pSplit-Year = No and a user has clicked on a mark.
Set the pSplit-Year parameter to No, then add Y-Axis Line to Rows. On the Y-Axis Line marks card, remove Highlight Mark from colour and size and also remove Dimension Row from Colour. Add Order Date at the Year level (blue discrete pill) to Detail. Change the mark type to Line then add Year(Order Date) to Path instead of Detail.
Make the chart dual axis and synchronise the axis.
Play around changing the pSplit-Year parameter and the value in the pSelectedDimension parameter to test the functionality.
Tidy the scatter plot by adjusting font sizes, removing the right hand axis & the gridlines, lightening the row & column dividers, removing row & column label headings. Tidy up tooltips. Add a title that references the parameter values.
Building the Total Marks KPI
Create a new field
Count Marks
SIZE()
and a field
Index
INDEX()
Set this field to be a discrete dimension (right click > convert to discrete)
On a new sheet, add Dimension Row, Dimension Detail and Order Date (set to Year level as blue discrete pill) to the Detail shelf. Add Count Marks to Text. Adjust the table calculation setting of Count Marks so that all the fields are selected.
Add Index to the Filter shelf and select 1. Then adjust the table calculation setting of this field so it is also computing by all fields. Re-edit the filter, and adjust so only 1 is selected. This should leave you with 1 mark. Change the mark type to shape and set to use a transparent shape.
Adjust font size & style, set background colour of worksheet to grey, adjust title, hide tooltips.
Building the X-Axis KPI
For this we need
Total X-Axis
TOTAL([X-Axis ])
Min X-Axis
WINDOW_MIN([X-Axis ])
Max X-Axis
WINDOW_MAX([X-Axis ])
On a new sheet add Dimension Row, Dimension Detail, YEAR(Order Date) to Detail. Add pX-Axis, Total X-Axis, Min X-Axis & Max X-Axis to Text. Adjust all the table calcs of the Total, Min, Max fields to compute using all dimensions listed. Add Index to filter and again set to 1, then adjust the table calc and re-edit so it is just filtered to be 1. Set the mark type to shape and use a transparent shape. Adjust the layout & font of the text on the label. Set background colour of worksheet to grey, adjust title, hide tooltips.
Building the Y-Axis KPI
Repeat the steps above, creating equivalent Total, Min & Max fields referencing the Y-Axis.
Creating the Y-Axis ‘buttons’
We’ll start with creating a Profit button
Create a field
Label: Profit
“Profit”
and
Y-Axis is Profit
[pY-Axis] = ‘Profit’
We will also need the field below for later on
Y-Axis not Profit
[pY-Axis] <> ‘Profit’
On a new sheet double click on Columns and manually type in MIN(1). Add Label: Profit to Text and Y-Axis is Profit to Colour. Change the mark type to bar.
Set the Size of the bar to maximum, adjust the axis to be fixed from 0-1 and hide the axis. Remove all column/row banding, axis line, gridlines etc.
Show the pY-Axis parameter. If the colour legend is set to True (as pY-Axis contains Profit), then adjust the colour to a dark grey. Then change the value of the pY-Axis parameter, which should then display False in the colour legend. Adjust this to light grey. You may need to do this the other way round. Hide tooltips.
Repeat the same process to create separate sheets for Sales and Quantity with equivalent calculated fields (I found the easiest way was to duplicate the sheet and then swap out the fields).
Creating the X-Axis ‘buttons’
Again, just duplicate the above steps but reference the pX-Axis parameter instead.
You should end up with 6 sheets (1 per measure – Sales, Profit, Quantity – per axis), and 18 calculated fields (3 per measure & axis) as a result.
Creating the ‘Select Colour’ buttons
For the Category button, create
Label: Category
‘Category’
and
Colour is Category
[pColour Dimension] = ‘Category’
Build a ‘button’ as a bar chart, using the same principals as above. You will need to show the pColour Dimension parameter to test changing the value to set the different colours.
Repeat the same steps to build 3 further sheets for Region, Segment and Ship Mode.
Building the dashboard
You will need to use layout containers to arrange all the objects in. The main body of the dashboard consists of a horizontal layout container, which contains 3 objects: a vertical container (left column with configuration options), the scatter plot in the middle and then another vertical container (right column with KPIs).
The left hand ‘Configure Your Chart’ vertical container consists of text objects, parameters, the X-Axis ‘button’ sheets, the Y-Axis ‘button’ sheets and the Colour ‘button’ sheets.
For each of the X-Axis and Y-Axis button sheets, a parameter action needs to be created like below
Set Y-Axis to Profit
On select of the Y-Axis Profit sheet (or whatever you have named the sheet), set the pY-Axis parameter with the value from the Label:Profit field.
You should end up with 6 different parameter actions for these fields – 1 per measure per axis .
For each of the ‘Colour’ buttons, a similar parameter action is also required
Set Colour to Category
On select of the Colour-Category sheet (or whatever you have named the sheet), set the pColour Dimension parameter with the value from the Label:Category field.
You should end up with 4 parameter actions like this.
The Y-Axis and X-Axis buttons should only display 2 options at a time, so you can’t select the same measure for both axis.
Assuming Profit is currently selected on the Y-Axis, then select the object that displays the Profit X-Axis ‘button’ and from the Layout tab, set to control visibility using value and select the Y-Axis not Profit calculated field. As the Y-Axis is set to Profit, the Profit X-Axis button will disappear.
Repeat these steps for each of the X & Y Axis button sheets. Note, sometimes it’s easier to select the object via the Item Hierarchy section of the Layout tab.
For the scatter plot, the user needs the ability to select a mark and the others be highlighted/connected depending on the current settings. This needs another parameter action
Select Dimension Value
On select of the scatter plot, set the pSelectedDimensionValue parameter with the value from the Dimension Detail field.
For the right hand KPI nav, the vertical container consists of text objects, and the KPI sheets.
To make horizontal line separators, set the padding on a blank object to 0, the background colour to the colour you want, and then edit the height of the blank object to 2pt.
For the Info ‘button’, add a floating text box containing the relevant text and position it where you want it to display. Give the object a border and set the background to white. Then select the object and choose the Add Show/HideButton from the context menu. Edit the button to display an info icon when hidden. Ensure the button is also floating and position where you want.
I used additional floating text boxes to display some of the other text information on the dashboard.
No doubt you’ll need to spend some time adjusting padding and layout to get everything where you want, but this should get you the core functionality.
Kyle returned this week to set this challenge to create a bullet chart which compared Win % for baseball teams in a year (bar) to the previous year (line), along with call out indicators (red circles) if the difference was greater than a user defined threshold. To add an extra ‘dimension’ to the challenge, a viz in tooltip showing the historical profile for the selected team was also required.
Building the basic bar chart
We’re going to use a parameter to define which year we want to analyse, as we can’t just filter by the Year as we need information about multiple years
pYear
integer field defaulted to 2018, formatted so the thousands separator does not display, and populated by Add values from the existing Year field
With this, we can create the following fields
Wins – CY
IF Year = [pYear] THEN [Wins] END
Wins – PY
IF Year = [pYear]-1 THEN [Wins] END
Games – CY
IF Year = [pYear] THEN [Games] END
Games – PY
IF Year = [pYear]-1 THEN [Games] END
and subsequently
Wins per Game – CY
SUM([Wins – CY]) / SUM([Games – CY])
and
Wins per Game – PY
SUM([Wins – PY]) / SUM([Games – PY])
Both these 2 fields are formatted to a custom format of ,##.000;-#,##.000 (this basically strips off the leading 0, so rather than 0.595 it’ll display as .595.
Now we have all these, we can build a dual axis chart.
Add League and Team to Rows and Wins per Game – CY to Columns. Sort by Wins per Game – CY descending. Display the pYear parameter.
Add Wins per Game – PY to Columns, make dual axis and synchronise the axis. Change the mark type of the CY card to a bar and the mark type of the PY card to Gantt. Remove Measure Names from the Colour shelf of both cards, and change the colour of the gantt bar to black.
Show mark labels for the bar mark and align left (expand the width of each row if need be).
Colouring the bars
The colour of the bars is based on whether the CY value is greater or less than the PY value. So we need to find the difference
CY-PY DIfference
[Wins per Game – CY]-[Wins per Game – PY ]
and then work out if the difference is positive or not
CY-PY Difference is +ve
[CY-PY Difference]>0
Add this to the Colour shelf of the bar marks card and adjust accordingly.
Adding the call out indicator
The red circle is based on whether the difference is above a threshold set by the user . A parameter is required for this
pChange
float field set to 0.1 by default; the values should range from 0.05 to 0.2 in 0.05 intervals
Show this parameter on the sheet.
We then can create
CY-PY Diff Greater than Change
IF ABS([CY-PY Difference])>[pChange] THEN ‘●’ ELSE ” END
The ABS function is used, as we want to show the indicator regardless as to whether the difference is a +ve or -ve difference. The ● image I get from copying from https://jrgraphix.net/r/Unicode/25A0-25FF. I use this page a lot, so keep it bookmarked.
Add this field to Rows, and then format the field so it is red and the font size is bigger (I used 12pt)
Now the viz just needs to be tidied up by
Hide field labels for rows
Rotate label of the League field
Format the font of the Team text
Reduce the width of the first three columns
Remove gridlines and zero lines
Adjust the row divider to be a dotted line at the 2nd level
Remove column dividers
Add an axis ruler to Rows
Uncheck Show Header on the axis to hide them
Tidy up the tooltip on the gantt mark type.
Building the Viz in Tooltip line chart
For this we need the Win % for every year, so we need
Wins per Game
SUM([Wins]) / SUM ([Games])
Add Team to Rows, Year to Columns (change to be a continuous, green pill) and Wins per Game to Rows
Add pYear to the Detail shelf, then add a Reference line to the Year axis to display the value of pYear as as dotted line
Right click on the reference line > format and adjust the alignment of the value displayed to be top centre.
Show mark labels and set to just show the min & max values for each line.
Colouring the lines
The colour of the line is based on whether the current year’s value is bigger or smaller than the previous year (ie the colour of the line matches the bar).
However, we can’t just use the fields we’ve already built, as because we have Year in the view, the data only exists against the appropriate year, so the difference can’t be computed. You can see this better if you build out the table below…
We need to ‘spread’ these values across every year for each team.
Wins per Game – CY Win Max
WINDOW_MAX(SUM([Wins – CY]) / SUM([Games – CY]))
Format this as you did above.
Add this to the view and amend the table calculation so it is computing by the Year field only. You should see that the value of this field matches the Wins per Game – CY value, but the same value is listed against every year now, rather than just the one selected.
Similarly, create
Wins per Game – PY Win Max
WINDOW_MAX(SUM([Wins – PY]) / SUM([Games – PY]))
format, and add this to the view too and adjust the table calculation as you did above.
So now we have this, we can work out whether the difference between these two fields is +ve or not
CY-PY Difference is +ve Win Max
[Wins per Game – CY Win Max]- [Wins per Game – PY Win Max]>0
Add this to the Colour shelf of the line chart. Verfify the table calculation is set to compute by Year only for both nested calculations, and then adjust the colours to match.
Finally tidy up the viz, to remove all headings, axis, gridlines, row/column dividers etc Set the background colour of the sheet to a light grey, then finally set the fit to be Entire View.
Adding the Viz in Tooltip
Go back the bar chart, and on the tooltip of the bar mark, adjust the initial text, then insert the line chart sheet via the Insert > Sheets > Sheetname option.
I adjusted the maxwidth & maxheight properties of the inserted text to be 350px each
If you hover over the bar chart now, you should get a filtered view of the line chart.
Final step is to put all this on a dashboard. My published viz is here.
The challenge this week came from Candra McRae, where the focus was to use statistics to identify the top & bottom performers, rather than the more common ‘top n’ and ‘bottom n’. By statistics, we’re specifically looking records within the top 25th percentile and the bottom 25th percentile.
So let’s dive in.
Identify Current date
Defining the calculations
Building the chart
Month Selector and interaction
Identify Current date
The data we’re using is the Superstore Sales data from 2021.1 which includes data up to 31st Dec 2021. The requirement talks about the current rolling x months worth of data compared to the previous x months worth of data.
This means we need a way to determine what ‘current’ is. Typically, in a real sales environment, you’d probably only have data up to ‘today’, and I did consider working up a solution based on ‘today’, but equally I like to deliver a solution that I know matches the challenger, as it helps to validate my workings, and also I like to have a solution that I can look back on in the future and know there’s data.
So I took Candra’s hint and based ‘current’ off of the maximum date in the data set, derived by
Max Date
DATE({FIXED:MAX([Order Date])})
Defining the calculations
If you’re a regular reader of my blogs, you’ll know when I can, I like to build the data out into a tabular form, so I can verify the calculations, and then I’ll build out the viz.
First up, we want to get a value for the ‘current rolling n months’.
To define ‘n’ we need a parameter.
pRollingMonth
An integer defaulted to 12. It doesn’t need to be a list, as this will be populated via a parameter action from another sheet – more on that later.
Current Rolling Sales
IF [Order Date]>= DATEADD(‘month’, -1 * [pRollingMonth] ,DATETRUNC(‘month’,[Max Date])) AND [Order Date] <= [Max Date] THEN [Sales] END
let’s break this down… DATETRUNC(‘month’,[Max Date]) truncates the Max Date which is 31st Dec 2021 to the 1st of the month ie it returns 01 Dec 2021.
DATEADD(‘month’, -1 * [pRollingMonth] ,DATETRUNC(‘month’,[Max Date])) , is then going back to the 12 months prior (-1×12=-12) , so is 01 Dec 2020.
So we’re only going to get a sales value if the Order Date is >= 01 Dec 2020 and <= 31 Dec 2021 (essentially 13 months of sales data).
For the previous sales, we first need
Prev Month
DATEADD(‘month’, -1, [Max Date])
so in our current example, this will be 30 Nov 2021.
and then to get the previous rolling 12 month sales, we can apply similar logic using Prev Month instead of Max Date
Previous Rolling Sales
IF [Order Date]>= DATEADD(‘month’, -1 * [pRollingMonth] ,DATETRUNC(‘month’,[Prev Month])) AND [Order Date] <= [Prev Month] THEN [Sales] END
Both these fields can be formatted to 1 decimal place, $ prefix and format in thousands (k).
And then we also need a difference to display on the tooltip
Difference
SUM([Current Rolling Sales]) – SUM([Previous Rolling Sales])
This needs to be additionally formatted so that negatives are displayed in brackets ().
Add all these into a table and sort by the Current Rolling Sales descending
So we’ve got the data needed for the bar, the line and the tooltip. We now need to work on the crux of the challenge – the calculations needed to identify the top & bottom.
We’re looking to identify the 25th percentile value based on Current Rolling Sales values displayed on screen
25th Percentile
WINDOW_PERCENTILE(SUM([Current Rolling Sales]),0.25)
and also we need the 75th percentile
75th Percentile
WINDOW_PERCENTILE(SUM([Current Rolling Sales]),0.25)
If you pop these table calcs into the table, you’ll see the values for each field are the same for each row
and with these we can now identify where each row falls
Colour
IF SUM([Current Rolling Sales]) >= [75th Percentile] THEN ‘Top’ ELSEIF SUM([Current Rolling Sales]) <= [25th Percentile] THEN ‘Bottom’ ELSE ‘Middle’ END
Finally we need to identify the rows with a negative difference and flag with a circle.
Sales Contraction Indicator
IF [Difference]<0 THEN ‘●’ ELSE ” END
I use this site to get the symbols for these types of requirements,
Pop these two fields in to the table, and you’ve got all the data needed to build the chart:
Building the chart
Candra states that we can’t use a reference line to display the previous sales data, so for the core chart we need to build a dual axis chart plotting Sub-Category against Current Rolling Sales (bar chart) and Previous Rolling Sales (gantt chart).
Current Rolling Sales is coloured by Colour. I created a Label:Current Rolling Sales field just based on Current Rolling Sales but formatted to 0dp to add to the Label shelf.
To get the circles displayed, and to retain the order of the display, duplicate the Sub-Category field so you have a Sub-Category (copy) field. Add this to Rows alongside the existing Sub-Category field.
Then add the Sales Contraction Indicator field between these 2 fields, and format the font of that field so it is in red text (getting a coloured circle, was the part of this challenge I struggled most over, yet it really was very simple once the penny dropped!).
Then hide the first Sub-Category field (uncheck Show Header) so it no longer displays.
Apply various formatting to remove the row & column lines, gridlines etc, and adjust the tooltip and you should be done.
Month Selector and Interaction
A separate sheet is needed for this. We need to build a basic viz that has 12 data points with values 1-12. And we can get this from the Order Date field
Month Order Date
MONTH([Order Date])
will return the month number
Add this field as a discrete (blue) pill to the Columns shelf, set the mark type to square, and add Month Order Date to the Label shelf too. Colour the mark pale grey, and remove all borders etc, and hide the headers
When you add the 2 sheets onto the dashboard, you need to set a parameter action from the Month Selector sheet that sets the pRollingMonth parameter, using the value from the Month Order Date field. When unselecting, the value should default back to 12.
Hopefully there’s enough here to get you to the end! My published viz is here.
Luke Stanke set the challenge this week, and posted a ‘sneak peak’ on Twitter before the challenge was formally released
Challenges from Luke can sometimes be on the harder end of the scale, so with the bit of extra time available and only the gif in the tweet as a clue , I had a play to see if I could get close at all. And surprisingly I could, so once it was formally issued, it was just a case of tidying up some of my calcs.
Calculations
First up we need to establish the basic calculations
Plotting these out onto a table with Segment we get
Note #Orders Per Customers needs to be set to be a discrete dimension.
So while this shows us a summary of the number of customers, we’ve only got 1 mark showing the summarised count. When plotting the chart we need something else in the view that will generate more marks. This is Customer ID.
At this point in order to help us build up our tabular view to ‘see’ what’s going on, I’ll filter the table to just show Segment = Consumer, and I’ll add Customer ID to Rows
As expected, our # Customers is now showing a count of 1 per row, but the data is now expanded as we have now got a row (ie a mark) per customer for each # Orders Per Customer ‘bucket’. But we still need a handle on the total customers in each ‘bucket’.
Customers Per Order Count
WINDOW_SUM([# Customers])
Adding this to the view and setting the table calc to compute based on Segment & Customer ID, we get the summarised value back again.
But we don’t want to actually show that number of marks; the number of circles to plot on the chart is dependent on a user parameter:
pMarkIndicator
Based on the requirements, if the number of customers is 15 and the user parameter is 5, then 3 circles should be drawn (15 / 5 =3 ), but if the number of customers was 14, only 2 circles should be drawn (14 / 5 = 2.8), ie the number of circles will always be set to the integer of the equal or lesser value (essentially the FLOOR() function). This can also be achieved by
Marks to Plot
INT([# Customers per Order Count]/[pMarkIndicator])
For some reason FLOOR can’t be used in the above as a table calculation is being used, but INT does the job just fine, and adding to the tabular view and adjust the table calculation accordingly we get
ie for the Consumer Segment, 6 customers have made 1 order in total, so based on batching the customers into groups of 5, this means 1 circle should be displayed. Whereas, 30 customers have made 3 orders in total, so 6 circles should be displayed.
But we can’t actually reduce the amount of rows (ie marks) displayed – we either have 1 row (by removing Customer ID) or a row per customer. But that’s fine, we don’t need to.
What we want is something in our data to group each row into the relevant batch size.
First up let’s generate an ID per row for each customer that restarts for each #Orders Per Customer.
Index
INDEX()
Add this as a discrete pill to Rows, and adjust the table calc
Now we have this, we calculate which ‘column’ each customer can sit in, a bit like what we would do if we were building a small multiple table, arranging objects in rows and columns (see here for an example of what I mean).
Cols
[Index]%([Marks to Plot])
This uses the modulo (%) notation which returns the remainder of the division sum. Lets put this on the view
For customers who have only ordered once, and where we’re only going to plot 1 mark, the Cols value is the same (0) for all rows.
Whereas for customers who have ordered twice, and where we want to plot 2 marks, the Cols value is either 0 or 1.
We’ve now got a value we can use to plot on an axis. We’re still going to plot a mark for each Customer ID, but some marks will be plotted in the same position, ie on top of each other, which therefore looks like just one mark.
Let’s show this more graphically, by duplicating the sheet, deleting some pills, moving some around, changing some to continuous, and setting the mark type to circle as below
Change the pMarksIndicator parameter and the number of circles will adjust as required.
So far, so good. We’ve got the right number of marks, it’s just not looking as nice and symmetrical as it should be.
We need to shift the marks to the left. But how far it shifts is dependent on whether we’ve plotted an odd or even number of marks.
If we have an odd number, the middle mark should be plotted at 0. If we have an even number the middle two marks should be plotted at -0.5 and +0.5 respectively. The calculation below will achieve this
Cols Shifted
IF [Marks to Plot]%2 = 0 //we’re even THEN [Cols] – ([Marks to Plot]/2) + 0.5 ELSE //we’re odd [Cols] – (([Marks to Plot]-1)/2) END
To demonstrate this, I’ve added Cols Shifted along side Cols on the viz (this time make sure all the table calculation settings (including the nested calcs) are applied to compute based on Customer ID only which is different from the calcs above)..
Now you can see how it all works, you can remove the Cols and the Segment from the Filter shelf.
And now its just a case of applying the various formatting to clean up the display, and adding to the dashboard.
Donna + DataViz 