Data + Science

[ad_1]


5/31/2018
Building a Calendar in Tableau with Discrete Events


This blog post is based on a recent visualization we created at the office. The goal was to visualize a list of automated tasks that occur periodically throughout a month. Some of these tasks happen daily, some weekly, bi-monthly and monthly. There are 14 total processes that trigger at different times during the month. We wanted a way to visualize this, in a calendar form, rather than just a list in Excel. Most calendar views in Tableau visualize some sort of Measure. For example, this tutorial by Kevin Taylor demonstrates how to create a calendar with a Measure and check out this video by Data Science Central. However, in my scenario, I do not have any measures. I simply want to visualize a list of discrete events on the calendar as text. I will outline two methods to do this.

This is the final viz with some dummy events.

The data is very simple; there are two columns. “Action” is a list of events and “Date” which is a date field. These events occur every month, so we decided to visualize a single month, January 2018, so there are 31 days in the month. We don’t have any processes that trigger over the weekend, so I am just visualizing weekdays. Also, we have at least one event everyday. If this is not the case in your data, then simply add a “blank” event on the days where there is no event to visualize. Download the data here if you would like to follow along with the steps outlined below. You can also download the finished workbook here.

Building a Basic Calendar in Tableau

This is the easy part. Building a basic calendar is pretty straight forward using any date field.

   Move Date to Columns
   Right-Click the Date on Columns. Select More (from Date Part) and Select Weekdays

   Move Date to Rows
   Right-Click the Date on Rows. Select More (from Date Part) and Select Week Number

   Move Action to Color
   Select “Entire View” from the view dropdown on the menu bar

This is the basic calendar setup in Tableau. You’ll notice that instead of a measure on Color, we have the discrete events. The next step is to make them into a list. In this data set the weekends are listed as an event and so we’ll filter those out.

   Move Action to Filters and uncheck Weekends to filter them out.

I am going to demonstrate two different methods for building this calendar, so right-click on the current worksheet and duplicate.

Adding Discrete Events Using Shapes

One way to list the discrete events is to use shapes. To do this we need to create a custom shape, stack the shapes and size them accordingly. Note – if using this method then sizing the shapes is very important. Otherwise you will have too much space between the shapes or they will overlap on top of each other.

Step 1: Create, Import and Assign a Custom Shape

I used PowerPoint to create a long pill shape and added it to my Tableau Shapes folder. The key to this is getting the size right, so you may need to experiment with the height and width depending on the size of your sheet or dashboard.

   Create the desired shape in PowerPoint.
   Right-click and Save Picture as a PNG file in one of your shapes folders (…/Documents/My Tableau Repository/Shapes folder/Misc)
      or download the shape I created here.
   Select Shape from the dropdown menu on the Marks Card
   Click on Shape on the Marks Card and Select More Shapes
   Click the “Reload Shapes” button to load your new shape into Tableau
   Select the folder from the dropdown menu in the Shapes window.
   Assign the new custom shape and click OK

Step 2: Creating an Index of the Events

We want to have a shape for each event and we want to stack them on top of each other. To do this, we will use the index() function..

   Calculated Field Name: Index
   Formula: index()

   Calculated Field Name: Combined Date
   Formula: DATEPART(‘weekday’, [Date]) + DATEPART(‘week’, [Date])
   Move Combined Date from Measures to Dimensions

   Move Combined Field to Details
   Move Index to Rows
   Right-click Index on Rows and Select Edit Table Calculations
   Select “Specific Dimensions” and check the box for Combined Details and Action

   Double-click the y-axis and set the axis range as needed. I set it from 0 to 6 in this example, which will allow room for the day of the month label.
   Click Size and size the shapes as appropriate for the view
   Move Action to Label
   Click Label and set Alignment to Middle and Center and check the box to “Allow labels to overlap other marks”
   Click Label and set Font Color to white

You should now a view that looks something like this:

Step 2: Add Day of the Month Labels in the Upper Right-Hand Corner

   Double-click on the rows to enter an in-line formula and type avg(6)
   Right-click on the new field and Select “Dual Axis”
   Right-click on the secondary y-axis and Select “Synchronize Axis”
   Select the first Marks card Index() and remove Measure Names from Color
   Select the second Marks card AGG(avg(6)) and remove all of the pills on Color, Details and Label
   Move Date to Label
   Right-Click the Date on Label and Select Day (from Date Part)

   Click Label and set Alignment to Top and Right
   Click Color and set Opacity to 0%
   Click Size and set the slider to size the blank shape so the day of the month is in the top right-hand corner

You should now have a view that looks something like this:

Step 3: Formatting View for Calendar

   Right-click on Field Labels for Columns and Select Hide Field Labels for Columns
   Right-click on Week(Date) and uncheck Show Header
   Right-click on Y-Axis and uncheck Show Header
   Right-click on calendar area and select Format
   Select Lines and remove Grid Lines on Rows
   Select Borders and Click the Sheets tab and drag the Column Divider Level slider to the right
   Select Analysis from the top menu and select Table Layout and Advanced and uncheck the box “Show innermost level at the bottom of view when there is a vertical axis” and click Apply
   Assign Colors to the Actions as desired (optional)

If you would prefer the events to start at the top of the calendar instead of the bottom:
   Right-click Index on Rows and select “Show Header”
   Double-click the y-axis and select “Reversed” under Scale
   Set the axis scale to be fixed from -1 to 5
   Right-click Index on Rows and uncheck “Show Header”
   Double-click AGG(avg(6)) on the Rows and change the 6 to -1
   Right-click Action on the Index Marks card and select Sort and change Sort Order to Ascending
   Right click calendar area and select Format and select Lines and remove Zero Lines on Rows

Here is the final view using shapes:

Adding Discrete Events Using A Stacked Bar Chart

Another way to show the discrete events is to use a stacked bar chart. I prefer this method over the shapes because the sizing of the the shapes can be problematic based on the worksheet and dashboard sizing and custom shapes do not export in high resolution. We start with the same approach as the shapes, so use the worksheet that you duplicated after creating the basic calendar template.

Step 1: Creating a Stacked Bar Chart

We want to have a bar segment for each event and we want to stack them on top of each other. To do this, we will use the countd() function.

   Calculated Field Name: Count Distinct
   Formula: COUNTD([Action])

   Move Count Distinct to Rows
   On the Marks card, change the dropdown from Automatic is Bars
   Click Color and set Borders to white (or to match the background color)
   Move Action to Label
   Click Label and set Alignment to Middle and Center and check the box to “Allow labels to overlap other marks”
   Click Label and set Font Color to white
   Double-click on the rows to enter an in-line formula and type avg(5)
   Right-click on the new field and Select “Dual Axis”
   Right-click on the secondary y-axis and Select “Synchronize Axis”
   Select the first Marks card AGG(Count Distinct) and remove Measure Names from Color
   Select the second Marks card AGG(avg(5)) and remove all of the pills on Color, Details
   Move Date to Text
   Right-Click the Date on Label and Select Day (from Date Part)
   Click Color and set Opacity to 0% and Borders to None
   Click Label and set Alignment to Top and Right

You should now a view that looks something like this:

Step 2: Formatting View for Calendar

   Right-click on Field Labels for Columns and Select Hide Field Labels for Columns
   Right-click on Week(Date) and uncheck Show Header
   Right-click on Y-Axis and uncheck Show Header
   Right-click on calendar area and select Format
   Select Lines and remove Grid Lines on Rows
   Select Borders and Click the Sheets tab and drag the Column Divider Level slider to the right
   Select Analysis from the top menu and select Table Layout and Advanced and uncheck the box “Show innermost level at the bottom of view when there is a vertical axis” and click Apply
   Assign Colors to the Actions as desired (optional)

As with the shapes, if you would prefer the events to start at the top of the calendar instead of the bottom:
   Right-click AGG(Count Distinct) on Rows and select “Show Header”
   Double-click the y-axis and select “Reversed” under Scale
   Set the axis scale to be fixed from -1 to 4
   Right-click Index on Rows and uncheck “Show Header”
   Double-click AGG(avg(6)) on the Rows and change the 6 to -1
   Right-click Action on the AGG(Count Distinct) Marks card and select Sort and change Sort Order to Descending
   Right-click on the calendar area and select Format and select Lines and remove Zero Lines on Rows

The final version of the Automated Process Calendar:

I hope you find this information useful. If you have any questions feel free to email me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer

Follow on Twitter @HighVizAbility



Read More …

[ad_2]


Write a comment