Tag: Week 15

Visualising population by age and gender in a population pyramid

Donna ColesLeave a comment

For this week’s challenge, we’re still in community month, so guest poster, Anna Clara Gatti, provided us with this challenge – to represent the spread of population in a pyramid. Anna provided 3 levels to the challenge, so I’m going to write this blog, building on each challenge.

Beginner Challenge

Defining the calculations

To start this we need to create some calculations. Firstly we have measures Male and Female which provide us with the population for each of the associated genders. But we need to ascertain the total population for each Country and Year so we can then calculate a % of total. So we need

Total Population

{FIXED [Country], [Year]: SUM([Male]) + SUM([Female])}

and from this we can then define

Male – % of Total Population

SUM([Male])/SUM([Total Population])

format this to a % with 1 dp

Female – % of Total Population

SUM([Female])/SUM([Total Population])

format this to a % with 1 dp

These are the core measures we want to plot, but we want to plot this against the Age. But the Age provided is ‘text’ and not a pure number. So to make life easier, we’re going to extract the number out

Age Axis

IF [Age] = ‘Less than 1 year’ THEN 0
ELSEIF [Age]= ‘100+ years’ THEN 100
ELSE INT(LEFT([Age],FIND([Age],’ ‘,1)-1)) END

Move this into the Dimensions section of the data pane (above the line ).

Now we can start to build the required viz.

Building the Pyramid

On a new sheet add Country to the Filter shelf and select Italy. Add Year to the Filter shelf. By default it will add as a green continuous pill. Just click OK to the filter dialog presented.

Then right click on the Year pill in the Filter shelf, and select discrete. Another filter selection dialog will appear. Select 2024.

Add Age Axis to Rows and change to be a continuous (green) pill. Add Male – % of Total Population to Columns, and then add Female – % of Total Population to Columns too.

Change the mark type on the All marks card to bar and reduce the size a bit. Add Measure Names to Colour and adjust accordingly.

Right click on the Male – % of Total Population axis and

  • Fix the axis from 0 to 0.01
  • Reverse the scale
  • Change the axis title

Then edit the Female – % of Total Population axis and fix the scale from 0 to 0.01 and rename the axis title to give you a symmetrical display

Add the following fields to the Tooltip of the All marks card, and then update accordingly :

  • Male
  • Female
  • Female – % of Total Population
  • Male – % of Total Population
  • Age

Finally tidy up the display by removing row & column dividers, hiding the Age Axis (right click > uncheck show header) and removing the row gridlines.

Building the KPI

On a new sheet add Total Population to Text. Revert back to the Pyramid sheet, and set the both the filters to also apply to new KPI sheet you’re creating.

Update the text to include the ‘Total Population’ label and adjust size of font and align middle centre. Change the mark type to shape and select a transparent shape (for details on how to do this refer to this blog). Set the sheet to Entire View then apply a grey background. Ensure the Tooltip doesn’t show.

Creating the dashboard

Add the Pyramid chart and the KPI chart to a dashboard. I used a vertical layout container to organise the ‘rows’ , and then placed horizontal containers within, so I had a ‘row’ to show the filter controls, and a ‘row’ to show the KPI and then a ‘row’ to display the chart.

In the ‘row’ showing the KPI I added a text object alongside and used the wingdings character set to display a square symbol which I could then just colour to represent the legend.

My beginner version of the viz is here.

Intermediate Challenge

Colouring the pyramid

For this challenge, I started by simply duplicating the Pyramid built for the Beginner challenge. I then created a new field

Age Bracket

IF [Age Axis] <=14 THEN ‘Young’ ELSEIF [Age Axis] >= 65 THEN ‘Elder’
ELSE ‘Active population’
END

and added this to the Detail shelf of the All marks card. I then added this to be on the Colour shelf as well as Measure Names by updating the symbol to the left of the pill

Adjust the colours on the colour legend accordingly, and then update the Tooltip to include a reference to the Age Bracket.

Building the dashboard

Duplicate the original dashboard you built, then swap the pyramid chart by clicking on the Pyramid chart object in the dashboard. Then on the left hand side in the dashboard pane, click on the 2nd pyramid sheet, and click the ‘swap’ icon to replace the chart..

Remove the title that’s automatically added. Then update the legend text box.

My Intermediate version is here.

Advanced Challenge

Defining the additional fields

For this challenge, we’re going to need a lot more fields, including the following parameters

Country 1

string parameter, that is a list, and is populated from the Country field when the workbook is opened. Defaulted to Italy.

Country 2

As above but defaulted to Austria

Year 1

integer parameter, that is a list that is populated from the Year field when the workbook is opened. Defaulted to 2024 that is formatted to display as a number with 0 dp and no thousands separator.

Year 2

As above, also defaulted to 2024.

With these parameters, we also need to redefine the the total population values

Total Pop – Year & Country 1

IF [Year] = [Year 1] and [Country] = [Country 1] THEN [Total Population] END

Total Pop – Year & Country 2

IF [Year] = [Year 2] and [Country] = [Country 2] THEN [Total Population] END

and we also need to capture the male and female populations for these parameters

Male Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Male] END

Male Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Male] END

Female Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Female] END

Female Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Female] END

and then in turn, we redefine the % of total calculations

Male – % of Total 1

SUM([Male Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Male – % of Total 2

SUM([Male Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Female – % of Total 1

SUM([Female Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Female – % of Total 2

SUM([Female Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Set all the % of total calcs to be % with 1 dp.

Building the Pyramid

Once again start by duplicating the pyramid chart sheet built for the intermediate solution

Drag Male – % of Total 1 and drop it directly onto the existing Male = % of Total Population pill on the Columns shelf, so it basically replaces it.

Repeat the process by dragging Female – % of Total 1 and dropping it directly over Female – % of Total Population. Adjust the colours if required.

Remove the Country and Year field from the Filter shelf and show the 4 parameters.

Drag Male – % of Total 2 and add to Columns between the two existing pills.

On the Male – % of Total 2 marks card, change the mark type to Line , remove Measure Names from Colour and move Age Bracket to Tooltip. Change the colour to dark grey/black.

Make the % of total male pills dual axis and synchronise the axis.

Repeat the process by adding Female – % of Total 2 to Columns to the right hand side of the existing Female field, adjusting the mark type and colouring and making dual axis.

Right click on the top axis and uncheck show header to hide them. Then right click on the Male – % of Total 1 axis at the bottom, as as before, fix the axis from 0 to 0.01, reverse the axis, and update the title. Repeat for the Female axis (though don’t reverse).

On the All marks card, make sure all the following fields exist on the tooltip, then adjust the tooltip as required, referencing the parameters as well.

  • Male Pop – Year & Country 1
  • Male Pop – Year & Country 2
  • Female Pop – Year & Country 1
  • Female Pop – Year & Country 2
  • Male – % of Total 1
  • Male – % of Total 2
  • Female – % of Total 1
  • Female – % of Total 2
  • Age
  • Age Bracket

Building the KPI cards

Duplicate the original KPI card

Drag Total Pop – Year & Country 1 directly onto the existing Total Population field. Then add the Country 1 and Year 1 parameters to the Label shelf, and update the text as required. Remove the fields from the Filter shelf.

Repeat this process again but add the appropriate ‘2’ versions of the fields to create the 2nd KPI card.

Building the dashboard

Once again, duplicate the dashboard and swap the pyramid charts. Replace the filter controls (if still present) with a row of parameter controls.

Swap/add the KPI sheets, and add an additional Text object. Update the text to display the ‘legends’ as required.

I chose to add navigation buttons to my dashboard to move between the 3 versions of the challenge.

My advanced version is published here.

Phew! a lot going on here it seems. Hope you managed it all!

Happy vizzin’!

Donna

Can you create digital signage in Tableau?

Donna Coles1 Comment

Community Challenge month continues this week, and Yusuke Nakanishi set this challenge where the concept is to engage the audience by providing some automated highlighting of the dimensions in the viz.

This challenge did take a bit of thinking, and what I document below is ultimately what you’ll need to build it as I did; however it took me a bit of ‘back and forth’ to get there.

Modelling the data

The viz essentially ‘ranks’ the Sub-Categories based on their Sales in 2023 (Note – I used the version of Superstore shipped with v2023.1, so I filtered to 2023 to get the same data as presented in the solution). The page control cycles through the rankings, highlighting the Sub-Category with the matching rank. Adding pills to the Page shelf essentially applies a filter to the data, so I couldn’t use the ranking calculation I created, as that would filter out all other data. I therefore created a ‘scaffold’ data source in Excel, which contained a single column called Number and contained a row for each number from 1 to 50 (I didn’t want to limit it to the number of Sub-Categories I knew existed, so I could demonstrate a more ‘generic’ solution).

I then related the Superstore data set to the excel sheet using a calculated relationship of 1=1

Creating the core calculations

We’ll work against a tabular view of the data to start with, so add Order Date to Filter and filter to the latest year in the data set you’re using (for me this was 2023, but it might be 2022 for you). Then add Category and Sub-Category to Rows and Sales to Text. Sort by Sales descending. Format Sales to $ with 0 dp.

We need to calculate two different rankings. One is the overall rank of each Sub-Category and is used in the highlighting via the page shelf. The other is the rank of each Sub-Category per Category and is needed so the bars can be displayed in the right places.

Overall Rank

RANK(SUM([Sales]))

Add this to the table, and set the table calculation properties to compute using both Category and Sub-Category, so the ranking runs from 1 to 17

Rank by Category

RANK(SUM([Sales]))

Add this to the sheet, and while this is the same calculation as the overall rank, apply the table calculation to compute by Sub-Category only. This means the ranking will restart for each Category

We need to capture the total number of Sub-Categories

Total Sub Cats

{FIXED Year([Order Date]): COUNTD([Sub-Category])}

Add this to the table.

At this point, the only data we’ve referred to in the calculations is the data from the Superstore data set. If we now add Number to Rows, we get every row duplicated 50 times. We need the Number to help with the paging, but we only want to page through 17 items (the number of Sub-Categories). To restrict this we need

Index to Include

[Number]<=[Total Sub Cats]

Add this to the Filter shelf and set to True. This will limit the duplicate rows to 17.

Now add Number to the Pages shelf. The page control will automatically be set to 1, and you’ll see all the data not related to Number =1 disappear.

We need to be able to identify/highlight the Sub-Category whose overall rank matches the value in the page control.

Rank to Highlight

[Overall Rank] = SUM([Number])

Add this to Rows and verify the table calculation is set to compute by Category & Sub-Category only. There should only be 1 row that is True per Sub-Category, and only 1 row in all the data that has Rank to Highlight = True and has data displayed in the other columns.

Move Rank to Highlight to the Colour shelf and remove the Number field from Rows. You should now only have 1 row per Sub-Category and as you cycle through the page control, the relevant row is highlighted in a different colour.

The above calculations are enough for us to build the bar chart, but we also need to be able to identify the selected Category to highlight on the BANs.

Category to Highlight

MIN([Category]) = WINDOW_MAX(IF [Rank to Highlight] THEN MIN([Category]) END)

Breaking this down: If the row is the rank we want to highlight, then return the Category associated to this row, and ‘spread’ it across all other rows (via the WINDOW_MAX function). If the Category for the row, matches this value, then return True.

To see how this is working, add the field the the Tooltip shelf of the table of data, and set the table calculations to compute by Category and Sub-Category (note there are two nested calculations within this calculation and both need setting).

If you hover over any row that has the same Category as the row that is highlighted, the Category to Highlight value will be True, but hovering over rows in the other Categories, will return False.

Building the Bar Chart

On a new sheet add Category to Columns, Sales to Columns and Sub-Category to Detail. Then add Rank By Category as a blue discrete pill to Rows. Verify the table calculation is set to compute by Sub-Category only.

Add Order Date to Filter and restrict to the latest year. Add Index to Include to Filter and set to True.

Double click into the Columns shelf and manually type MIN(0) to create another axis.

Change the mark of the MIN(0) marks card to be Gantt Bar and set the Size to be as small as possible. Add Sub-Category and Sales to the Label shelf of the MIN(0) marks card, and adjust the label so the text is arranged as required. Add Measure Names to the Rows shelf to create a row per Measure.

Make the chart Dual Axis and Synchronise the axis. Change the mark type of the Sales marks card back to a bar, and remove Measure Names from the All marks card.

Add Number to the Pages shelf, then add Rank to Highlight to the Colour shelf of the All marks card. Adjust the table calculation so it is computing by both Category and Sub-Category, then adjust the colours to suit.

Edit the Label of the MIN(0) marks card, so the font is bigger/bolder (I used Tableau Medium Bold 10pt) and set the font to match mark colour.

Hide the MIN(0) top axis, the Category headings and the Measure Names column (uncheck show header). Edit the Sales axis: remove the title, fix the axis from 0 to 199,000, and fix the axis ticks every 100,000. Format the Sales axis, so the tick marks are labelled in $k format. Format the Rank by Category column so the text is bolder and larger (I used Tableau Medium 12pt). Hide the column label (hide field labels for rows), and make the column narrower

Format the sheet, so the background is grey, remove all row dividers, set the column dividers to be thicker, to not apply to the header, and to be at Level 2

Adjust the colour of the column grid lines so they show up on the grey background. Adjust the formatting of the Sales axis labels. Remove all tooltips. Scroll through the page control and verify the highlighted dimensions change.

Building the BANs

On a new sheet, add Category to Columns and Sub-Category to rows. Add Order Date to Filter and restrict to the latest year. Add Index to Include to filter and set to True. Create a new field

Sales per Category

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

format to $ with 0dp and add to Text.

Add Rank By Category as a blue discrete pill to the Rows shelf, and move Sub-Category to Detail. Verify the Rank by Category table calculation is set to compute by Sub-Category only.

Add Number to the Pages shelf, and then add Category to Highlight to the Colour shelf. Verify both the nested table calculations are set to compute by Category and Sub-Category only, then adjust the colour accordingly.

Move the Rank by Category field from the Rows shelf to the Filter shelf and set to filter to 1, so only the first row displays. Add Category to the Text shelf, and adjust the text positioning and size as required, and align centrally. If you wish, change the mark type to Shape and set to use a transparent shape. (this just gives a nicer user experience when clicking/hovering on the text – see this post for information). If you do this, you’ll need to adjust the Label to match mark colour. Hide the Category header, remove row dividers and format the background to be light grey. Remove the tooltip and update the title.

Building the Summary Ranking

While the requirements suggested the viz should be built using 2 sheets, I couldn’t manage this (I was hoping to use the Caption feature on the bar chart, but as I used table calculations, the values wouldn’t retain). So I built this section with a 3rd sheet, and checking the solution after posting, found that it also had 3 sheets.

On a new sheet, add Order Date to Filter and set to the latest year, and add Index to Include to filter and set to True. Add Sub-Category to Detail, Number to the Pages shelf and then add Rank to Highlight to Filter and set to True. Verify the table calculation is set to compute by Sub-Category only, and if adjusted, recheck the filter is set to just True. You should have 1 mark displayed, and on hover, it should list the Sub-Category with the rank that matches the number of the page.

Add Overall Rank (set to compute by Sub-Category only), Total Sub Cats and Sub-Category to the Text shelf. Adjust the size and format of the text and align centrally. Again if preferred, change the mark to Shape and use a transparent shape.

Set the background to light grey, hide the tooltip, and verify the display changes as you cycle through the pages.

Now arrange all the sheets onto a dashboard using padding if required to provide some spacing.

My published viz is here.

Happy vizzin’!

Donna

Filter Challenge : How well do you know Tableau’s Order of Operations?

Donna ColesLeave a comment

It was Erica Hughes’ turn to set the #WOW challenge this week. This ended up being a bit of a journey for me, because of one requirement – to use the max date of the dataset.

I typically use a FIXED LOD to determine the max date, which I did initially in this case, and after building a solution, tripped up when it came to adding some of the filters ‘to context’. The context filter meant my Max Date was changing depending on the filter and subsequently I wasn’t getting the right results for the ‘days since last purchase’.

So I ended up having to put my thinking cap back on, and after a lot of trial and error and reading on the internet (including this article by the Flerlage Twins) , I finally managed to get a solution that involved both LOD calculations (including a rarely used INCLUDE LOD) and Table calculations, and no context filters at all. This was a pretty complex solution. The table of data had to include the Order Date on the Detail shelf, resulting in multiple rows per customer showing, which meant I had to then use an INDEX()=1 filter to only show 1 row for each customer. I then used an additional INDEX() against each customer, so I could filter to show only the customers in position 1-10. My solution to this is published here.

After publishing, I checked Erica’s solution and found how she’d handled the max date requirement. It used a concept I haven’t had to use so far, so I decided to rebuild a less complex solution, which is what I will now blog about.

Capturing the max date of data set which isn’t affected by context filters

The usual way to define the maximum date in the data set is to create a FIXED LOD calculation, and this is still required

Max Date

{FIXED :MAX([Order Date])}

This returns 30 Dec 2021.

The problem is, once a context filter is applied to a sheet, the value of this field can change. For example, if Region is applied as a context filter and set to ‘South’, then what Tableau will do, based on the ‘order of operations’, is first filter all the data to just those records where Region=South. It will then work out the max date of these filtered records, and if there are no orders on 30 Dec 2021 for the South Region, then the value of the Max Date field will differ. This is because context filters get applied before FIXED LOD calculations. We need a way to ensure we can retain the 30 Dec 2021 without hardcoding it.

The solution is to use a parameter.

pMaxDate

A date parameter which is set to use the value of the Max Date field when the workbook is opened.

This is quite a sneaky but clever way to retain this, which is why I’m reworking my solution to use it, so I have something for future reference myself. When the workbook opens, the pMaxDate parameter will get set to 30 Dec 2021 and won’t ever change, whereas the Max Date field will change if context filters are used.

Building the Table

It may seem a bit odd, but I’m going to start with the table of data that’s displayed, as this requires the most calculations, and the most validation.

On a new sheet, add Customer Name, Customer ID to Rows and Sales onto Text. Order by Sales desc. Format Sales to $ with 0dp.

We need to determine the latest order date for each customer in order to work out other information.

Latest Order Date

{FIXED [Customer ID] : MAX([Order Date])}

This finds the maximum Order Date for each Customer.

And with this, we can now work out

Days Since Last Purchase

DATEDIFF(‘day’, [Latest Order Date], [pMaxDate]

Note this is comparing the Latest Order Date to the pMaxDate parameter instead of Max Date.

Format this to custom number, with 0 dp and a suffix of ‘ days’.

Add Latest Order Date to Rows (exact date, discrete) and Days Since Last Purchase into the table.

Now we need to work out

Latest Order Amount

{FIXED [Customer ID]: SUM(IF [Order Date]=[Latest Order Date] THEN [Sales] END)}

For each customer, if the Order Date is the same as the Latest Order Date, then retrieve the Sales value.

Format this to $ with 0 dp and add to the view.

Now add Customer ID to the Filter shelf and set to filter by the Top 10 based on Sales.

To validate the behaviour of the calculations add Region to the Filter shelf and select ‘South’. You’ll notice only 8 rows are shown, and not 10.

This is because the data has been filtered based on the top 10 customers with the most sales first, and then restricted those top 10, to those with sales in the South. Only 8 of the top 10 customers have sales in the South, hence the 8 rows.

To resolve this, we need to add Region to Context (right click on the filter and Add to Context . This has the effect of filtering all the data by Region = South first, and then displaying the top 10 customers by Sales. We now have 10 rows displayed.

Add State to the Filters shelf, and add that to context too. Test the table of results by selecting different combinations of regions and/or states and comparing to the results on Erica’s solution to verify all the calculations are behaving as expected.

Now we’re happy the table is displaying the data as expected, we can format it and make it ready for the dashboard :

  • Remove Latest Order Date & Customer ID
  • Set the Row Banding
  • Set the Row Dividers to Level 0, so only row lines appear at top and bottom
  • Remove Column Dividers
  • Format all text (row/column headings & text data) to be 8pt.
  • Alias Sales to be Total Sales (right click on the Sales title in the columns and Edit Alias)
  • Remove Region & State from the Filter shelf. These will get re-added later.

Building the Map

On a new sheet, double click State to load a map (you may need to set your location to United States : Map menu -> Edit Locations).

Change mark type to a Filled Map, add Region to Colour and adjust colours.

Remove all map layers (Map menu -> Map Layers and uncheck all options listed on left hand side). Change border on Colour shelf to white.

Remove row & column dividers, and remove all map options (Map menu -> Map Options and uncheck all options).

Drag a new instance of State onto the canvas and Add a Marks Layer. This will create a 2nd marks card on the left. Change the colour of the circles to black. Add Sales to the Size shelf and adjust. Adjust the tooltip of both marks card (you’ll need to add Sales to the Tooltip on the map marks card).

Building the Legend

On a new sheet add Region to Columns and type in MIN(1) into the Columns shelf too. Add Region to the Label shelf, and the Colour shelf. Adjust the height of the rows, so you can see the text.

Edit the axis so it is fixed from 0 to 1. Then format the Label so the font is larger and centred.

Hide the axis and the Region (uncheck show header). Remove row & column dividers and any gridlines. Adjust the border on the Colour shelf to be white. Set the tooltip not to show.

Building the Bar Chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Order by Sales desc. Add Sub-Category to the Filter shelf and set to show Top 10 by Sales. Reduce the Size of the bars, and align the row labels to be left aligned, and the font to be 8.

Format the axis, so the values are displayed in $K.

Hide the row label, and adjust Tooltip. You may need to create an additional calculated field based on Sales to add to the Tooltip which you can then format to $ with 0dp.

Adding the interactivity & context filters

Add the sheets onto a dashboard. You’ll need to use a mixture of vertical & horizontal layout containers, inner and outer padding and background colours to get the required layout.

Add a dashboard filter action which runs on Select when the Legend sheet is selected, which affects all other sheets and shows all values when unselected.

Add another filter dashboard action, which this time runs on Select when the Map sheet is selected, which affects all sheets except the Legend sheet, and also shows all values when unselected.

Now click on one of the regions in the legend, then click on a state in the map. This has the effect of adding filters to the shelves on the other sheets. Navigate to the Table sheet, and add the 2 ‘Action’ filters to context

Do the same for the Bar char sheet.

And you should now have a completed viz. My published version based on this solution is here.

Happy vizzin’!

Donna

Tableau Website Analytics

Donna ColesLeave a comment

This week’s #WOW2021 challenge by Ann Jackson is focussed on dashboard design/layout, reference lines and formatting time, so that will be the focus of this blog too. I’m hoping it might be a fairly short post this week 🙂

  • Filtering the data
  • Creating the key measures, formatting time, adding average lines
  • Dashboard layout

Filtering the data

The data Ann provided is based on the Google Analytics data the team have harnessed related to the activity on the #WOW website. It’s a static data set, which contains data from 29 Dec 2019 up to 12 April 2021, but Ann states her solution only includes data up to 10 April 2021. I therefore added the Date field to the Filter shelf on the sheet and set it to end on 10 April 2021

Knowing I’d be building multiple sheets for this challenge, I set this filter to be a global filter by setting it to apply to worksheets – > all using this data source

Creating the key measures, formatting time, adding average lines

For this challenge, we’ll be creating a sheet for each BAN, and a sheet for each trend line depicting a measure by week, so 8 sheets in total.

For the Sessions measure, both the BAN and the trend chart are straightforward.

The BAN just shows SUM([Sessions]) on the Text shelf, appropriately formatted. The other BAN sheets are pretty much the same, but just show the appropriate measure in the appropriate colour.

The trend line displays Week([Date]) by SUM([Sessions]), with the average line added by dragging Average Line from the Analytics pane onto the chart and then formatting.

If the numbers don’t quite match up, it may be because your week is set to start on a different day. By default as I’m UK based, dates are set to start their week on a Monday. For this challenge to match Ann’s solution, your week needs to start on a Sunday. You can set this by right clicking on the data source itself and changing via the Date Properties option

To determine the average session duration, we need to build a calculated field, that then needs to be formatted to show minutes and seconds.

Average Session Duration

(SUM([Session Duration]) / SUM([Sessions]))/86400

Session Duration / Sessions will return a value in seconds. To be able to format this in the way required, we need to get the number of seconds as a proportion of a day. There are 86400 seconds in a day (60 * 60 * 24), so we divide by this.

We can then use a custom format on this field and use the nn:ss notation. NOTE not mm:ss. If you needed to format this as hours:minutes:seconds, the format would be hh:mm:ss, but mm:ss does not provide you with the right values. This video demonstrates all this, if you’re interested.

When it comes to building the trend line for this measure, the average line, can’t be added as simply in the way it could for the Sessions trend chart (at least I couldn’t get it to work that way…).

We need to build a calculated field that will show the same overall average value alongside the weekly averages. This value needs to match what’s displayed in the BAN chart.

Overall Session Duration mm:ss

{FIXED : [Avg Session Duration]}

This is returning the Avg Session Duration based on all the rows in the data. As the Date field has been added as a global filter, it is acting like a data source filter, so the dates we don’t want have been excluded from the data set that the FIXED LoD is being applied against. If the Date filter was a simple ‘quick filter’, this calculation wouldn’t work, as the data for the 11th & 12th April would also be included in the calculation.

Format this to nn:ss as well. Add this field to the Detail shelf of the trend chart, then right click on the Average Session Duration axis and Add Reference Line, and reference the Overall Session Duration mm:ss field.

For the bounce rate, we simply need

Bounce Rate

SUM([Bounces])/SUM([Sessions])

which is formatted to a percentage of 1 dp.

When building the trend line, I added the average line from the analytics pane, but that gave me a different value from my BAN. So I built

Overall Bounce Rate

{FIXED : [Bounce Rate]}

This was formatted to % 1dp, and added as a reference line as described above.

Finally the last measure, we need

Avg Time on Page

(SUM([Time on Page])/(SUM([Pageviews])- SUM([Exits])))/86400

formatted to nn:ss

and

Overall Time on Page mm:ss

{FIXED : [Avg Time on Page]}

and again formatted to nn:ss.

The charts for these are built exactly like the Average Session Duration.

Dashboard Layout

The easiest way to describe the layout I built is to show it 🙂 Note the Item Hierarchy on the left hand side of the image below.

I have a vertical container as the Main page.

The first row in this container is the title in a Text object.

The second row is a Blank object and is the yellow line. The background of the blank object is set to the relevant yellow, the outer padding is set to 0 all round, and then the height is set to 4. This gives the appearance of a thick coloured line.

The third row is another vertical container, and I’ve done this, so I can ultimately use the option to Distribute Contents Evenly on the container to ensure the horizontal container ‘rows’, which I’ll be adding into this container, are evenly spaced.

So ‘within’ the 3rd row, the 4th-7th rows are managed using a horizontal container, which in turn contains a blank object (the coloured vertical line), the BAN sheet and the trend sheet. Around each horizontal container I set the outer padding to 10 all round to give some spacing. The blank object in each ‘row’ is given the relevant background colour and set to a width of 15.

Finally, I finished off with an additional horizontal container at the bottom which is where I added my standard #WOW footer. Note this horizontal container is essentially the 4th row of the original Main vertical container though.

Hopefully I’ve provided enough for you to build the challenge yourself / resolve any issues you might have. If there’s anything I’ve missed, do please comment to let me know.

My published viz is here. Please note, that for some reason (and I don’t know why), Tableau Public does not seem to display my time axis properly (just shows 0). As the workbook renders on Public, the values on the axis show, but once fully loaded, they change. If you download the workbook, the values are fine. I published from Tableau Desktop 2021.1.0. I’m putting this down to an issue with Tableau Public.

Happy vizzin’! Stay Safe!

Donna

Can you create a chart with a dynamic week start?

Donna ColesLeave a comment

This week Ann set the challenge and suggested she had ‘distilled it down a bit’ so it wouldn’t take as long to solve….hmmmm… not sure if that really worked out for me…. I certainly managed to crack through it to begin with, but the requirement to ‘show the data when there were missing values’ seemed to end up being trickier than I thought it would be, and the trying to display a date on the tooltip for those days…. well, that was just so freakin’ hard! Given this was ‘distilled down’, I thought I must have been missing something pretty obvious to be struggling so much. I played round with so many different table calcs to try to get this to work, but ended up admitting defeat and peaked at Ann’s solution.

The missing dates bit… that ended up being a bit odd. I rebuilt my chart multiple times, starting with a table of data as a ‘check’ and then carefully converting that into the required Viz. Eventually it just seemed to work, which left me very puzzled, as I was pretty certain what I ended up with, I’d had previously… I can only assume there is something about the order of the steps I took that may have caused the problem.

As I write this blog out, I’ll be rebuilding as I go, to generate the various screen shots, so fingers crossed, if you follow step by step, you’ll get a working solution 🙂

Right let’s get onto it…

Building out the data we need

As I’ve done before, I’m going to build up the fields I need into a tabular format, so I can ratify I’ve got everything correct before I build up the viz.

First off, we need a couple of parameters to drive the inputs for the chart :

Week Ending On

A date parameter set to 24th Oct 2019, that allows all values

Include X Prior Weeks

an integer parameter set to 10 by default

From the requirements, we need to display data for the 7 days of the week up to the Week Ending On date, along with data for each of the 7 days in the previous number of weeks as stated in the Include X Prior Weeks parameter.

So first up we need to work out when the ‘latest’ week starts

Start of Selected Week

DATEADD(‘day’,-6,[1a.Week Ending On])

Then I want to group all the Order Dates into their respective weeks, where each week needs to start on the same day of the week as the Start of Selected Week field. eg if Start of Selected Week is Friday 18th Oct, then I want all the dates of the previous x weeks to be grouped to the Friday of the week they’re in. So all the dates from Friday 11th Oct to Thurs 17th Oct are grouped as Fri 11th Oct, dates from Friday 4th Oct to Thurs 10th Oct are grouped as Fri 4th Oct, and so on.

Now the ideal way I think I should be able to do this is use a DATETRUNC function passing in the name of the day of the week, the week should start, which is based on day associated to the Start of Selected Week field. So I wanted to write something like this:

DATETRUNC(‘week’, [Order Date],DATENAME(‘weekday’,[Start of Selected Week]))

but Tableau doesn’t like the fact the 3rd parameter is a calculation rather than simply the text ‘Friday’.

So instead I had to build up the logic as follows

Order Date Week

CASE DATEPART(‘weekday’, [Start of Selected Week])
WHEN 1 THEN DATETRUNC(‘week’, [Order Date], ‘sunday’)
WHEN 2 THEN DATETRUNC(‘week’, [Order Date], ‘monday’)
WHEN 3 THEN DATETRUNC(‘week’, [Order Date], ‘tuesday’)
WHEN 4 THEN DATETRUNC(‘week’, [Order Date], ‘wednesday’)
WHEN 5 THEN DATETRUNC(‘week’, [Order Date], ‘thursday’)
WHEN 6 THEN DATETRUNC(‘week’, [Order Date], ‘friday’)
WHEN 7 THEN DATETRUNC(‘week’, [Order Date], ‘saturday’)
END

This Order Date Week is essentially the field that represents each line on the final viz.

I also need a field that I can used to represent the x-axis on the final viz, which is labelled based on the days of the week, but (as per the challenge), is starting at a different day.

To do this, I chose to ‘baseline’ all the dates in each week, to be aligned to the ‘current’ week. What I mean by this is that I want to create a field whereby if the Order Date is a Friday, the field will store the date of the Friday of the ‘latest’ week, in this case Friday 18th Oct. Any Order Date on a Saturday will be ‘aligned’ to Saturday 19th Oct and so on.

Order Date Baseline

DATE(DATEADD(‘day’, 7 * DATEDIFF(‘week’,[Order Date Week],[Start of Selected Week]), [Order Date]))

This finds the number of weeks between the week the order date is in and the start of the ‘latest’ week, which is then multiplied by 7 to convert to a days difference, which is then added onto the Order Date itself.

So from below, you can see both Fri 13th Sept & Fri 20th Sept map to 18th Oct; Tue 17th Sept and Tue 24th Sept both map to 22nd Oct etc.

I now need another field to restrict the dates being displayed to those within the timeframe dictated by the Include X Prior Weeks parameter.

Dates to Include

[Order Date]>=DATEADD(‘week’,-1 * [Include X Prior Weeks], [Start of Selected Week])
AND [Order Date] <= [Week Ending On]

So let’s start building the check table of data as follows:

  • Order Date Week on Rows as discrete, exact date
  • Order Date Baseline on Columns as discrete, exact date
  • Dates To Include on Filter shelf, set to True
  • Sales on Text

You can see we have some gaps where there are no Sales, we need these to display 0, which we do using

Inc Null Sales

IFNULL(LOOKUP(SUM([Sales]),0),0)

This is a simple table calc that is ‘looking up’ its own value, and if it’s null, sets itself to 0 otherwise uses it’s own value.

So let’s put this on the Text shelf instead

We’re also going to need a field to define the ‘latest week’, as this is coloured differently on the viz

Is Latest Week

[Order Date Week]=[Start of Selected Week]

Bring this into the table

We’ve now got the core things we need to build the majority of the chart.

Building the chart

Firstly, duplicate the table above, then move the pills round as follows:

  • Move Is Latest Week to Colour shelf, and adjust colours accordingly
  • Move Order Date Week to Detail shelf
  • Move Inc Null Sales to Columns shelf
  • Swap the Colour Legend so True is first (makes the latest line to the front)
  • Format the Order Date Baseline field to custom formatting of dddd which will just the display the day of the week rather than the full date (sneaky huh?)

Then tidy up the formatting to remove gridlines, get the axis in the right format, change the font of the axis labels etc.

And that’s the main viz… now we need to sort the tooltips.

Tooltips

On the tooltip we need to display

  • The order date
  • The sales value or ‘no sales’ if there are no sales
  • The start and end day of the week

Let’s start backwards.

Day of Week Start

DATENAME(‘weekday’,[Start of Selected Week])

Day of Week End

DATENAME(‘weekday’, [Week Ending On])

Put these on the Detail shelf (as we’re going to need them for the title too).

Add the Sales onto Tooltip and format it to $ with 0dp. This will display the Sales value when there is one, and ‘nothing’ if there are no sales (as opposed to the 0 that the Inc Null Sales field will show.

To get the ‘no sales’ text we need

Tooltip: No Sales

IF IFNULL(LOOKUP(SUM([Sales]),0),0)=0 THEN ‘no sales’ END

Note – this is what I did and is in my published solution, but I could have just checked whether Inc Null Sales = 0 – doh!

Add this onto the Tooltip and position directly adjacent to the SUM([Sales]) field, so either one will be displayed (they can never both display).

Right, now we need to get the date.

The problem we have is that like the no sales, if there’s no orders on a date there’s no date to display either, but unlike the ‘no sales’ value, we haven’t got a single default value like 0 to use. Instead we need to work out what the missing date should be.

I tried various things for this, but finally had to look at Ann’s solution to get this, which is :

Tooltip: Order Date

IFNULL(ATTR([Order Date]),
IFNULL(DATEADD(‘day’,1,LOOKUP(ATTR([Order Date]),-1)),DATEADD(‘day’,-1,LOOKUP(ATTR([Order Date]),1))))

Urgh! Horrid right!… so what is this saying…

  • If the current order date doesn’t exist, then
  • lookup the previous order date and add 1 day to it, but if that is also null then
  • lookup the next order date and take 1 day off it

So this is only working on the assumption that there are not 2 days in a row with no orders.

Add this to the Tooltip and format accordingly to get the layout required.

Finally amend the sheet title to match the title displayed. The title can reference the Day of Week Start and Day of Week End fields along with the Week Ending On and the Include X Prior Weeks parameters.

When you then add onto the dashboard, make the parameters floating and position them top right.

Hopefully, you’ve ended up with a working solution. I know I approached mine slightly differently from Ann’s, so it’s worth checking hers out too.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna