## Tableau Prep – Scaffolding, solving the problem with visualizing subscriptions

by Joe Stokes

Scaffolding is a technique that allows us to visualize income from subscriptions. The problem with subscriptions in Tableau is that contracts can run for various lengths of time and also start and end at different times.

Take for example this data captured here:

Trying to visualize it in Tableau yields these results:

We see income from the start date of the contract, but Tableau has no way of understanding that this income continues for a number of months until it expires.

So, how can we deal this this issue?

First, head to the excel sheet and identify the longest contract length. In my example it is Callum’s contract of 72 months. Create a new sheet and add a column header of month number. Starting with 0, you want to create 72 rows (remember we started at 0), one month for each month in the contract – this is your scaffold sheet.

Load up Tableau prep and connect to the excel file. First, drag in the customers sheet and then the scaffold sheet.

The next step is to create a ‘dummy’ field on both sheets, this allows us to join the two sheets together and starts the process of creating one row for each month a customer is contracted.

For both sheets add a step and create a calculated field called ‘1’. The only content in the calculated field is 1, to add a column on both sheets with this number.

Next, add a join step and join the two sheets with the newly created ‘1’ field.

Next we will add a filter value on the join step with the condition ‘Month Number < Contracted Months’. This means that for each customer, the month number never exceeds the number of months the contract runs for. Note: we do not use ‘<=’ because we have included a month number of 0.

At this point, we have a row of data for each month of the contract length, for each customer.

Next, we will add a row that shows a date for each month the customer pays. This will allow us to create a chart in Tableau which shows the income for each month.

We will achieve this using a DATEADD function. Add a new step and create a calculated field.

This will take the start date of the contract and use the month number to add, at the month level, the number of months to the start date. The result gives us a true date for every month in the contracted period.

Great! Now we can use this data to create a graph to show monthly income for a company. The data will show when different contracts start and end throughout the entire date period of the dataset.

Joe Stokes

Style your KPIs - Displaying positive or negative change

Mon 09 Sep 2019

Dashboard Day 5 - Done and Dusted

Fri 23 Aug 2019

Dashboard Day 4 - Cheating on Tableau