[ This is a guest post by Hans Romeijn. This is a followup post to my recent post on calculating YTD/YoY, QTD/QoQ, MTD/MoM and WTD/WoW. Hans shows a practitioner's approach to the calculation with performance in mind. The key is to replace conditionals by multiplications with pre-calculated indicator 1 or Null. This can accelerate the calculations significantly, especially when the data set is big.

The companion workbook courtesy by Hans is available for download.

Good implementation and fast speed do matter greatly for business users. ]

---

Recently a post from Tableau Zen Master Alexander Mou showed up in my timeline on LinkedIn. The post was about Simple calculations for YTD/YoY and other timeframes in Tableau and he ended the post with an invite to comment.

As this is something I work with on a daily basis I got curious and followed the link to his blog. It was a very clear step by step explanation. What I noticed that initially the conditions to calculate the timeframes are in the Sales calculation itself. My comment on Alexander’s post was therefore to separate both and work with a 1/NULL indicator.

There are many ways to work with time period based calculations. The way I always set up these calculations is the following:

1.     First I create condition based period calculations for each time frame the business wants to look at. The output of these calculations is either 1 or NULL

2.     I create a parameter to be able to select the necessary time frame

3.     Create two indicator fields (Current Year & Last Year) based on the parameter

4.     Create the actual sales calculations where sales is multiplied with the indicators

Setting it up like this gives me a couple of advantages, such as:

·       All period based calculations can be re-used for other purposes then Sales, e.g. Quantity, Margin, etc.

·       Any changes or bugs can be fixed in one place: the concerning time indicator

·       As the final (Sales) calculation is based on multiplying, there’s minimum impact on performance.

As all data sources and models I worked with have a calendar table, it is even better to actually calculate these indicators in your backend. When working with very big data sets this is definitely preferred.

Working with 1/NULL indicators is not limited to time only. When you actually get the hang of working with indicators, you can use them for all kinds of purposes. I apply them with different kind off conditional calculations e.g. show results for commodity products or certain customer groups only. This way I can avoid using too many filters and I can also use the indicators in LoD calculations.

For now, I will describe how to set up time indicators calculated in Tableau.

Step 1 Condition based indicators:

For each time frame we’re going to create a separate indicator. Below the list of Timeframes I commonly use, including the calculations:

·       YTD (Year to Date)

oCCY: IF DATEDIFF('year',[Order Date],TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END

oLCY: IF DATEDIFF('year',[Order Date], TODAY())=1 AND [Order Date] <= DATEADD('year',-1, TODAY()) THEN 1 END

·       YTM (Year to Month)

oCCY: IF DATEDIFF('year',[Order Date], TODAY())=0 AND DATEDIFF('month',[Order Date], TODAY())>0 THEN 1 END

oLCY: IF DATEDIFF('year',[Order Date], TODAY())=2 AND DATEDIFF('month',[Order Date], TODAY())>24 THEN 1 END

·       YTQ (Year to Quarter)

oCCY: IF DATEDIFF('year',[Order Date], TODAY())=0 AND DATEDIFF('quarter',[Order Date], TODAY())>0 THEN 1 END

oLCY: IF DATEDIFF('year',[Order Date], TODAY())=1 AND DATEDIFF('quarter',[Order Date], TODAY())>4 THEN 1 END

·       QTD (Quarter to Date)

oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END

oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=4 AND [Order Date] <= DATEADD('year',-1 , TODAY()) THEN 1 END

·       QTM (Quarter to Month)

oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=0 AND DATEDIFF('month',[Order Date], TODAY())>0 THEN 1 END

oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=4 AND DATEDIFF('month',[Order Date], TODAY())>12 THEN 1 END

·       MTD (Month to Date)

oCCY: IF DATEDIFF('month',[Order Date], TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END

oLCY: IF DATEDIFF('month',[Order Date], TODAY())=12 AND [Order Date] <= DATEADD('year',-1, TODAY()) THEN 1 END

·       L12M (Last 12 Months)

oCCY: IF DATEDIFF('month',[Order Date], TODAY())>=1 AND DATEDIFF('month',[Order Date], TODAY())<=12 THEN 1 END

oLCY: IF DATEDIFF('month',[Order Date], TODAY())>=13 AND DATEDIFF('month',[Order Date], TODAY())<=24 THEN 1 END

·       LQ (Last Quarter)

oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=1 THEN 1 END

oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=5 THEN 1 END

·       LM (Last Month)

oCCY: IF DATEDIFF('month',[Order Date], TODAY())=1 THEN 1 END

oLCY: IF DATEDIFF('month',[Order Date], TODAY())=13 THEN 1 END

Important! Make sure to convert all indicator calculations to Dimension.

I use the following abbreviations:

·       CCY: Current Calendar Year

·       LCY: Last Calendar Year

·       PCY: Prior Calendar year

·       CFY: Current Fiscal Year

·       LFY: Last Fiscal Year

·       PFY: Prior Fiscal Year

Step 2 Create a parameter

For this example I’ve created a string based parameter with all the possible timeframes:

Step 3 Create the parameter based indicator fields

All the options from the parameter need to be brought back to 1 calculation for each year. This calculation looks like this:

 Ind Period CCY: 

WHEN 'YTD'  THEN [Ind YTD CCY]

WHEN 'YTM'  THEN [Ind YTM CCY]

WHEN 'YTQ'  THEN [Ind YTQ CCY]

WHEN 'QTD'  THEN [Ind QTD CCY]

WHEN 'QTM'  THEN [Ind QTM CCY]

WHEN 'MTD'  THEN [Ind MTD CCY]

WHEN 'L12M' THEN [Ind L12M CCY]

WHEN 'LQ'   THEN [Ind LQ CCY]

WHEN 'LM'   THEN [Ind LM CCY]

END

 Ind Period LCY:

CASE [Period Selection]

WHEN 'YTD'  THEN [Ind YTD LCY]

WHEN 'YTM'  THEN [Ind YTM LCY]

WHEN 'YTQ'  THEN [Ind YTQ LCY]

WHEN 'QTD'  THEN [Ind QTD LCY]

WHEN 'QTM'  THEN [Ind QTM LCY]

WHEN 'MTD'  THEN [Ind MTD LCY]

WHEN 'L12M' THEN [Ind L12M LCY]

WHEN 'LQ'   THEN [Ind LQ LCY]

WHEN 'LM'   THEN [Ind LM LCY]

END

Important! Also these calculations should be converted to Dimension.

Step 4 Create the Sales calculations

The final Sales calculations will look like this

·       Sales Period CCY:  SUM([Sales] * [Ind Period CCY])

·       Sales Period LCY:  SUM([Sales] * [Ind Period LCY])

To make this all properly work when applying with e.g. months, it’s best you create at least two additional calculations:

1.     For sorting purpose:
Date Sort Month: DATEDIFF('month',[Order Date],TODAY())

2.     For Filtering purpose, that only the concerning Months are shown:
Ind Period Filter: ZN([Ind Period CCY])+ ZN([Ind Period LCY])

A basic Year over Year comparison could look like this:

With the Period Parameter you can now easily change the timeframe and evaluate results.

Feel free to leave comments. You can contact Hans Romejin for questions.

Click the image to download the companion workbook and the table of indicators that you may use in your workbook.

I was asked a question: How to find out the IDs that showed up consecutively 5 times during the last 14 days?

1. Solution with Running Sum

2. Solution with Moving Window Sum

In corporate finance, bridge chart is often used to visualize itemized sales/revenue performance during a particular period, such as a quarter or a year.

Bridge chart can be designed using waterfall chart. But we will use a different approach. Here we add itemized AOPs to the mix so that we can visualize the performance against goals for each item.

AOP means annual operation plan which is equivalent to the target in annual financial planning, usually on a quarterly basis.

Another acronym that is going to be used in this post is ARR, annual recurring revenue. It is often regarding the revenue on a quarterly basis and for each item.

The Data Set

We made up a data set for an example, which is very simple. The BeginningARR is the revenue of the previous period or the previous quarter.

We do not need to specify the Ending ARR and AOP because both can be derived from the itemized numbers in the table.

Creating the Bridge Chart

First let's create the chart with ARR values. Because we are going to overlap the ARR values with AOP values, we won't use the waterfall chart approach where the bars are downwardly drawn. 

We will first create a gantt bar chart based on the following values:

• Running_Sum() of ARR values are the cumulative result of ARR leading to total ARR.
• Lookup() with offset -1 will shift the cumulative result to the right by one position.
• ZN() will fill the first position with 0 after the shift towards right.
• MIN(Item)=MAX(Item) is the condition for each item and ELSE is for the total which we force it to be 0 in this case.

For the ARRs, we use Sum(Value) as Size. For the AOPs we use Sum(AOP) as Size. These are all logical following the gantt bar setup. In contrast, in waterfall chart, we have to use negative values for sizes.

A few details

For the AOP label, we don't need it for the BeginningARR because there is none. So the calculation is as follows:

It defines the total and the value per item except the first one (BeginningARR). Note that in the formatting, its prefix is '/ $'.

Click to set a reference point

Hover over a point of interest

Highlight both points with bigger size and different colors

Reset the reference point with a reset button

After we are done with the business, we should not leave the reference point remain highlighted  Thus we created a reset button sheet for resetting the highlighted reference point. First, we need to create 3 values for the 3 reference attributes to be reset with.

For example, the Rest Date is 1/1/1900. The Reset Sales is reset to 0. The Reset Sub Category is None. 

We need 3 parameter actions from the reset button sheet to reset the 3 attributes.

De-select the reference point and the reset button

In a Tableau dashboard, when we select/click a data mark, such as a reference point or the reset button in our case, it will stay selected until we click somewhere else. This is not good for us because we may not click anywhere soon. So we will apply a de-select action right away to the sheet in question. 

We need to create two fields with True and False each as values. And place them in the Details of the sheets in question. 

Here is the de-select filter action for the reset button:

There you go. It is not a complex dashboard in principle. We just have quite a few steps to get it done. It's a good exercise for playing with parameter actions.

Feel free to download the companion workbook and play with it. Leave comment below or contact me at twitter @aleksoft if you have questions.

The show/hide buttons in containers and also in sheets allow us to create drill down functionalities in Tableau dashboards. Actually they make it simple to drill down in more ways than before.

Drill down with fixed sized containers

Here is an example. Given a simple bar chart by category.

Then with the click of a button, we can look into more details such Quarterly Profit and Regional Quarterly Sales.

In some of the earlier drill down techniques, we have to create a drill down with the same chart type or in the same hierarchy. Now we are no longer constrained by such limitations. We can display any kind of details as long as they are put in a container.

Steps to create a collapsible container:

1. Right click the dropdown menu at top right corner of a container.
2. Select "Add a show/hide button".
3. This creates a floating button by default. Uncheck the floating option to make it a tiled one.
4. Drag and place the button object right under the Category name. This requires a vertical container to contain the Category name and the button. Then this vertical container and the bar are placed in a horizontal container. (See the picture above) Check out the companion workbook for more details.
5. Edit the button to add images (see below) for opening/closing the container.
6. Add left paddings to the details container to shift the charts to the right, if you wish.
7. Add Max Sales per category as a reference line to normalize all the bars.

Repeat the process for the two other categories. Then we will have a dashboard with drill down functionalities. Below is the fully extended dashboard view.

Drill down with elastic charts

Here I am presenting how to design Sunburst Chart with practical considerations, such as:

• Labels inside
• Categorical sequential colors with dynamic data.

Turn off Map Background

Size the View 

• Use Ctrl-Shift-B/Ctrl-B to increase/decrease the size of the entire view (all layers included)
• Use the Size card to the max

Create Center

• Use the formula MAKEPOINT(0,0) to create a field called Center.

Create Subcategory Pie Layer

Drag the field Center to the view. Change the data mark to Pie. Drag the fields to the Mark's cards exactly as in the following screenshot.

1. Size the view with Ctrl-Shift-B as big as you can. 
2. Place Category above Subcategory.
3. Sort either of them by the field Sales descendingly.
4. Place Sum(Sales) in Color. Edit Color using deep gray to create sequential color.
5. Click Color card and set the color opacity to be ~55%

Create Category Pie Layer

Drag the field Center to the view and create a new layer. Change the data mark to Pie. Drag the fields to the Mark's cards exactly as in the following screenshot.

1. Size the pie with the Size card and make it smaller than the Subcategory pie. Leave enough spaces for labels. 
2. Place Category above Subcategory.
3. Sort either of them by the field Sales descendingly.
4. Place Category in Color and Subcategory in Label.
5. Set the Label font color to white (showing labels in black for illustrative purpose only )

Create White Circle Layer

Drag the field Center to the view and create a new layer. Change the data mark to Pie. Drag the fields to the Mark's cards exactly as in the following screenshot.

1. Size the pie with the Size card and make it smaller than the Category pie. Leave enough spaces for labels. 
2. Place Category in Label.
3. Sort Category by the field Sales descendingly.
4. Set Color to white. (showing black border for illustrative purpose only)
5. Set the Label font color to be white (showing labels in black for illustrative purpose only )

Create Background Layer

Put this layer under the Subcategory layer will generate categorical sequential colors. 

Drag the field Center to the view. Change the data mark to Pie. Drag the fields to the Mark's cards exactly as in the following screenshot.

1. Size the pie with the Size card to the Max. 
2. Sort Category by the field Sales descendingly.
3. Place Category in Color.

Put Layers in Order

The order of layers is as follows:

Drag the layers to arrange them in the above order.

Voila. It's done. You can download the demo workbook here.

If questions, leave comment or contact me via twitter @aleksoft

PS: Fine-Tuning the Labels (6-Layer Sunburst Chart)

In the above workbook, we included a 6-layer Sunburst chart as well. The idea is to allow us to fine tune the position of the labels.

By default Tableau places the labels very close to the exterior of the circle. We can manually move the labels individually to a different place. It's not good for dynamic data visualization.

To make the position of the labels tunable and predictable, we added a transparent pie layer. For example, we can insert a Transparent Pie layer between the White layer and the Category layer, and place the Category labels on the transparent pie. By tuning the size of the latter via the Size card, we can determine the location (radius) of the labels. 

To make a pie transparent, we only need to set a regular pie's color opacity to be 0%. Note that the transparent pie has exactly the same dimensions and measure as the one that it puts labels on. It is smaller in size (radius) and is transparent.

In the 6-layer dashboard, we inserted two transparent pies for two sets of labels: Category and Subcategory. 

You can see that the labels are placed a bit more away from the circles than the default placement by Tableau.

[ Followup guest post by Hans Romeijn: Calculating Period-To-Date/PoP with Indicators for Better Performance ]

Year to Date (YTD) and Year over Year (YoY) calculations are very important in business dashboards. Jim Dehner recently wrote a great post on the topic. It inspired me to present additional approaches for the issues.

Calculating YTD and YoY without LOD

We have been using YTD and YoY long before LOD (level of details) appeared. So we are not dependent on LOD at all. 

Here is how we calculate YTD, YTD Sales, Previous YTD, Previous YTD Sales and YoY Change %. Note that YTD and Previous YTD can be applied to any other measure's YTD calculations.

What is good about LOD?

With LOD, such as below, it provides portability when using the same value in different sheets with different dimensions. (Caveat: make your filters contextual when using LOD). If you use it in a single sheet, LOD is not a concern. In most cases, we don't need LOD.

Formatting YoY

When calculating YoY change % using the native quick table calculation "Percent Difference", we find that the first column is empty. Visually it's not an agreeable thing, although we perfectly understand why. With a little formatting, we can make the viewers feel better like below. 

Double click the green pill of Percent Difference and use ZN() to wrap around the formula. Then format the pill as follows.

This will put a dash "-" in the empty  column. If you wish, we can put a N/A there. 

Calculations for QTD/QoQ, MTD/MoM and WTD/WoW

Note that you can replace the date part 'year' in YTD/YoY calculations by  'quarter', 'month' or 'week' in both DATEDIFF() and DATEADD() for calculating QTD/QoQ, MTD/MoM and WTD/WoW. Here are the calculations for QTD, Previous QTD, QoQ Change % and respective sales. You can apply QTD and Previous QTD to calculating other measures.

Calculations for QoPYQ: QTD over Previous Year QTD

The condition for the previous year QTD is like 

We also provide calculations for PoPYP: PTD over Previous Year PTD. P is a parameterized period which can be quarter, month or week.

The companion workbook can be downloaded here.

Date Grain and Anchor Date as Parameters

PS. Tom T left a comment below saying that we can use a parameter for the date grain like year, quarter, month and week. That's very true. It will allow user to select a date grain. So here is the parameter Date Grain:

And here is the Period to Date calculation, Period being defined by the Date Grain.

Also Tom also suggested we can use a parameter for the anchor date instead of Today() as in Period to Date. So here is the parameter Date Select:

And here is the PTD Select Date calculation.

And Previous PTD, Period over Period Change % etc are included in the companion workbook. Check it out.

A viewer to my two button sheet swap video left a comment and asked me how to do single button sheet swap. Here is the solution.

It takes the following steps. Assume we have two worksheets built from two data sources. But they can be built from the same data source as well. 

Two filters with a parameter

First let's create a parameter [Sheet Select] with 1 and 2 as list of values.

Then for each sheet we set up a specific filter for it. For Sheet1 we have

[Sheet Select] = 1

For Sheet2 we have

[Sheet Select] = 2

Then place the filter in the filter shelf of the respective sheet and select the option True. By controlling the value of the parameter [Sheet Select], we can decide which sheet to show.

Creating a button sheet

Actually we duplicated the same sheet 2 more times and named the sheets for three different action types: Hover, Select, Menu. You can remove any of the sheets that you don't need.

This button sheet has a made-up data source with a single row of value 1 in a single column "Sheet". You can create this in an Excel or CSV file and import it into Tableau.

Then we need to create three new fields: True, False and Circular Value. And put them all the three in the Detail card. The Sheet field is a placeholder which allows an editable Label. We will put the button name in the label.

True and False fields have content exactly as their names.

The field Circular Value is created which will change to a value other than the current parameter [Sheet Select]. When the parameter is 1, the Circular Value is 2. Vice versa.

The components of the dashboard

The dashboard for sheet swap is composed of 3 button sheets and 2 chart sheets. You can always remove the buttons that you don't need.

The two chart sheets must be placed in a vertical container. In anytime, only one sheet is visible depending on the parameter value of [Sheet Select]. 

Caveat: Make sure that you hide the title of each sheet. Use area annotation for the title instead!! Otherwise, you can't hide the sheet.

Set up a Parameter Action

The purpose is to let the dashboard action assign a new value to the target parameter [Sheet Select]. The reference value is from that of the field Circular Value.

Here is the Parameter action set up of the Select button.

De-select the Select button

For the Select button, the regular select action actually will leave the button stay selected after clicking on it. We need a Filter dashboard action to de-select the button. This technique is invented by Simon Runc and Yuri Fal. Here is the setup.

Multi-sheet swapping via one button

The same technique can be applied to multiple sheet swapping via one button. The parameter [Sheet Select] has to have a list of 1 to N numbers. For the N sheets ready to swap, each needs to have a filter like [Sheet Select] =K where K=1,..., N.

For the Circular Value, the formula is as follows:

[Sheet Select]%N+1

By clicking the Select button, we can flip through all the N sheets in sequence. Same for running the other two button actions: Hover and Menu.

This topic came as a question from a colleague. I found the same question on Tableau community site which was unanswered. There is a similar post on the topic, a method first proposed by Zen Master Mike Cisneros. But it is about a single measure while the question is about two measures. So I am writing here a short answer to it. Note that this approach works for both single-measure and two-measure cases. That is, split the single one into two measures if necessary. 

Using the superstore data set, I created California Sales and New York Sales to emulate 2 different measures. Such as:

The key is to create the lower sales of the two: