#WorkoutWednesday Week 41 saw Luke challenge us with this task, based on a real scenario he’d encountered during his job.
Before I’d even saw Luke’s tweet advertising the challenge, I knew this was going to require Set Actions
To avoid any discrepancies interpreting the requirements, Luke very kindly defined all the calculations, so I’m not going to dwell on these. This blog will focus on the requirements I think will be most useful / those I had to think about, so I’m making assumptions you can build the calcs and the basic vizzes required. My published workbook (referenced at the end of the blog) can be downloaded if you’re not too sure.
Only Show Customers with Sales over $500
I created a fixed level of detail (LoD) calculation to store the total sales per customer
Total Sales per Customer
{FIXED [Customer Name] : SUM([Sales])}
which I then referenced in another calculation
Sales > 500
[Total Customer Sales]>500
which I added to the Filter shelf of all the sheets I built, setting the value to True.
Change the circle colour on the scatter plot for selected customers
I created a Set based on the Customer Name
I called the Set Selected Customer and just ticked a random set of customers in the list presented (I’ll describe how the set members, ie the customers, change later).
Dragging the Selected Customer set onto the Colour shelf of my scatter plot, gives me an In/Out colour option, where the ‘In’ represents the customers that are members of the set (those I randomly selected above), and ‘Out’ is those customers that aren’t members.
Move the selected customers to the top of the table
In the table viz, add the Selected Customer set onto the Rows shelf, in front of the Customer Name. This will add a level of ‘grouping’ to the table, with those ‘In’ the set being listed above those ‘Out’ of the set.
Then untick Show Header against this pill, to hide the In/Out from the display
Show a totals for each cohort
This is achieved by adding subtotals to the table.
Go to Analysis > Totals > Add all Subtotals
This will add a Total row which can then be formatted via a right-click, where the Label can be renamed to ‘Cohort’ and bold text applied.
Change the colour of the ‘unselected’ customers to dark grey in the table
I have to admit, I did scratch my head on this one for a few moments…but then the light bulb switched on 🙂
This is simply an adjustment of the row banding formatting option, ensuring the band size & level are set appropriately
Sort Customers by Lost Sales
In the table, this is just done by applying a Sort to the Customer Name field, using the Lost Sales calculated field that you should already have created.
Change the members of the Selected Customer Set…
…which in turn will change which customers are displayed at the top of the table.
This is where Set Actions come into play.
Add both the scatter viz and the table viz to a dashboard.
Then go to Dashboard -> Actions ->Add Action > Change Set Values
Select the Scatter viz to be the source sheet, and the Target Set to be the Selected Customer set. Choose to run action on Select and opt to remove all values from the set when the selection is cleared.
Now test the set action, by selecting circles on the scatter plot – you should see your selections changing colour and the customers selected being listed at the top of the table…. we’re nearly there… one final tricky requirement…
Make sure all members remain un-highlighted on the scatterplot
What you’ll notice when testing the set action above, is that on selection, while your selected customer circles change colour, the unselected customer circles’fade out’
The challenge is for the unselected circles to remain the same colour as in the screenshot to the left above.
We need to use a highlight action to do this, but trick it in such a way that it doesn’t actually highlight anything…
I did this by creating a new calculated field called Dummy which I just set to be an empty string (“”).
Add this to the Detail shelf on the scatter viz.
On the dashboard, add a Highlight dashboard action
Set the source and target sheets to be the scatter viz and change the Target Highlighting section to be Selected Fields, choosing the Dummy field.
And now, on selection, the other customer circles won’t fade away! Hurrah!
After last week’s tricky calcs that meant my solution didn’t match up, it was a welcome relief to get this challenge from Curtis Harris. Whilst some ‘seasoned’ #WorkoutWednesdayers may find this challenge very straightfoward, the main intention was to ensure everyone had been given an opportunity to implement the technique of sheet swapping.
I put myself into the seasoned category and have completed sheet swapping challenges in the past. In the past I would have been googling ‘tableau sheet swapping’ and referring to the raft of blogs & videos that provide you with the technique (maybe this one will become one of those in future!).
The challenge for me this time, was whether I could remember what to do without having to access previous workbooks or online references.
The answer was ‘yes’. I got through this in about 30 mins, which was very satisfying. So here’s what I did.
Build the Views
I created 6 views for this challenge; 3 for the main chart, and 3 for the preview.
I started by creating the Sales by Month line chart, applied all the various formatting to remove the axis & gridlines, set the tooltips etc. Once happy, I duplicated the sheet and changed the mark type to ‘bar’, then I duplicated again and changed the mark type ‘area’
I then named the 3 sheets line, bar , area.
Next I created a ‘preview’ chart. I duplicated the bar chart, removed the text from the tooltip, then added another instance of the date pill alongside to give me a secondary marks card, which I changed to be area.
I duplicated this chart 2 more times, changing the mark types so one showed a line and area, and another line and bar.
I named these Preview :Line, Preview: Bar and Preview:Area.
Show & Hide the Data
I created a parameter called Choose Display Type to store 3 string values : Line, Bar, Area.
The parameter won’t do anything until referenced within another field, so I created the following calculated field
FILTER: Display
CASE [Choose Display Type] WHEN ‘Line’ THEN ‘Line’ WHEN ‘Bar’ THEN ‘Bar’ ELSE ‘Area’ END
I then navigated to the Line sheet created above, showed the parameter control and set the option to Line. Then I added the FILTER:Display field to the Filters shelf. The only option available to select is ‘Line’ , so I ticked it.
I then selected to ‘apply this filter’ to ‘selected worksheets’, selecting my Preview:Line sheet
Then I went to the Bar sheet. Again showed the parameter control, and changed it to ‘bar’. Then I added the FILTER:Display field to the Filters shelf. The only option available this time to select is ‘Bar’ , so I ticked it, and then ‘applied the filter’ to the Preview:Bar sheet too.
At this point, navigating back to the Line sheet, the display is blank because ‘bar’ is the selected option in the parameter, and this sheet is filtered to ‘Line’. Looking at the filter, you see as the parameter is set to bar, the only option for selection in the filter is also bar. As line <> bar, the data doesn’t show.
Finally I went to the Area sheet. Again showed the parameter control, and changed it to ‘area’. Then I added the FILTER:Display field to the Filters shelf. The only option available this time to select is ‘Area’ , so I ticked it, and then ‘applied the filter’ to the Preview:Area sheet too.
Adding to the Dashboard
So while the parameter is driving the visibility of the views, they now need to be added to the dashboard.
The trick is to use Containers objects. These can be a bit fiddly and take some time to get used to. If you haven’t used them before, I recommend you have a watch of Tim Ngwena‘s videos :
I added a vertical container to the dashboard, and one of the first things I always do when working with containers is add a blank object. I then added the 3 initial charts (line, bar, area). Due to the parameter driving the hiding function, only one of these views actually displayed, although the title of each is visible, so you can see it’s been added. As a double check to make sure all are inside the same container, select one of the views on the dashboard (it will have a grey border when selected), then double click on the ‘handle’ at the top
This will then select the layout container the view is in, identified by the blue border
and you should see the 3 sheets + the blank object surrounded by a solid blue border, separated by dotted lines.
Test changing the parameter, and the appropriate sheet should display and the others hidden. Now hide the titles of each sheet, and remove the blank object. The displayed chart should look to fill up the whole space
The ‘preview’ sheets also need adding to another vertical container, but this vertical container needs to be positioned next to some text, so what I actually need first is a horizontal container.
I add the horizontal container to exist inside the same vertical container as the 3 sheets, so it’s at the bottom. The Item Hierarchy on the left nav bar of the Layout tab, helps show where items are positioned
The horizontal container has pushed the other objects up, but I’ll deal with that shortly.
I then add a text object into this container, then place another vertical container to the right of the text object
So now we have a vertical container inside a horizontal container, inside a vertical container. Phew! Once again double-clicking on the container handle will highlight the container the object is within to help you get to grips with the arrangement (I personally wish there was a way to see the layout by displayed lines in some way that don’t show on publication – a bit like the grid display, so you can get a better sense of the structure).
Now into this final vertical container add the 3 preview sheets.
and remove all the titles again, and test the parameter control.
Finally, the requirement is for the preview to be 50 pixels. Containers allow you to set the height for horizontal containers, and the width for vertical containers.
So select the horizontal container (either by selecting on the dashboard, or click the relevant horizontal container on the Item Hierarchy).
then choose Edit Height from the container menu accessible from the right hand side. Set to 50, and voila, the section shrinks.
Week 39 of #WorkoutWednesday was set by Lorna this week. Details of the challenge and a description of what a BCG Growth Share Matrix is here.
The main complexity of this challenge is the calculations, and more so with Lorna’s stipulation ‘no table calcs allowed’.
I started using Tableau before LoDs were introduced, so I tend to be much more comfortable with Table Calcs than I do with LoDs; I know there are many people who are the other way round.
So, let’s get started. In the interest of time (it’s now Sunday afternoon, and I’ve dipped into this challenge on & off since Wednesday), I’m going to focus on the calcs involved in achieving my solution, and not necessarily going into the detail of putting it all together on the Viz.
Sales Scatter
To build the Sales Scatter Plot, we need the following information to help get the %growth and %market share values :
The total sales across all the specified years per subcategory
The sales for the latest year per subcategory
The total sales for the previous years per subcategory
The overall total sales across all the specified years
The number of years to compare against is set by a parameter to be 2,3,or 4 years (No.of Years).
To limit the data just to these years, I created a Dates To Include as
YEAR([Order Date]) >= [Max Year in Dataset] – ([No of Years]-1)
Max Year in Dataset is another field that I could have simply hardcoded to 2019, but I decided to be more dynamic, and derive it using an LoD
YEAR({FIXED: MAX([Order Date])})
which gets the latest Order Date in the data and then retrieves the Year part of it, which happens to be 2019 in this instance. Dates To Include is then True if the year of the order date is greater than, or equal to, the maximum year less one less than the No of Years param.
Eg if No of Years = 2, then Dates to Include is True if the year of the order date >= 2019 – (2-1) = 2019-1 = 2018.
if No of Years = 4, then Dates to Include is True if the year of the order date >= 2019 – (4-1) = 2019-3= 2016.
To sense check the numbers I need, I built a tabular view by Sub Category adding Dates to Include = True to the filters shelf.
Total sales across all the specified years per subcategory
is simply achieved by adding SUM([Sales]) to the view.
Sales Latest Year
The sales for the latest year per subcategory is
IF YEAR([Order Date]) = [Max Year in Dataset] THEN [Sales] END
Sales Previous Years
The sales for the previous years per subcategory is then just the opposite
IF YEAR([Order Date]) <> [Max Year in Dataset] THEN [Sales] END
The value of this field ultimately changes as the No of Years parameter changes. If No of Years = 2, then this will contain the 2018 sales, if its 3, then it will contain the sum of the sales in 2017 & 2018 etc.
The Growth % is stated as being the change for the latest year in comparison to the average sales of the previous years. So next I need
Average Sales Previous Years
SUM([Sales Previous Years])/([No of Years]-1)
Note the [No of Years]-1, as when comparing across 2 years, I’m really only being asked to compare 2019 with the previous year, ie 2018.
The difference of the latest year compared to the average, as a proportion of the average.
Now we need the Market Share of Sales. This would usually be a pretty simple Percent of Total quick table calculation on SUM([Sales]), but I’ve been challenged to do this without table calcs. So an LoD is needed instead. I created
Sales All Years
{FIXED [Dates to Include]:SUM([Sales])}
which gives me my total sales across the specified years, at the ‘overall’ level.
From this I can calculate
Market Share Sales
SUM([Sales])/SUM([Sales All Years])
The table below gives the breakdown of all these fields at a sub-category level for 2 years
Next step is to categorise each row into Cash Cows, Dogs, Question Marks or Stars based on their % Growth & % Market Share values. Lorna stated that 7% should be considered the mid-point for % Market Share, but that the Growth mid point should be half of the maximum growth value.
From the table above, 64.8% (against appliances) is the maximum value in the set, so I need a new field against each row that outputs 32.4%. Once again, with Table Calcs I would just have used WINDOW_MAX to find the max and halve it. But instead I needed another LOD, this one a bit more complex…
I find INCLUDE and EXCLUDE LoDs a bit of a black art, and there may well have been another way to do this, but it was a bit of trial and error in the end, and so once it gave the value I was after, I stuck with it 🙂 I’ve tried to write some sentences to explain it better, but can’t 😦
So with the mid point defined, the categorisation is then
Sales Category (Sub Cat)
IF [Growth Sales]<=MIN([Mid Sales Growth Constant (Sub Cat)]) AND [Market Share Sales]<=MIN([Mid Market Share Constant]) THEN ‘Dogs’ ELSEIF [Market Share Sales]<=MIN([Mid Market Share Constant]) AND [Growth Sales]>MIN([Mid Sales Growth Constant (Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Sales]>MIN([Mid Sales Growth Constant (Sub Cat)]) AND [Market Share Sales]>MIN([Mid Market Share Constant]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
This is all the information now needed to plot the Sales Scatter chart. Custom shapes were sourced from flaticon.com and I used Paint to colour and resize them appropriately, before saving them into a folder in the Shapes folder of the My Tableau Repository directory on my laptop.
Order Scatter
The steps for creating the Orders scatter are the same in principle. I need to find
The total orders across all the specified years per subcategory
The number of orders for the latest year per subcategory
The number of orders for the previous years per subcategory
The overall number of orders across all the specified years
However in doing this I got quite different numbers from Lorna, and its due to how you choose to count orders. Unlike a sales value, where the value is just attributed to a single line in the data set, an Order ID can span multiple lines, which means they can span sub-categories too. Eg imagine you have
Order A – contains 2 line of appliances, and 1 line of phones
Order B – contains 1 line of appliances.
How many orders are there? Answer = 2
How many order lines are there? Answer = 4
How many orders contain appliances? Answer = 2 (100%)
How many order lines contain appliances? Answer = 3 (75%)
How many orders contain phones? Answer 1 (50%)
How many order lines contain phones? Answer 1 (25%)
So when it comes to the ‘market share %’, should I be considering order lines (which means it’ll sum to 100%) or distinct orders which means it’ll sum to more than 100%.
I wrestled with this, and ultimately concluded, this challenge is purely for illustrative purposes, and to stick with my original assumption based on distinct orders (which I’d already calculated before finding the discrepancy). So my Market Share % won’t sum to 100%, and probably isn’t really a definition of Market Share, but I made peace with myself and moved on 🙂
# Orders
COUNTD([Order ID])
Orders Latest Year
COUNTD(IF YEAR([Order Date]) = [Max Year in Dataset] THEN [Order ID] END)
Orders Previous Years
COUNTD(IF YEAR([Order Date]) <> [Max Year in Dataset] THEN [Order ID] END)
Once again the Market share mid point was defined to be a 7% constant, while the growth % mid point needed to be half the max value, which in this instance based on the above, was 1/2 of 51.2%. The same type of LOD was required which gave me
IF [Growth Orders]<=MIN([Mid Orders Growth Constant (Sub Cat)]) AND [Market Share Orders]<=MIN([Mid Market Share Constant]) THEN ‘Dogs’ ELSEIF [Market Share Orders]<=MIN([Mid Market Share Constant]) AND [Growth Orders]>MIN([Mid Orders Growth Constant (Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Orders]>MIN([Mid Orders Growth Constant (Sub Cat)]) AND [Market Share Orders]>MIN([Mid Market Share Constant]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
Top 20 Manufacturers Bar Chart
To create the bar chart, I created a new field Manufacturer-SubCat as
[Manufacturer] + ‘ (‘ + [Sub-Category] + ‘)’
I then set about creating the same data tables as I’ve included above, one for Sales and one for Orders.
The majority of the measures used were the same, but when it came to determining the growth mid-point, I found creating a separate field was simpler than trying to use a single field that worked across both Sub-Category and Manufacturer-SubCat. I followed the same methodology though:
I also found though that the 7% market share constant didn’t really give me the split, so I decided a 3% constant would be better to give me categorisations at this level into each bracket. As I had different comparison values, I needed new categorisation fields too.
Sales Category (Manu Sub Cat)
IF [Growth Sales]<=MIN([Mid Sales Growth Constant (Manu Sub Cat)]) AND [Market Share Sales]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Dogs’ ELSEIF [Market Share Sales]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) AND [Growth Sales]>MIN([Mid Sales Growth Constant (Manu Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Sales]>MIN([Mid Sales Growth Constant (Manu Sub Cat)]) AND [Market Share Sales]>MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
Orders Category (Manu Sub Cat)
IF [Growth Orders]<=MIN([Mid Orders Growth Constant (Manu Sub)]) AND [Market Share Orders]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Dogs’ ELSEIF [Market Share Orders]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) AND [Growth Orders]>MIN([Mid Orders Growth Constant (Manu Sub)]) THEN ‘Question Marks’ ELSEIF [Growth Orders]>MIN([Mid Orders Growth Constant (Manu Sub)]) AND [Market Share Orders]>MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
To Sort the Manufacturer-SubCategory data by Sales or Orders, I created a SORT BY parameter with values of Sales and Orders.
I then created
Sort Value
IF [Sort] = ‘Order’ THEN [# Orders] ELSE SUM([Sales]) END
On the Manufacturer-SubCat field, I then set the Sort by property to be
Manufacturer-SubCat was then also added to the Filters shelf, with the filter set to
And that covers the main calcs and complexities that went into my version/interpretation of this challenge. I hate it when I can’t get the numbers to match 😦 but ultimately now feel comfortable with what I did and I hope it might explain why some of you may have got differences too…
My version of the viz is here, and I’ve included additional detail on the tooltips too which should show the numbers that went into the % calcs for each mark.
This weeks #WorkoutWednesday was set by the lovely Ann Jackson who often delivers some ‘challenging’ problems, all beautifully presented to fool you into thinking it’s going to be straightforward.
This week was no different. Time constraints meant I couldn’t dedicate the usual time to it on Wednesday, and then when I did get to it, I ended up with several false starts, that got very nearly there, but just fell at the final hurdle. I started again this evening, and finally got to something I’m happy with. So let’s get to it.
Ann’s challenge here, was to show a set of monthly KPI BANs (big-ass numbers) with a day by day comparison to the same time month in the previous year. From initial inspection, I figured that several table calculations were going to be needed. She also stated that we could use as many sheets as we liked. I ended up with 4 in my final viz; 1 displaying the BAN numbers, 1 displaying the trend chart, 1 displaying the red/green indicators to the left and 1 for the ‘days until month end’ subtitle.
Let’s start with the BAN numbers.
Ann wanted the chart to be dynamic, to be based as if you were looking at the data based on the month of ‘today’, and for it to change if you looked at it tomorrow. Since the Superstore dataset being used only contains data from 2015-2018, you can’t use the real ‘today’ date.
I authored my viz on 20th Sept 2019. I set up a table calculation to simulate today’s date as follows
Today
//simulate today to be based on the latest year in the dataset MAKEDATE( YEAR({FIXED:MAX([Order Date])}), MONTH(TODAY()), DAY(TODAY()) )
This produces a date of 20 Sept 2018 (or whatever date in 2018 you happen to be building your viz).
Since the data set is fixed, I could have simply hardcoded the year to 2018, but used the above FIXED LoD expression to be more generic. This LoD finds the year of the maximum date in the whole dataset.
I need to know the month to date sales for the month I’m in (in this case sales from the 1st to 20th September).
Sales MTD This Year
IF [Order Date]>=DATETRUNC(‘month’, [Today]) AND [Order Date]<= [Today] THEN [Sales] ELSE 0 END
This returns the Sales value for the records dated between 01 Sept 2018 and 20 Sept 2018.
This gives me my basic headline BAN number
For the BAN, I also need % change from previous year which requires
Today Last Year
DATEADD(‘year’, -1,[Today])
which returns 20 Sept 2017
Sales MTD Last Year
IF [Order Date]>=DATETRUNC(‘month’, [Today Last Year]) AND [Order Date]<= [Today Last Year] THEN [Sales] ELSE 0 END
which returns the Sales value for the records dated between 01 Sept 2017 and 20 Sept 2017.
% Change
(SUM([Sales MTD This Year]) – SUM([Sales MTD Last Year]))/Sum([Sales MTD Last Year])
This gives me the YoY difference, which I then custom formatted to
â–² 0%;â–¼ 0%
I could then set up my BAN sheet, by adding the relevant fields to the Text shelf, and formatting accordingly
For the KPI indicator, I required an additional field to set the colouring based on the value of %Change
Colour:BAN
IF [% Change] < 0 THEN ‘red’ ELSE ‘green’ END
I then created a very simple bar chart using an ‘old favourite’ MIN(1) to create an axis for a bar chart. The axis was fixed to end at 1, so the bar fills the space.
So that’s the straightforward bits… now onto the more challenging part – the trend chart.
This chart is showing the following:
The daily month to date sales for the current month up to ‘today’. This is the red/green line which is labelled with the total MTD sales as at today. At the point I’m writing this is the sales from 1-20 Sept 2018.
The daily month to date sales for the equivalent month last year, from the start of the month up to the same date last year (in my case 1-20 Sept 2017). This is the darker grey area chart up to the dotted ‘today’ reference line.
The daily month to date sales for the equivalent month last year from the start of the month up to the end of the month (in my case 1-30 Sept 2017). This is the dark + light grey area chart.
For this I knew I’d need a dual axis chart using an area chart for one and line chart for the other.
Given there’s a reference line on the axis indicating ‘Today’, I know I needed a continuous date axis, and chose to use the idea of baselining all the dates to the same year, and then filtering the viz just to use the dates in the current month (in this case September).
Date Aligned
//reset all data to pretend all against same year MAKEDATE(YEAR([Today]),MONTH([Order Date]), DAY([Order Date]))
You can see from above regardless of the year of the actual Order Date, the re-aligned date field, has the same date.
Month To Include
MONTH([Order Date]) = MONTH(TODAY())
adding this to the filter shelf and setting to True filters to just the September dates in the data set.
Area Chart
The area chart is last year’s data. So far I’ve only built a Sales MTD – Last Year field, but plotting this as a running total table calc against Date Aligned (exact date), doesn’t give me what I need….
…as it flattens out after 20 Sept, as that is when I defined the Sales value to stop being counted. I need a Sales field that continues to grow until the end of the month. I also need a Sales field that gives me my running total up to 20 Sept, but then stops.
Sales Full Month Last Year
IF DATETRUNC(‘month’,[Order Date]) = DATETRUNC(‘month’,[Today Last Year]) THEN [Sales] ELSE 0 END
Adding this to the view and changing to use a Running Total quick table calc gives me what I need
Running Sum Sales MTD Last Year
IF ATTR([Date Aligned])<=ATTR([Today]) THEN RUNNING_SUM(SUM([Sales MTD Last Year])) ELSE NULL END
This is basically only storing the running sum if the date is prior or on today.
I then changed these to sit on the same axis, rather than side by side, changed the mark type to Area, turned stack marks to off, and changed the colour to grey. As there are measures that overlap each other they give the appearance of a darker shade (sneaky huh?).
Line Chart
In a similar way described above, I can’t just use a running total of my existing Sales MTD – This Year field for the line, as that will also continue beyond 20 Sept. So I need
Running Sum Sales MTD This Year
IF ATTR([Date Aligned])<=ATTR([Today]) THEN RUNNING_SUM(SUM([Sales MTD This Year])) ELSE NULL END
Added to the view as a dual axis (synchronised) and mark type of line I get
To change the colour of the line I can’t just use the field I used to make the KPI indicator above, as my data is now at a much more granular level, and it will return me multiple % changes. I just want the overall % change. I had to create more calculated fields for this :
Total Sales MTD
WINDOW_SUM(SUM([Sales MTD This Year]))
Total Sales MTD – Last Year
WINDOW_SUM(SUM([Sales MTD Last Year]))
% Total Change
([Total Sales MTD] -[Total Sales MTD – Last Yr]) / [Total Sales MTD – Last Yr]
Colour : Line
IF [% Total Change ] < 0 THEN ‘red’ ELSE ‘green’ END
You can obviously combine all these steps into one, but I find it easier to read this way. No doubt there’s also another way I could have achieved this.
So that’s the main trend chart complete you think (don’t forget to add Today as a reference line, and label the end of the line chart), until you examine the tooltips and notice things aren’t quite giving you what you need.
Against each mark, Ann wants us to show:
Sales MTD for this year, which rises until ‘today’, then remains the same
Sales MTD for previous year, which also rises until ‘today’, then remains the same
Sales MTD for previous year, which continues until the end of the month
With the measures I’ve got on the view, the MTD Sales up to today for this year and last year stop once I pass ‘today’.
But not to worry, this actually isn’t too hard; I just need to add Sales MTD This Year,Sales MTD Last Year and Sales Full Month Last Year to the tooltip and change all the be Running Total table calcs.
Apply relevant formatting to the tooltip, and gridlines etc, hide headers & axis and this chart is now good to go!
When I then added these 3 views to the dashboard, I placed them side by side in a horizontal container, and changed the padding on each view to 0 on all sides, so they all butted up against each other and the lines for each row appeared joined up.
The subtitle showing the days until the end of month is simply a sheet showing another calculated field Days Until End of Month in the text
And so that’s about it I think… on reflection I wonder why I was being such a knob with my initial attempts where the table calcs I was using seemed to be getting out of hand…. we just all have those days I guess 🙂
For Week 37 2019 of #WorkoutWednesday, Luke Stanke challenged us to create a rounded bar chart, displaying the % of sales by sub-category in a selected region as a proportion of the whole.
In the challenge (here), Luke hinted that this could be achieved by unioning the data set together, but I recalled doing something similar before, and so didn’t think I’d need this.
Before tackling the bars though, I needed to set up the data required, which was pretty straightforward. I needed a parameter to store the region, and then a couple of calculated fields
Region Sales
If [Region] = [Region Param] THEN [Sales] END
which stored the value of the sales for the region selected and
Region % of Sales
SUM([Region Sales]) / SUM ([Sales])
which stored the % value required.
Having set that up, I then went to my stored ‘go to’ references as a refresher:
I started with Andy’s technique first, which uses MIN(0) plotted on the same axis as Region % of Sales, displayed as a line chart with Measure Names on the Path shelf to force the points to join up. Increasing the size of the line produces rounded bars.
However, I then spent some time puzzling over how to get the 2nd bar, which needed to be on a second (dual) axis, and ideally also needed to be created in a similar technique using MIN(0) to MIN(1) instead. I tried a few things, but couldn’t crack it this way, so used the technique Ryan adopts which overlays bars and circles on a dual axis.
For this, MIN(0), MIN(1) and Region % of Sales are all added to the same axis, so Measure Values is plotted against Sub-Category and Measure Names added to colour
However if you look closely, you’ll see the values go beyond 1, as the marks are all stacked. We don’t want this, so we need to turn stacking off…
.. and then move Region % of Sales to the top of the list in Measure Values, to bring it to the front, and adjust the colours to suit
This gives our bars. Now, for the rounded bit.
Duplicate Measure Values, by clicking on the Measure Values pill in the columns shelf, holding down Ctrl and dragging your mouse to the right. This will create a copy of the Measure Values pill which you can drop next to it
Make the chart dual axis, synchronise the axis and change the mark types to be a bar for one set of Measure Values and a circle for the other. Adjust the sizing so the marks appear as one ‘lozenge’ shape
Labelling the end of the bar with the % values, needed a little bit of creativity. Just adding Region % of Sales to the label of the ‘bar’ marks card showed the label a bit too close to the display, even when right-aligned, as the circle mark was taking up the space
Labelling the circles instead would have meant a bit of trickery to only label the last circle.
So the quickest & easiest thing to do, was to simply create a label field to make the spacing work :
#WorkoutWednesday 2019 Week 36 was set by Curtis Harris, and for me was a nice gentle challenge to finish up my post holiday catch up.
The full challenge is posted here, with the main requirement being to create a ‘custom axis’, which on hover, showed intersecting reference lines on the line chart, as shown below.
This challenge involves the use of 2 views (one for the axis and one for the line chart) and then Set Actions to drive the interactivity.
To build this, I created a calculated field to essentially store the month/year of each record
Month Order Date
DATE(DATETRUNC(‘month’,[Order Date]))
This is then custom formatted to m-yyyy to get the required presentation
The line chart then simply plots Month Order Date (set to continuous, exact date) against SUM([Sales])
Using Month Order Date, I then created a set, Selected Date Set, and just selected one of the values listed. It is this set that will get changed via the use of Set Actions later.
Selected Date
IF [Selected Date Set] THEN [Month Order Date] END
This field stores the date that has been selected in the set, and can then be added to the Detail shelf on the line chart (as a continuous, attribute), so it can be used as a reference line on the date axis.
Sales Ref
IF [Month Order Date]=[Selected Date] THEN [Sales] END
This field stores the value of the sales for the date selected in the set, and can also be added to the Detail shelf on the line chart, so it can be used as a reference line on the Sales axis.
Custom Axis
The custom axis simply plots Month Order Date on the columns with mark type of circle.
However the requirement states that if the month is the last month or the start of a quarter, the text should display rather than a dot. To do this I needed…
IF ([Month Order Date] = DATETRUNC(‘quarter’,[Month Order Date])) OR ([Month Order Date] = [Max Date Month]) THEN [Month Order Date] END
If the month is also the same as the quarter, or the month is the last month, return the month.
Putting this field onto the Label shelf, centre aligning and allowing labels to overlap marks, almost gives the required affect….
I don’t want the dots showing through when the labels exist, so I created
Colour : Circle
IF ISNULL([Order Date Display]) THEN ‘teal’ ELSE ‘white’ END
and added this to the Colour shelf,and adjusted the colours to suit.
Now it’s just a case of putting the sheets together onto a dashboard, so we can then invoke the Set Action, which is sourced from the Custom Axis sheet on Hover, affects the Selected Date Set, and empties the set when the mouse is moved off (so causing the reference lines to disappear).
That’s the core features this challenge is testing. There’s a few other bits and bobs listed, which I haven’t gone into, so do let me know if there’s something you’re struggling with that I have mentioned.
The final guest #WorkoutWednesday challenge for August was set by zen master, ambassador and fellow #WorkoutWednesday enthusiast Rosairo Gauna.
The challenge is here and built on a challenge set earlier in the year by Luke Stanke which involved Set Actions. Rosario was challenging us to use Parameter Actions instead to navigate the hierarchy (they weren’t around when Luke set his challenge), as well as not duplicate the data set.
I have to say, this really stumped me. Huge kudos to Rosario for managing to figure out how to use Parameter Actions in this way.
I had to reference Rosario’s own blog post to get to the bottom of this, so I won’t be documenting this myself.
This is going to be one of those challenges that demonstrates a technique that if I ever want to use again, I’ll be referencing this post (well Rosario’s post via this post), rather than remembering all the steps/calculations… a bit like I do when I have a need to build a trellis chart or if I ever had to build a radial chart again – I have my ‘go to’ reminder posts.
