How does the company perform in the fiscal year?

Guest poster Ivett Kovacs was back to set the weekly #WOW2020 challenge this week, and delivered a very ‘relatable’ business challenge – Fiscal Year to Date reporting.

I’ve been quite used to doing this type of reporting within my job, so on the whole I found the core requirements pretty straightforward – there’s just a lot of calculated fields 🙂 The trickiest part I found was getting everything organised on the dashboard and making the budget parameters appear/disappear based on the filter selected – this took an extraordinarily looonnnng time even though I knew the technique – more on that later.

Setting up the Fiscal Year

The data source provided was one curated by Ivett. It contained financial ‘transactions’ against Account Codes from Oct 2018 to May 2020.

The date in the file was initially recognised by Tableau as a string, but simply changing it to a Date datatype in the Data Source pane easily resolved this.

The fiscal year starts on 1st October, so once on a sheet, set this by right-clicking on the Accounting Date field and choosing Default Properties -> Fiscal Year Start -> October

If you add Accounting Date to a sheet and expand from Year -> Month, you’ll see that the Year part is now labelled as FY 2019 and FY 2020, and Q1 starts in October

Just a couple of points to be aware of if you’re not familiar with working with Fiscal Years.

  • A relative date filter for ‘this year’ will recognise your fiscal date setting, so if you did this on this data set, you’d get data from Oct 2019 to May 2020.
  • Any date related functions such as YEAR([Date]) or DATETRUNC(‘year’, [Date]) does not recognise the fiscal year setting. So YEAR(#2019-11-01#) will return 2019 and YEAR(#2020-01-01) will return 2020 even though they are both in the same FY 2020 fiscal year. It does mean at times, depending on what you’re building, you may need to hard-code information that defines the start month of your fiscal year.

Building all the calculated fields

First up we need a couple of measures as stated in the requirements.

Sales

IF STARTSWITH([Account Number],’5′) THEN [Value] END

only counts for Account Number starting with a 5

OPP

IF STARTSWITH([Account Number],’5′) OR STARTSWITH([Account Number],’696′) THEN [Value] END

only counts for Account Numbers starting with a 5 or 696.

Then we need lots of fields to help us get all the various data needed. This is simply going to be a big list 🙂

Current Month

DATE(DATETRUNC(‘month’,{FIXED : MAX([Accounting Date])}))

finds the maximum date in the dataset and sets to the 1st of the month, in this instance it will be 01 May 2020. This is a more generic approach than harcoding.

FY Start Current Year

#2019-10-01#

I said there might need to be some hardcoding! Ideally, having set a fiscal year, you’d like a function along the lines of DATETRUNC(‘fiscal year’, [Date]) to give you this value. Apart from setting this value in a parameter, I’m not aware of any way you can determine this date without any sort of hardcoding 😦 This KB article tries to provide some suggestions, but you still need to hardcode a value to ‘shift’ to the appropriate month; in this case we’d have to hardcode 10 as October is the 10th month. Feel free to upvote this idea on the Tableau Forums which asks to address this 🙂

Current FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [Sales] END

only return sales values for dates from 01 Oct 2019 to 31 May 2020.

Current FTYD OPP

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [OPP] END

as above but for the OPP measure.

Current Month Prev FY

DATE(DATEADD(‘year’,-1,[Current Month]))

go back 1 year to 1st May 2019.

Prev FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date])<= [Current Month Prev FY] THEN [Sales] END

Only return sales values for dates up to 31 May 2019. Note the data only starts from 01 Oct 2018, which is the start of the previous FY, but if we couldn’t guarantee that, we’d have stored an ‘FY Start Prior Year’ and added an additional clause to the above calculation.

Prev FYTD OPP

IF DATETRUNC(‘month’,[Accounting Date])<=[Current Month Prev FY] THEN [OPP] END

as above for OPP.

Curr vs Prev Sales Diff

SUM([Current FYTD Sales]) – SUM([Prev FYTD Sales])

the actual difference in sales

Curr vs Prev Sales Diff %

[Curr v Prev Sales Diff] / SUM([Prev FYTD Sales])

the % change in sales

Curr vs Prev OPP Diff

SUM([Current FYTD OPP]) – SUM([Prev FYTD OPP])

the actual difference in OPP

Curr vs Prev OPP Diff %

[Curr v Prev OPP Diff] / SUM([Prev FYTD OPP])

the % change in OPP

To deal with the Budgets, we need to create a couple of parameters Budget Sales (M) and Budget OPP (M). Both are integer parameters defaulted to 2,300 and 3000 respectively.

But because these parameters aren’t actually in millions, we need further fields to translate them into the value we really need for comparisons.

Budget Sales Ref Line

[Budget Sales (M)] * 1000000

Budget OPP Ref Line

[Budget OPP (M)] * 1000000

and now we can compute the differences

Curr vs Budget Sales Diff

SUM([Current FYTD Sales]) – MIN([Budget Sales Ref Line])

We need the MIN() function as the same Budget Sales Ref Line value is stored on every row, so SUM() will multiply the value too much. AVG() or MAX() would have worked just as well.

Curr vs Budget Sales Diff %

[Curr v Budget Sales Diff] / MIN([Budget Sales Ref Line])

and again we need to duplicate these for the OPP measure

Curr vs Budget OPP Diff

SUM([Current FYTD OPP]) – MIN([Budget OPP Ref Line])

Curr vs Budget OPP Diff %

[Curr v Budget OPP Diff] / MIN([Budget OPP Ref Line])

Now we’ve got all the core variables we need, we need to determine which one to use as the comparison in the bar chart and the KPI indicator. This is based on a parameter for the user to decide if they’re comparing Actuals against Budget or against Previous Year Actuals.

Compare Filter

I chose to create an integer based parameter with the display altered to show the relevant text. We will be using this parameter in calculated fields and comparing integers rather than strings is much more efficient (and easier to read).

So with the parameter set up, we now need to create a few fields that we’ll use to show the values we want

Sales Diff

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff] ELSE [Curr v Prev Sales Diff] END

This is custom formatted to display as $ in millions with an arrow to show positive or negative : ▲”$”#,##0,,M;▼”$”#,##0,,M

Sales Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff %] ELSE [Curr v Prev Sales Diff %] END

This is formatted to 1 decimal place.

Opp Diff

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff] ELSE [Curr v Prev OPP Diff] END

Same custom formatting as above.

Opp Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff %] ELSE [Curr v Prev OPP Diff % ] END

Sales Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget Sales Ref Line]) ELSE SUM([Prev FYTD Sales]) END

This is used for the line shown on the bar chart, and we’ll need the same for the Opp measure.

Opp Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget OPP Ref Line]) ELSE SUM([Prev FYTD OPP]) END

Finally we need a couple of fields to use to work what colour the bars need to be.

Colour : Sales Diff

IF [Sales Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [Sales Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Colour : OPP Diff

IF [OPP Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [OPP Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Note – my published solution has something slightly longer winded as when I originally built the viz, I created the colour fields before I created the generic Diff/Diff% fields referenced above.

Right! That’s a LOT of calculated fields (I did warn you!). In some cases it may have been possible to combine, but I like creating building blocks to keep things simpler to read.

Sales YoY Trend Line

The basis of this type of chart is pretty much Desktop 101.

  • Month(Accounting Date) on Columns (blue pill)
  • Sales on Rows
  • Year(Accounting Date) on Colour with colours adjusted accordingly.
  • Add the average line and Label the most recent point
  • Format the gridlines/rows etc and axis

The most recent mark is a larger circle. We need another calculated field for this

Current Month Sales

IF DATETRUNC(‘month’,[Accounting Date]) = [Current Month] THEN [Sales] END

This just stores the sales value for the latest month.

Add this field to the Columns and make dual axis and synchronise axis. Adjust the marks back to be a line and a circle and adjust the Sizes to suit.

Duplicate all the above on another sheet for the OPP values instead.

Sales Bar Chart

Simple bar chart

  • Current FYTD Sales on Columns
  • Colour : Sales Diff on Colour
  • Sales Ref Line on Detail
  • Add Sales Ref Line as a reference line and label & format to suit.
  • Label bar and align middle right; format to suit.

The colour legend will only display a single option at a time. You’ll need to set the Compare Filter to compare against budget and show, then adjust, the value of the Budget Sales (M) parameter to values that go beyond the thresholds, to set the other colour options.

Repeat all this again on another sheet for the bar displaying the OPP measure.

Sales KPI

Simply add Sales Diff & Sales Diff % to the Text. Format row/column lines to suit.

Again, repeat for the equivalent OPP measures.

Year Legend

The standard legends aren’t used as these are only square icons, but the challenge shows circles. So a custom legend sheet is created as below

The hidden axis has been fixed from 0.45-1 to push the display to the left more.

% Diff Indicator Legend

Again the standard legend can’t be used this time, as only 1 option ever shows. So this is a custom ‘fake’ legend.

I simply used the Account Number field for this purpose. I filtered a sheet to just 3 different Account Numbers. I then built a similar viz to the above, but using Account Number throughout.

The sneaky trick I used here was to simply ‘alias’ the actual Account Numbers displayed to the text I wanted.

Building the dashboard

Getting all the objects in the right places can be a bit of trial and error. All the objects on my dashboard, apart from the Budget parameters, are tiled using a mix of horizontal and vertical containers, nested within.

Hide/Show the Budget Parameters

This probably took me the most time. It’s using a technique sometimes referred to as Parameter Popping. It uses containers and works similarly to sheet swapping.

I had to build a ‘blank’ sheet for this – a sheet that will show (a blank value) and hide based on the Compare Filter parameter.

I needed another field

Is Prev Value to Compare

[Compare Filter]=1

This returns true if the parameter is set to compare against the Previous Year rather than the Budget.

This is added as a Filter to the blank sheet and set to True, which means the sheet will ‘show’ when comparing to Previous Year, and hide when comparing to Budget.

A floating horizontal container is then added to the sheet, and all the objects – the Budget parameters, some Text boxes to label the parameters and the ‘blank’ sheet are ‘carefully’ added. The blank sheet should be the first object on the left. I say ‘carefully’ as it took a lot of trial and error to make it work. If it is done right, then changing the Compare Filter parameter should make the budget parameters move left & right as the blank sheet shows and then hides.

The floating horizontal container is made wider than the dashboard itself, so the showing of the ‘blank’ sheet, pushes the other objects so far to the right that they’re not on the dashboard at all.

The screen shots below show what it looks like in Desktop

As I said, Parameter Popping can be a bit tricky – I only used it twice, so haven’t yet found a sure fire way to get it right first time. If you google ‘Tableau Parameter Popping’ you’ll find a few links that might help, and/or check out Week 4 of #WOW2020 which also uses it (the other time I’ve used it).

And that’s about it. My published viz is here. Enjoy!

Happy vizzin’! Stay Safe!

Donna

Do you want to build a simple Sales dashboard?

This week’s challenge was the first challenge to be set by guest author Meera Umasnakar, and is mainly a formatting/layout challenge.

The requirement was to show some month to date / year to date metrics in comparison to the previous month to date, and also work out where the current month might finish.

I took up the challenge on Weds 25th March, and the challenge was dated ‘as at 24th March’. There was nothing in the requirements to indicate whether this was ‘hardcoded’ to this date, or whether it happened to be this date based on the time I viewed (ie it was yesterday, the last full day). In the interest of being ‘flexible’ I therefore chose to design my solution more dynamically. I based my challenge on viewing the data up to ‘yesterday’, where ‘yesterday’ is yesterday’s date in 2019. So if today is 27 March 2020, then the dashboard will be based up to 26 March 2019. Hope that’s clear.

As a result of this, there’s a fair few date calculations involved, so let’s crack on.

Setting up the calculations

I like to build up my calculations so they’re easier to read, rather than nest everything. First up we need

Current Year

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

The maximum year in the data set (which happens to be 2019 for the data source I’m connected to).

Yesterday

MAKEDATE([Current Year],MONTH(TODAY()),DAY(TODAY()))-1

Fake the date based on today’s date (see discussion above).

Current Month

DATETRUNC(‘month’, [Yesterday])

returns yesterday’s date to be 1st of the month eg 26th March 2019 becomes 1st March 2019

Current MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Sales] END

If the order date is between the 1st of the month and yesterday, then capture the Sales value.

We can repeat for profit

Current MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Profit] END

Current YTD Sales

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Sales] END

If the order date is within the latest year, but also less than today, then capture the Sales value.

Again repeat for profit

Current YTD Profit

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Profit] END

Format all of these to $ with 1 decimal place.

To work out the numbers we need for the previous month, we need some further dates

Yesterday Previous Month

DATEADD(‘month’,-1,[Yesterday])

Just takes 1 month off of yesterday’s date.

Previous Month

DATETRUNC(‘month’, [Yesterday Previous Month])

gets back to the 1st of the previous month’s date.

Previous MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Sales] END

If the order date is within the previous month, but only up to the equivalent day of the month (previous month to date), then capture the Sales value.

Previous MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Profit] END

Format both of these to $ and 1 decimal place

MoM Sales

SUM([Current MTD Sales])-SUM([Previous MTD Sales])

Month on Month Sales, is just the difference between the two variables.

Similarly for profit

MoM Profit

SUM([Current MTD Profit])-SUM([Previous MTD Profit])

Both these need custom formatting applied

▲”$”#,##0,.0K;▼”$”#,##0,.0K

To get this, use the formatting to set to $ and 1 decimal place, then once set, change the format to ‘custom formatting’, which will display the ‘formatting code’. Then add the ▲▼ symbols. I use this site to get the characters I need.

The final calculation we need to work out is what’s been referred to as the ‘run rate’. This is basically trying to show what the final month sales/profit will be, based on the current rate. This means taking the current MTD Sales/Profit, dividing it by the number days this has been computed over to get an average sales/profit per day. Then this number is multiplied by the number of days in the month. Got it?

So we need to start by working out….

Days in Current MTD

DAY([Yesterday])

This just returns the number of the day.

Days in Current Full Month

DATEDIFF(‘day’, DATETRUNC(‘month’, [Yesterday]), DATEADD(‘month’,1,DATETRUNC(‘month’, [Yesterday])))

This is counting the number of days between the 1st of the month (based on yesterday), and the 1st of the next month.

Run Rate Sales

(SUM([Current MTD Sales])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Run Rate Profit

(SUM([Current MTD Profit])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Format these to $ and 1 decimal place.

This gives us all of the core calculations we need to build the KPIs.

Building the KPI card

This is done on one sheet, and is simply utilising the Text mark type.

By typing in, create a pill MIN(0) on the Rows shelf, and another one right next to it. Change the mark type to Text. This gives you 2 ‘cards’ you can now use. Add the all Sales related calculated fields to the Text shelf of the first card, and the Profit fields to the second card, and just format the font/layout accordingly. Then remove the axis headers, gridlines etc etc.

Building the Sales YoY Bar Chart

To ensure we only have the dates for the last 2 years, up to the current point in time, we need some additional fields

Previous Year

[Current Year]-1

Dates To Include

YEAR([Order Date])>= [Previous Year] AND [Order Date]<= [Yesterday]

Add Dates To Include to the Filter shelf and set to True.

Now build the viz by

  • Sales on Rows
  • Order Date on Columns, set to the Month level only (discrete blue pill).
  • Mark Type = bar
  • Order Date on Colour, set to Year level. Adjust colours to suit
  • Order Date on Size, set to Year level. Adjust size to suit so the latest year is narrower.
  • Set Stack Marks = Off (Analysis -> Stack Marks). This will stop the bars for each year from sitting on top of each other.
  • Format the Order Date axis, so the Month is displayed as 1st letter only.
  • Format the Sales axis so the value is displayed to K with no decimal places.
  • Due to the above, the format of the Sales value on the tooltip is likely to change too. If this happens, duplicate the Sales field, rename it to Tooltip – Sales or similar and format to $ with 0 dp. Add this to the Tooltip shelf.
  • You’ll need to do similar to get a month field for the tooltip. Create a calculated field Tooltip-Month = DATETRUNC(‘month’,[Order Date]) and custom format this to mmm yy. Add this to the Tooltip shelf.

Repeat the same steps to build the YoY Profit chart.

Date Sheet

The final dashboard indicates the date of the report. As my dashboard is dynamic and changes based on the current date, I couldn’t hardcode this. So I built the date to display on another sheet. This means I have 1 more sheet than stated in the challenge.

I simply added Yesterday to the Text shelf and referenced it

Building the Dashboard

Here we get into a bit of container fun! As I did last week, I’ll try to just step through the order you need to place the objects on the dashboard…

  • Add a Text object to store the title.
  • Beneath it, add the Date sheet.
  • To the right of both of these, add another Text object to store the sheet information text which will be displayed top right.
  • Add a Horizontal container beneath all of the above. Set the background of this container to light grey, and Inner Padding to 10 all round
  • Add the KPI sheet into the container and remove the title. Adjust the height of the objects to suit.
  • Now add a Vertical container to the right of the KPI chart. Adjust width to suit
  • Add the Sales YoY chart to the vertical container. Set the background of this object to white, so the title background isn’t grey.
  • Add the Profit YoY sheet below the Sales one. Again set the background of this to white.
  • Remove the container on the right hand side that contains the legends.
  • Add a floating blank object to the sheet. Set the background of this to light grey, and then adjust the positioning and height and width so it’s splitting your KPI card.
  • Finally if you haven’t already, edit the title and summary text appropriately.

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

Happy vizzin’!

Donna

Can you show missing selected periods and autosize bars?

Newly wed Lorna Brown (previously known as Lorna Eden 😉 ) returned for this week’s #WOW challenge.

The requirement was to create a bar chart showing number of orders per subcategory per day/week/month over a user defined number of years. The chart is coloured based on Profit. The bars also need to change size based on what date part was being displayed Lorna already hinted they’d be parameters and set actions involved.

Building the chart

First off let’s create the parameters we need to drive the dates we need to include in the viz.

Select Period

A string parameter listing Daily, Weekly, Monthly. The trick here is to store the datepart of day, week, month as the value, while displaying the required text for selection. Default to Weekly.

Doing this means later we can refer directly to the parameter when we need some date manipulation.

Number of Years

A range integer parameter starting from 1 to 4, defaulting to 2

With these, we can define the Date field that we need to plot on our axis

Date to Plot

DATE(DATETRUNC([Select Period],[Order Date],’Sunday’))

This has the effect of truncating every order date to the 1st day of the relevant month or week or just to the day, so for example if ‘month’ is selected all the orders placed in May 2019 will be grouped together under 01 May 2019 etc.

Dates to Include

YEAR([Order Date])>={MAX(Year([Order Date]))}-([Number of Years]-1)

{MAX(Year([Order Date]))} is a shortened notation for the level of detail calculation (LoD)

{FIXED : MAX(Year([Order Date]))}

which basically returns the highest year in the data set, which in this case is 2019. If the [Number of Years] parameter is set to 1 for example, we would expect all of 2019 to display, hence we need to subtract 1 in the formula so we get all orders in 2019.

Adding this to the Filter shelf and set to true will limit the orders to the dates in the years required.

Finally we need the measure

Number of Orders

COUNTD([Order ID])

Right, with those parameters set, we can now build the basic bar chart. For now we’ll just restrict the data to a single Sub-Category directly – we’ll look to adjust this later.

  • Add Dates to Include = True to Filter
  • Add Sub-Category = Tables to Filter
  • Add Date to Plot as a continuous exact date to Rows (green pill)
  • Add Number of Orders to Columns
  • Add Profit to Colour
  • Set Mark Type to Bar
  • Adjust the Tooltip to match
  • Show the Select Period & Number of Years parameters
  • Adjust the Sheet Title to reference the Select Period parameter

Sizing the bars

You’ll see the bars all look a bit overlapped. You might be tempted to adjust the bar size by moving the slider to reduce the overlap, which may well work, but as you change the Select Period parameter you’ll find that what you’re doing is setting the bar width to set width that is the same whether you have 24 marks displayed (monthly for 2 years) or 156 marks (daily for 2 years). The requirement is for the bar to adjust in width, so it’s wider when there’s less marks.

To do this, we need a calculated field

Size

CASE [Select Period]
WHEN ‘day’ THEN 1
WHEN ‘week’ THEN 5
WHEN ‘month’ THEN 10
END

These are just arbitrary values I chose, and you can play around with the values to suit, but the key is you’re choosing a range of numbers with the smallest for ‘day’ and the largest for ‘month’.

Add this field to the Size shelf. and change it to be a Continuous Dimension ie a green pill of just Size rather than SUM([Size]).

Then click on the Size shelf and change from Manual to Fixed, and set the alignment to Centre.

Changing the Select Period parameter you’ll see the bars adjust their width from being very narrow for Daily, wider for Weekly, and wider again for Monthly.

Sub Category Selector

This is based on techniques that have cropped up in a few #WOWs this year.

We’re going to be using Set Actions for this bit, so to start we need a Set.

Right click on Sub-Category -> Create Set. Name the set Selected Sub-Category and just select a single value, Tables for now.

Now on a new sheet, add MIN(0.0) to Columns (type directly in) and Sub-Category to Rows.

  • Change the Mark Type to Shape
  • Add Sub-Category to Label
  • Add Selected Sub Category set to Shape, and adjust the shape and colour to suit
  • Create a calculated field called True and another called False, each containing the value True and False respectively – add these to the Detail shelf.
  • Edit the Axis to be Fixed to start at -0.07 to 1. This is to shift everything to the left.
  • Turn off Tooltips
  • Format to remove all column, row, zero & grid lines & axis rulers.
  • Uncheck Show Header on Sub-Category and MIN(0.0)

Building the dashboard

First up, now we’ve got a Set to store the Selected Sub-Category, remove, the Sub-Category field from the Filter shelf of the bar chart. Add the Selected Sub-Category set to the Filter shelf instead.

Now create a new dashboard sheet and add the bar chart to it.

By default, the Profit colour legend and parameters will be displayed in a vertical layout container to the right of the bar chart.

Remove the colour legend, and position the Select Period parameter above the Number of Years.

Then add the Selector sheet between the two parameters, and remove any additional legends that get added.

Add a dashboard action Deselect against the Selector sheet to stop the un-selected Sub-Categories from fading out.

Add a further dashboard action Select Sub Cat against the Selector sheet to set the value of the Selected Sub-Category set on selection. The dashboard action should be set flagged to Run on single select only, so multiple values can’t be chosen.

Change the vertical layout container to be floating, then adjust the height and set the background colour to white.

Use the Add Show/Hide Button option on the Layout container to enable the collapsible container functionality.

You’ll just need to move things around a bit, adjust the sizes to suit, but that should be pretty much it.

My published version is here.

Keep safe & Happy vizzin’!

Donna

Can you show and hide your sheets?

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 :

Tableau Layout Containers : Part 1 The Basics gives an overview of the types of container, and the UI appearance when using them and where to drag objects to.

Tableau Layout Containers : Part 2 – Tableau Menu Interface expands on the above with a particular use case.

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.

My published version can be viewed here.

Happy vizzin!

Donna