Corey’s Table Challenge

I’m going to attempt to be really brief this week, as I have very little time to get this drafted before I head off on my holibobs for a couple of weeks (so I’ll be off grid for the next couple of #WorkoutWednesdays but will attempt to catch up when I return).

This week’s challenge was set by IronViz 2018 finalist, Corey Jones, and focuses on formatting a table – full challenge details here.

Presenting data in Tableau in an ‘out of the box’ table chart type, does not allow row level formatting in the manner described in this challenge (Tableau is not Excel you know :-))

So you have to be creative, and this is what this challenge is all about.

Corey gives the clue that no more than 3 sheets are allowed, hinting that the above presentation is not all on one view, but at least 2 carefully arranged side by side on a dashboard.

I had 3 views in my dashboard – the shaded table of sub-category and measures, the stacked bar of % of region sales vs rest, and the title.

Now I ended up with 3 including the title, as I used the [Region] field as a quick filter throughout my build, and so to enable the title to change dynamically I needed to create a sheet so I could reference that field. Having looked at Corey’s solution later on, he chose to use a parameter to drive the Region selection. Parameters can be referenced in dashboard titles, so in Corey’s solution, the Sub Category list on the left hand side is also a view, whereas I combined with the measures. The only thing I couldn’t achieve with the combination was being able to left-align the Sub-Category heading, while leaving the other headings (Sales, Profit etc) centre-aligned.

The formatted table

The magic ingredient in building formatted tables is using an axis with labels. In this instance the field MIN(1) is your best friend.

  1. Add [Sub-Category] to rows
  2. Type MIN(1) into columns
  3. Set mark type to bar
  4. Add SUM([Sales]) to the Text shelf and right align
  5. Fix the axis from to start from 0 and ends at 1

and you get…

… something that looks pretty tabular, but you now have more flexibility with formatting styles.

This concept forms the basis for the whole table, but we need to step back quickly, as we need to set up some calculated fields.

Sales, Profit & Quantity are already defined in the dataset, but we also need

Profit Ratio

SUM([Profit]) / SUM(Sales)] formatted as a percentage to 0 decimal places

Total Sales for Year & Category

{FIXED YEAR([Order Date]), [Sub-Category]: SUM([Sales])}

This is a Level of Detail (LoD) calculation that stores the total sales for the year (2018) and subcategory, regardless of the [Region] filter being selected

% Sales for Selected Region

SUM([Sales])/SUM([Total Sales for Year & Category]) formatted as a percentage to 0 decimal places

Note Technically this field will return % sales if any filter is applied, not just region

Highlight

[% Sales for Selected Region] > ([Highlight Threshold]/100)

This returns true where the % sales is more than the threshold selected by the user by entering a value into the [Highlight Threshold] parameter

The [Highlight] field can now be used to format the ‘table’ by adding to the Colour Shelf, setting true to be grey and false to be white.

Making the text bold

My solution differs from Corey’s. I chose to create the following calculated fields

LABEL : Sales Bold

IF [Highlight] THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is true.

LABEL: Sales Normal

IF NOT([Highlight]) THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is false.

Both these fields are placed side by side on the Label shelf, and with the ‘bold’ field formatted to be bold. As no row will ever have a value set in both fields at the same time, only a single value is displayed

Showing the column title at the top

I did this by creating an additional axis MIN(0), and applying dual axis which I synchronised. I removed all the pills from the MIN(0) marks card, and the [Measure Names] that had been automatically added to the colour shelf on the MIN(1) marks card.

This gave me an axis at the top which I could then edit – I changed the title to Sales, and set the axis tick marks to None for both major & minor ticks marks. On the bottom axis, I also had to edit, in this case setting the title to nothing as well as setting the axis ticks to none.

The height of the axis was also adjusted to make it appear closer and the font formatted accordingly. The Sub-Category was also set to not ‘Show Header’.

To create the other columns, the process is repeated with further dual axis MIN(1) & MIN(0) fields added to the columns, with further bold/normal Label fields also created.

The More Fields to the bottom left lists all the MIN(1) & MIN(0) cards in order.

To get the ‘column’ line to appear between the sub-category and sales columns, I set the transparency of the MIN(0) card associated to the Sales measure back to 100%, and it gave the appearance of a solid line.

Stacked Bar Chart

This is also a dual axis chart, which plots the % Sales for Selected Region field alongside our new friend MIN(1), where the MIN(1) axis is set to just over 1.

  • The Measure Names colours are set to grey (% Sales) and white (MIN(1))
  • The MIN(1) axis is set to the back
  • % Sales for Selected Region is the label on the associated card, and left aligned
  • A new field which is basically 1 – [ % Sales for Selected Region] is added to the label of the MIN(1) card and right aligned.
  • A border is applied to the bars via the Colour shelf

To create the ‘column heading’ a new field is required

LABEL: Bar

[Region] + ‘ vs. All Other Regions’

This is then added to the column shelf to get

so when the Region filter changes, the title changes too.

Both these sheets are then positioned carefully side by side on a dashboard, setting the Fit to Entire View. The padding of the table sheet is adjusted so it has 0 outer padding on the right, whilst the padding of the stacked bar is adjusted so it has 0 outer padding on the left.

And finally, the Region filter is added to the dashboard, set to floating positioned just near to the heading of the stacked bar. It’s size is also adjusted so only the arrow part remains.

There’s a fair few other little formatting bits and bobs to get the text right, remove columns etc, but these are hopefully settings you already know about. Feel free to contact me if you can’t figure something out.

My solution is available here.

Happy vizzin!

I’m off for some sunshine 🙂

Donna

Advertisements

Can you create step area charts?

Continuing on from last year, August is #WorkoutWednesday takeover month, with challenges posted by invited guests. First up, for Week 32 was this challenge from Klaus Schulte to create a step area chart WITHOUT the use of data densification (ie duplicating the data set).

WARNING! This blog describes my approach to solving the challenge which I can only describe as trying to find your way through a maze for the first time – you take a few wrong turns, double back on yourself several times, but eventually get to the the centre, feeling quite satisfied. However, you later find, that if only you’d turned right rather than left to start with, there would have been a much quicker direct route to get to the end. My approach to this challenge ended up being quite cumbersome due to the path I started down. Klaus’ solution is much simpler 🙂

There were minimal clues in the challenge requirements, apart from the fact that table calcs were going to be required and, as stated, you couldn’t duplicate the data set. So I started by viewing Klaus’ solution on Tableau Public to see what I could glean by mousing over the chart. From doing this I understood that

  1. The stepped area chart had to be managed using a single axis, since the dual axis would be needed for the ‘bonus’, highlighting the max & min drop.
  2. The stepped area chart was indeed an ‘area’ mark type (and not carefully disguised bars or similar), since when you hovered over a mark, an indicator appeared at both the top and bottom of the chart, and if you selected anywhere, the whole section was highlighted

Stepped Area Chart

So where to start…. I decided to watch the relevant section of the TC Europe talk Klaus referred to in the challenge intro, in which he showed a way of doing this by duplicating the data, to see if there were any clues there. By doing this, I understood that

  1. The difference between an area & line chart was that with an area chart, there is no Path shelf, so you can’t control how the marks will be joined up. An Area chart will just join the marks from left to right. This was already familiar, but the reminder was useful to jog the memory.
  2. Whilst Klaus’ solution was using a duplicated data set, he incorporated a method of offsetting the point to plot forward (+0.499) or backward (-0.499) depending on what data set was being referenced. This reminded me of something I’d done in a previous #WorkoutWednesday challenge a long time ago, where I needed to display a side by side bar chart

So I figured I needed to find a way to ‘expand’ the data to give me 2 rows per month by using some offset logic, which I did by creating new fields

Month Position To Plot

DATE(IF MONTH([Order Date]) = 12 THEN DATETRUNC(‘month’,[Order Date])
ELSE
IF DAY([Order Date]) <=15 THEN DATETRUNC(‘month’,[Order Date]) ELSE DATEADD(‘month’,1,DATETRUNC(‘month’,[Order Date]))-1 END
END)

This sets the date to be 1st Dec if the Order Date was December (as I didn’t want an extra point), otherwise, if the Order Date was before 15th of the month, the date would be the 1st day of the month, otherwise it would be the last day of the month.

Sales In Month

{FIXED [Category], DATETRUNC(‘month’, [Order Date]): SUM([Sales])}

I needed this LoD to store the total value of the monthly sales. Without it, due to my Month Position To Plot field above, the Sales would only show the partial sales in the month.

This made my data look like

Plotting this on a chart, setting the [Month Position to Plot] to be a continuous (green) pill set to the Day level, and choosing the Area mark type, and I got a stepped area chart 🙂 The days where the marks are plotted end up being so close together, that the vertical line looks straight to the eye.

Side notes

  1. I initially created a numeric field based on the month’s number offset by +/- 4.99 to plot, instead of the date field [Month Position To Plot], assuming I might be able to use custom formatting to show the date on the axis. When I eventually found this wasn’t possible, I confirmed the axis on Klaus’ solution was actually associated to the main view, and not part of another view carefully placed on the dashboard. I found this by clicking on the small + icon that appears when hovering by the date. This isn’t a feature you’re encouraged to use, as it makes the view go all bonkers 🙂
  2. I used the alias feature to display the Category field in uppercase, as there were only 3 values, so it wasn’t overly cumbersome to type them in.
  3. The date axis was fixed to a suitable start & end to allow a decent amount of spacing between each category. I also set the axis tick months to start on 01 Jan 18 and recur every 11 months, so only Jan & Dec would display.

So this didn’t seem too bad – I’d got a step area chart using a single axis – everything was all good. On to the next bit…

Tooltips

The tooltip needed to show the category, the month/year, the sales value and the value of the change from the previous month.

Because of the way I’d gone about structuring my data, to work out the change I actually needed to get the value of the next row, and find the difference between the two. This may sound counter-intuitive since we’re asked to find the change from the previous month, but it was necessary due to that path I had chosen to build my area chart 🙂

Sales Next Value

LOOKUP(SUM([Sales In Month]),1)

This table calculation needed to be applied for each category (ie in the Edit Table Calculation window, all dimensions except the Category needed to be selected in the Compute Using section). The difference was just then comparing the two fields

Sales Diff

[Sales Next Value] – SUM([Sales In Month])

But whilst this was getting me the numbers I needed, I was stumbling into another issue.

On Klaus’ viz, the point at the top and bottom of each vertical line, represented the same month, whereas the point at the start and end of each horizontal line was the same month for me 😦

So I had to 1) build another calculated field to shift the months so I could label the points correctly, and 2) since every row didn’t show the Sales Difference (the rows with $0 above), I need another table calc to get the info relating to the previous row. This gave me

Labels

So having sorted the tooltips, my focus now changed to the labels that were displayed which were

  • the points which had the biggest rise (the maximum change) or biggest fall (the minimum change) per category
  • the points at the start and end of each year (if they didn’t correspond to being the max/min change above).

You can use WINDOW_MAX() to find the maximum difference for each category, and then compare this to the actual difference to find the matching row. However, yet again, due to my set up, the matching row wasn’t on the right point, I had to shift it again using a LOOKUP() function to find the previous row.

I had to repeat the exercise using WINDOW_MIN to find the minimum difference, and then apply similar logic using FIRST() and LAST() to identify the start and end points. It was all very convoluted and involved multiple table calculations, but it produced the output I needed. (I’ve included my data table sheet in my published workbook, for reference).

Highlighting biggest rise and smallest drop

So now onto the 2nd part of the challenge. As I mentioned at the start, I knew a dual axis would be involved to achieve the lines.

My initial thought was to use the ‘waterfall’ type technique involving a gantt mark type, sized by the difference.

However, I spotted that the blue and red vertical lines were thicker than the grey vertical lines which were also visible, which meant gantt wouldn’t be an option, as I couldn’t use the Size shelf for two purposes.

I recalled the fact you can place multiple measures on the same axis and use the line to join them up vertically (I did this creating a candlestick chart for #WorkoutWednesday2019 week 13, solution here), so started trying this out. But in this case I couldn’t just use any existing fields, as I’d ‘duplicated rows’, so had use some logic to create further fields

Sales in Month Dual Axis

IF [Sales Diff]<>0 THEN SUM([Sales In Month]) END

Sales Next Value Dual Axis

IF [Sales Diff] <> 0 THEN [Sales Next Value] END

These were basically just giving me values for every other row (so de-duplicating the data I’d expanded). I could then plot these two measures on the same secondary axis, remove Measure Names from the Colour shelf, and by setting the mark type to line, I got what I needed. I applied logic to create a field to set the colour based on whether the change was the max or min or not, and also created a field to set the size again based on whether it was max/min or not.

I then applied the dual axis function, not forgetting to synchronise the axis, tidied up the formatting and added to a dashboard. My published version is here.

But you don’t want to do any of that 🙂

You want to have a read of Rody Zakovich‘s blog from October 2017 – Create a step chart in Tableau without data modelling, and just change the mark type to Area…all the vertical dates are the same, so no messing around to get the data for the labels/tooltips …. simples really… *sigh*

Happy vizzin!

Donna

Can you create a hub and spoke map?

This week’s #WorkoutWednesday challange was a mash up with my other favourite weekly challenge, #PreppinData, the idea being to manipulate the data for the challenge in Tableau Prep, then create the viz in Tableau with the output.

I don’t plan to document this challenge to the nth degree, but just pull out the bits that I think are of most relevance… always happy for anyone to reach out though if there’s something I don’t mention that they’re not clear on 🙂

Anyway, so onto the challenge. Let’s start with the data prep….

#PreppinData Week 25

If you haven’t yet discovered it, #PreppinData is a set of weekly challenges to help you learn Tableau Prep. Carl Allchin and Jonathan Allenby set this up inspired by the success of #WorkoutWednesday. I had barely used the tool before they set the challenges up, and it’s definitely a great resource to get you started.

So this week’s challenge is defined here, and consisted of the following input

  • a file with 1 row per concert involving either Ed Sheeran or Ben Howard (ie the artists) including concert location
  • a file with lat/long data for each location
  • a file with the artists’ hometowns and lat/long

The basic requirement was to

  1. Join the concert/location data together
  2. Find the fellow artists who performed at the concert along with Ben and Ed, and expand the data so there was 1 row per concert per fellow artist as well as row for the artist themselves.
  3. Remove some ‘obvious’ duplicate rows
  4. Add in the home location for the artist

1.Join concert/location data

The location data contained Latitude and Longitude data in a single field LongLats in the format below

LocationLongLats
Rochester Hills, MI42.680396, -83.133654
Camden, NJ39.925640, -75.121151

So before combining with the concert data, this needed to be split up, which I did using the Split Values -> Automatic Split function

This automatically created 2 fields, splitting the data by the comma (,) which I renamed Long & Lat.

Warning : I don’t use mapping data that often and am therefore not overly familiar with what a typical lat/long number looks like. Given the field was originally named LongLats, I assumed the first number was Longitude and the second Latitude. This was an incorrect assumption, which I only discovered later when I displayed in Tableau to find the marks spread up and down the viz rather than across. So I came back and rectified my flow with the correct labels.

I then joined the concert data with the location data using a ‘left outer’ JOIN, to ensure all concert data was retained, even if a match to location couldn’t be found.

2.Find fellow artists and expand 1 row per concert & fellow artist

This bit took some thinking.

The Concert field contained a string of text in varying forms, that was either

  • blank
  • contained the name of the artist on their own, eg Ben Howard
  • contained the name of the concert eg Multiply Tour
  • contained a string of ‘fellow artists’, where each artist was separated by a forward slash ( / ). The number of artists listed could vary considerably
    1. eg Ed Sheeran / Taylor Swift or
    2. Mumford & Sons / Felice Brothers / Ben Howard etc

The instruction was that if the Concert field didn’t contain a slash ( / ), then there were no fellow artists.

I knew that ultimately I was going to need to split the Concert field up, pivot the data to expand 1 row into 1 or more rows, and then delete the unnecessary rows which had no fellow artist data. This is because when a field is split, you end up with however many columns are necessary for the longest string. So for the examples above, 3 split fields would be created due to example 2 above, but example 1 would also have 3 fields, with the 3rd field being blank. When this data is then pivoted, there would be 3 rows per original row, so I’d want to delete the rows where fellow artist was then blank. HOWEVER, if there had been no fellow artist in the first place (ie the Concert field did not contain a / ), I also needed to ensure I wouldn’t inadvertently be deleting rows of data I actually wanted.

I approached this in the following way (by all means there may be something more straightforward, but this is just how my brain was working at the time 🙂 )

  1. Created the Fellow Artist field, and set it to <blank string> ( ” ) if the Concert field was NULL or did not contain a slash ( / ). Otherwise I stored the Concert data.
    • This involved the use of FIND() and ISNULL() functions
  2. Removed the name of the artist if he also appeared in the listing (as I wasn’t sure it was always there).
    • This involved the use of REPLACE() to replace the artist with <blank string>
    • TIP : To manipulate a single field in multiple steps, you don’t need to keep creating a field with a new name each time, and then remove/rename. You just name the calculated field the same as the one you’re working with (eg Fellow Artist in this instance), and you can refer to the field itself in the calculation. This isn’t something you can do in Tableau Desktop, so might not be obvious if you’re familiar with that.
  3. Added the name of the artist back in to every row, including the ones where there was no artist listed. This may seem odd, given I’d just removed it, but as I said above I couldn’t be sure that the artist themselves was always present in the original list, and I wanted to ensure the Fellow Artist field in every row contained the name of the artist once only. This would ensure that when it came to deleting rows later, I wouldn’t remove rows I still needed (ie those relating to Concerts with no fellow artists).
    • If the Fellow Artist did not contain ‘ / ‘ , then store Artist else add Artist/ to the front of the Fellow Artist field.
  4. I then used the Custom Split function to split all fields separated by /

This generated several SPLIT calculated fields based on the maximum number of ‘ / ‘ found in the string. NOTE – if my input data then changed and there was an even longer string containing more / , then I’d have to manually add further calculated fields to cope with this.

I could now add a PIVOT step to transpose the data from 1 row into multiple, ensuring I was pivoting columns to rows and adding all my ‘split’ fields as the columns to pivot

This generated 15 rows for every original row, with the automatically named field Fellow Artist Split containing either blank or the name of a single artist. Adding a step to then filter all the rows where Fellow Artist Split <> ” then removed all the unnecessary rows in the data.

3. Remove some obvious duplicate rows

Determining what the authors had defined a ‘duplicate’ took a couple of attempts. They had stated in the requirements that the provided Concert ID field could not be relied upon, but that was the only clue. In my mind the Date, Artist , Fellow Artist Split and Location was probably enough, but although when eyeballing the data, this seemed to be a sensible grouping, this didn’t yield the expected number of records. In the end it seemed Date, Artist, Fellow Artist Split, Concert, Location,Venue were the fields deemed to define uniqueness.

So I created an AGGREGATE step, grouping the data by the fields listed above, and then I stored the minimum value of Concert ID against each summarised row. I then joined this aggregation back to the step before the aggregation joining on the Concert ID fields. This resulted in the expected number of records stated in the challenge.

I then applied a few tidy up steps – removing unwanted columns, renaming columns as appropriate and most importantly (to match the requirements), setting the Fellow Artist field to NULL if it only contained the name of the Artist himself.

4. Add in Home Location of Artist

This was a simple step to Inner JOIN to the Artist’s hometown location file, using the Artist field as the linking field.

I then saved the output. Complete flow below.

#WorkoutWednesday Week 31 – hub and spoke chart

So now the data was prepped, I could start to tackle Lorna’s challenge to visualise Ed Sheeran’s & Ben Howard’s tour data. The full challenge is defined here, with the core requirements of

  1. Group Locations into Regions
  2. Create Hub & Spoke maps
  3. Create a Word Cloud of Fellow Artists

In addition to the above, there are also charts summarising the number of concerts per artist per region, and a cumulative count over time. These are pretty standard / typical outputs, so I’m not going to describe how to build these, but you may notice that the numbers I present differ from those on Lorna’s solution.

This is due to the way I decided to calculate the Number of Concerts (# Concerts). As part of the #PreppinData challenge, I still had a Concert ID in my output. Due to the way I approached the challenge, the Concert ID ended up being a unique value per Concert in my output, so I chose to use this as my way to count the # Concerts (using COUNTD() function).

I did check Lorna’s solution to see what she did, but decided to proceed with my definition, knowing that ultimately it didn’t really make that much difference to the visualisations required.

1. Group Locations into Regions

I did this simply by manually selecting marks on a map.

Simply double clicking on my Lat & Long fields, then unchecking Aggregate Measures on the Analysis menu automatically presented me with a map of the world. I then selected the marks in Europe with the intention to use the automatic Group function, but it wasn’t available.

This puzzled me momentarily, but then I realised I needed a dimension (blue pill) in the view, so I added Location to the Detail shelf, and now I had the Group function visible. I repeated the process with the other marks to create multiple groupings which I renamed appropriately.

2. Create Hub & Spoke Maps

This was the primary focus of this week’s challenge, providing the ability to try out some very new features – the MAKEPOINT() and MAKELINE() functions which have only been released in v2019.2.

Being someone who doesn’t work with map data very much at all, Lorna quite helpfully added a pointer to this Tableau blog which introduced the new functions and how to use them.

So following the instructions, I created a field called Route as follows:

MAKELINE(MAKEPOINT([Hometown Latitude],[Hometown Longitude]), MAKEPOINT([Lat],[Long]))

MAKELINE() requires a start & end point. In the above the start is the point associated to the artist’s hometown, defined by the first MAKEPOINT() function, and the end is the point associated to the location of the concert, defined by the second MAKEPOINT() function.

Simply double-clicking on this new field, automatically generated the display below

This then needed to be split by Artist, filtered & coloured by Region, sized by # Concerts, and have Venue and Concert added to the Detail shelf. The Tooltip was then added and the sizing adjusted to get a better differentiation.

To get the circles at each destination, a dual axis map is required, where the second axis is just showing the point related to the Concert location. This is basically the 2nd part of the MAKELINE function above, so I had to create a new field Destination which is

MAKEPOINT([Lat],[Long])

In hindsight, to make things clearer, I probably should have created a Start field representing the starting point, and a Destination field representing the end point, and then adjusted the Route field to reference these…

Anyway, to get the circle, I did the following:

  • Duplicated the Longitude(generated) field on Columns
  • On the second Marks card, replaced the Route field with the Destination field, and removed the # Concerts from the Size shelf
  • Set the circle to have a white border (via the Colour shelf properties)
  • Made the chart dual axis
  • Set the background style of the map to Dark (via the Map -> Map Layers menu)
  • Unticked all the map option features to prevent the pan/zoom/selector toolbar from showing when you hover over the map (via the Map -> Map Options menu)

NOTE – I think there may be a bug in v2019.2.2 as whilst the map toolbar stopped being visible on the worksheet view, when I later added the map to the dashboard, it was visible again 😦 Testing the property in v2018.3.3, which is the version I use for work, I didn’t get this behaviour.

I tidied up by applying various other formatting features to meet the required display, then duplicated the sheet and adjusted the filters for other regions.

3. Create a Word Cloud of Fellow Artists

I’ve never actually had a need to create a word cloud before, so figured it would be useful to document, just as much as for my future reference 🙂

  1. Add Fellow Artist to Text shelf
  2. Add Fellow Artist to Size shelf, then change to be measure Count
  3. At this point, the chart will change to be a treemap style, so you need to change the Mark type to Text

Then just apply the various formatting/colour etc to match the requirement

Obviously there’s more views that need to be built for this challenge, and then they all need to be placed on a single dashboard with the relevant formatting and dashboard action being applied to make the Word Cloud change when a summary value is selected.

Hopefully these tasks should be quite straightforward and this is already a very lengthy post. If you’ve got this far, then thanks very much for taking the time to read 🙂

My solution is published here

Happy Vizzin’

Donna

Creating a Navigating KPI Block

You know you get those days when your mind just isn’t in the right place?  Well, that was me yesterday – even forgot to collect my daughter from her holiday club… I’d driven home before I remembered – eeek!  She was none the wiser though, as I wasn’t late – phew!

After reading through Ann Jackson’s challenge for Week 30 of #WorkoutWednesday2019 (here), I thought this should be pretty straightforward (Ann had even said so herself), so found it quite perplexing to then be sitting staring at my screen trying to figure out how to get 4 independent measures into a single view in the grid format displayed…..

So I closed my laptop lid, and walked away… well actually went and worked up a sweat in my weekly Zumba class.

After a good physical workout, shower and food, I set about readdressing the mental workout challenge, and promptly had that ‘doh!’ moment…

The focus of the challenge was to utilise some of the new features in Tableau to aid in navigation between dashboards.  Subsequently you’ll need Tableau v2018.3 to be able to complete this challenge.

The solution is built of some very simple views/worksheets, which are then placed on a number of dashboards.  The Go To Sheet dashboard action and Button dashboard object are then utilised to provide the navigation – these are the features introduced in v2018.3.

All relatively straightforward, and I have no idea why I had such a mental block….

My solution consists of 8 worksheets and 5 dashboards, and to build really ended up involving a lot of right-click -> duplicate worksheet. 

1. 4 Block KPI dashboard

This consists of 4 separate views, 1 for each measure. I built the first view (Customers) as follows:

a. Dragged Customer_ID to Text shelf, then changed the aggregation to Count (Distinct) by clicking on the white arrow (carrot) to the right of the pill.

b. Changed the mark type to Square, changed the Size to be as large as possible, and changed the ‘fit type’ to Entire View

c. Edited the text and formatted the size, set the font to ‘match mark colour’ and adjusted the alignment to middle centre

d. Coloured the box, using a recent tip I believe I got from Lorna Eden

If you just select the Colour shelf with the view above, the colour palette below is displayed

Ann mentions in her blog that she uses the Hue Circle colour palette.  The easiest way to access this specific palette is to create a ‘dummy’ pill to put on the Colour shelf.  To do this, simply double click in the space on the marks shelf, below where the text measure is shown, and type the word ‘dummy’ (including quotes) into the area

This creates a new blue (discrete) pill, which you can then add to the Colour shelf, and this will then display the Colour Legend, which you can edit, find the Hue Circle colour palette and select the appropriate colour.

e. Finally edit the tooltips to uncheck the ‘show tooltip’ option.

f. Once done, this sheet can then be duplicated to create the other KPI blocks; you just then need to change the field on the text to be CNTD(Product Name), CNTD(Order ID) or CNTD(City State), where [City State] is a calculated field I created of UPPER([City] + ‘, ‘ + [State]).

g. NOTE – You do need to remove then recreate the ‘dummy’ field on each duplicated sheet though, as otherwise, if you change the colour, it will affect all the sheets.  Simply remove the field, then create a new one by typing directly in the same manner described above.  Whilst you may name this field the same ie ‘dummy’, the removal and recreation actually creates a new instance of the pill ‘under the bonnet’, which is therefore independent of the ‘dummy’ pill on the other sheets, so can be coloured differently.

h. Add these 4 views to a dashboard.

2. Bar chart dashboard

On clicking on each block on the 4 block KPI dashboard, a new dashboard is presented showing a sorted Sales by xxxx bar chart.  Let’s work through creating the Sales By Customer dashboard.

a. Ann likes her UPPERCASE, so first create a calculated field to store UPPER([Customer Name]), and add this to rows and SUM(Sales) to columns and sort.

b. Add both fields to the Label shelf, edit the font sizes to suit, match mark colour and align left middle. Untick ‘show tooltips’ from the Tooltip shelf.

c. Apply formatting to the chart to remove gridlines, columns, rows etc, but keep the axis rulers on rows – make the line thicker/darker to suit.

d. Add a ‘dummy’ pill to set the colour again, and choose the appropriate colour from the hue circle palette again.

e. Untick ‘Show Header’ from the pills on the rows & columns shelf to remove the axis and the Customer column.

f. Change the title to SALES BY CUSTOMER, adjusting the font size & colour to match (you should hopefully see the colour you’ve recently selected out of the hue circle palette in the ‘recently used’ section of the text colour selector

g. Create a new dashboard and add this view to it, and set to ‘fit width’ if it isn’t already

h. Add a floating button object to the dashboard and position top right

i.  Edit button, by clicking the carrot and changing the option

Navigate to – select the name you’ve given the 4 KPI block dashboard

 Button style – Text

Title – GO BACK (and adjust font to suit)

Background – select the relevant colour to match

j. Back to the KPI dashboard, and select Dashboard -> Actions from the menu, and add a ‘Go To Sheet’ action.

Give it a suitable title, then select the source sheet on the KPI dashboard as the one displaying the Customer Count KPI, and set the Target sheet to be the name of the Customers dashboard created above.

Once you’re happy all is working as expected, then repeat the steps outlined above to create a Sales by Product, a Sales By City and a Sales by Order dashboard.  You may need to create some further calculated fields to handle the UPPER case formatting, and don’t forget to recreate the ‘dummy’ field to help with the colouring.

My version of the challenge is published here.

Happy vizzin’

Donna

Which months have the higher number of orders?

For week 29 of #WorkoutWednesday2019, Luke Stanke set the challenge above (described here), which is comparing the overall average of orders placed per day against the average for each month.

On the face of it, this didn’t seem too bad (especially for a challenge set by Luke).  Some days when I tackle these challenges, the path I take can be long and arduous with several false starts along the way; the result being more of a ‘happy accident’ than anything of real coherence. Luckily for me, since I’d promised to start blogging on these challenges, this wasn’t one of them.

My usual approach to any of these challenges is to take some time reviewing the published viz on Tableau Public; hovering over the various marks to understand what’s on the tooltips and seeing if I can get any clues into how the various objects (marks, views, legends, titles etc) have been rendered, whether axis are being sneakily used, understanding the interactivity at play etc.

My next step is to then get the figures right based on what’s presented.  I typically like to create a ‘data’ sheet in the workbook – a tabular view of the data and associated calculations I have built, so I can easily sense check with the published viz whether my assumptions and computations are valid, and it provides a useful reference point if I’m trying to figure out what I did sometime later. 

So I started this challenge the same way.

First up, I assumed the ‘line’ was probably a reference line, and hovering over the viz confirmed this.  Everything else seemed pretty straightforward, so onto the figures.

1. Average no of orders per day per segment

This is what the line represents, and do to this I need to find the total number of orders placed per segment and the total number of days on which orders were placed for each segment.  The average is then just count orders / count days.

Some people will create everything in a single calculated field, but I like to break things up to help me troubleshoot if things don’t quite work as intended, so I ended up with 3 calculated fields, and since the overall average was required at a level higher than the level of detail being displayed (which is month in this viz), I figured LoD calculations were the way to go.

Count Orders Per Segment

{FIXED [Segment]: COUNTD([Order ID])}

For each segment, count the number of distinct orders that exist.

Count Days Per Segment

{FIXED [Segment]: COUNTD([Order Date])}

For each segment, count the number of distinct days on which an order was placed.

Overall Avg Orders Per Day Per Segment

SUM([Count Orders per Segment]) / SUM([Count Days Per Segment])

Format this to 2dp.

2. Avg no of orders per day per segment per month

This is what each coloured ‘bar’ represents, and for this I needed to find the total number of orders placed per segment per month, and the total number of days in each month on which orders were placed for each segment.   I chose to stick with LoDs again for this :

Count Orders Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order ID])}

For each segment and month, count the number of distinct orders that exist.

Count Days Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order Date])}

For each segment and month, count the number of distinct days that an order was placed.

Avg Orders Per Day Per Segment Per Month

SUM([Count Orders Per Segment Per Month]) / SUM([Count Days Per Segment Per Month])

Format this to 2dp.

Putting these fields out in a table, you can see the first three columns contain the same values for each segment even though the data is being displayed at the month level.  The final 3 columns are the monthly figures.  These numbers all reconcile back to the data displayed on the viz.

So I’m heading in the right direction, now onto the next bit.

The requirement is to Label the bars with the percent difference between the monthly value and the overall value”, so I need another field…

3.  % Difference

…which is basically the difference between column 3 and column 6 above, as a proportion of column 3

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])/[Overall Avg Orders Per Day Per Segment]

By default though, this shows 0s when added to the table as the numbers calculated are actually 0.38, -0.33 etc, so it needs formatting as a percentage to 0 decimal places.  However, applying the standard ‘percentage’ number format won’t quite cut it for this challenge, as Luke has labelled the positive numbers with a + too.  To get this, I need to apply custom formatting.

The easiest way to get this right I find, is to use one of the default number formatting options to set the number in whatever ‘main’ format you need, eg if you have a monetary value to display in £k, use the Currency (Custom) to get all the settings right.  In this instance though I want Percentage set to 0 dp..

Once done, press ok to close the dialog box, then go to set the number format again, but this time choose Custom.  The formatting ‘style’ applied previously will be shown

and it can then be modified to get the desired format, in this instance I change to

It’s often these little formatting tips that get thrown into the #WorkoutWednesday challenges that I love the most, although sometimes they can be tucked away and hard to find (or remember).

So at this point I now think I can start to build the viz, so adding the various pills I need, and adding a reference line I get this…

A line chart isn’t what I want though, and it’s not bars either, as they start from 0.  The mark type I need is gantt

which makes me realise I need to create another field…

4.  Difference

The gantt chart has the marks in the right position, but to make the ‘bars’ I need to alter the size, and that size is the difference between the mark position and reference line, which is

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])

Placing this field on size, gives me this but while the bars are the right height, they’re not in the right place.

Using gantt bars in this way is akin to the technique used in building waterfall charts, and is rectified simply by applying a multiple of -1 to the pill on the size shelf (in this instance [Difference]).

As a shortcut, I simply type this into the pill on the size shelf itself, which is a nifty little trick.

And voila! The viz is essentially there now.  Just need to add colour and further formatting …

The ‘bars’ are coloured based on whether they’re above or below the line, ie whether the difference is positive or negative, so another field is needed :

5. COLOUR : Difference

IF [Difference]>=0 THEN ‘green’ ELSE ‘blue’ END

And popping this on the colour shelf, and adjusting the colours to suit, gives me

The main thing left now is the little formatting bits and pieces :

Borders : remove columns

Axis : remove title, set to be independent, and set not to start at zero

Reference Line : change the label font size, align left middle, and set the shading to have a white background and 100% opacity

Reference Line TootlTip (new feature in v2019.2): set to Custom as below

Note if I’d simply called my field ‘Average’ rather than ‘Overall Avg Orders Per Day Per Segment’, I wouldn’t have needed this step, though it’s always useful to try out the new ‘little’ features if you can 🙂

Label : add [% Difference] to label shelf, and format centre middle

Tooltip : add relevant fields and adjust formatting as appropriate

The final thing I noticed was the axis scale – my scale was 2dp due to the formatting of my [Avg Orders Per Segment Per Month] field.  Luke’s axis was mixed – some scales at 1dp and some with 0.

I tried a few things, like formatting the axis to be Number (Standard) which has the effect of ‘automatically’ showing a number as a decimal or a whole numbers (something I recall from a very early WorkoutWednesday challenge a couple of years back).  But this didn’t give me the desired effect.  I ended up setting the axis format to be 1 dp.  But this then meant the value on the tooltip also ended up displaying as 1dp, when I wanted it to be 2 *sigh*

To fix this I created a duplicate field of the measure being displayed (a copy of [Avg Orders Per Segment Per Month]), and formatted it to 2dp, and placed this on the tooltip instead. 

I’m going to have to have a peak at Luke’s solution to figure out what magic he’s done here….

Very final step was to add to a dashboard, and add the title and my own custom footer.

So phew done!  My published version is here.

If you’ve got this far, thank you for reading J  I can’t guarantee all write ups will be to this level – it’ll partly depend on the challenge itself, and what path I head down to solve it.

I’m now off to have a peak at Luke’s challenge to figure out that pesky axis……. or I would if his workbook was downloadable 😦

Happy vizzin!

Donna

Taking the plunge

So deep breath… here we go! My first proper public blog post. Be kind, thank you please 🙂

I know its something I probably should have done a long time ago, but it just didn’t happen. So why now…? Hmmm… good question… I’m still debating that internally. The arm twisting I got from Lorna Eden was certainly a factor: Zen Master Kriebel – you taught your student well 😉 .

You see I’m a bit of a perfectionist; I believe if a job’s worth doing, it’s worth doing well, and when it comes to blogging, I feel it should be added to regularly, at least once a fortnight, ideally weekly… isn’t that part of the ‘bloggers’ code’?

Can I manage that type of commitment? I’ve no idea, but I’m really going to give it a try. And if I don’t manage to keep it up… well that’s something I’m going to have to live with and learn from; at least I can say “I tried”.

So what will I be blogging about?

Well it’ll predominantly be Tableau based, most likely focused on dissecting the weekly #WorkoutWednesday challenges I like to do. There might be a bit of #PreppinData discoveries thrown in too, alongside a sprinkling of other Tableau/Data Viz bits n bobs I think may be of use.

If this becomes no more than my personal reference guide, then that’s no bad thing. If it can be of any use to others, then that truly will be a satisfying outcome.

Thank you for reading!

D