Introduction

There is a technique that I frequently use in Tableau that I call “creating buckets”. Buckets represent categories that I create on the fly for a variety of data sets, for a variety of reasons. Buckets can be used instead of going into the original data sources to build additional fields to specify your groupings and they can augment, extend or replace the concept of groups or sets in Tableau. Some of the things I show using buckets can probably be done in sets or groups, but this “bucket” concept is something I have been using for six years and I like it because it gives me flexibility in my analysis.

The advantage of the bucket approach is that for large data sets, you don’t have to take the time to create the additional fields and you don’t have to carry the overhead of the additional fields. An advantage over using the Groups command in Tableau is that for buckets that have a lot of members in them, you don’t have to take the time to add each member to the group. Recent versions of Tableau have made creating groups easier than it used to be, but I was using buckets before these changes were made.

---

If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!

---

What is a Bucket?

A bucket can represent a period of time, a grouping of items, or just a temporary placeholder to store some things for an intermediate calculation.  In this blog, I give three examples of buckets to demonstrate how they are used.  The first example (Figure 1) shows how items can be grouped together into buckets, the second example (Figure 2) shows how time can be used to create buckets, and the third example (Figure 3) uses a slightly more advanced approach to calculate time-varying parameters using time-based buckets.

 Click here to watch the Example 1 bucket training video

Click here to watch the Example 2 time-bucket training video

Click here to access a Tableau Public workbook for this example

Background

Assume you have a database of weekly-based unit counts for different types of products from many states in the US.  The weekly unit counts can be either positive (new customers) or negative (lost customers).  For each of these products, the margin associated with these products is calculated on a monthly basis after all the accounting is complete.  The objective of this tip and trick is to use the unit counts and unit-based margins (varies by month) to calculate the total margin by product by state by month.

The Solution: Use calculated fields to perform the calculations

Step 1: Sum-up weekly units for each month for each focus product, which Tableau does automatically

Step 2: Assign monthly margins per unit for each focus product.  Create calculated fields to hold the margins for Feb, Mar, Apr, May and Jun 2008. In the example shown below, LD = long distance and the margin is $5.64 per unit.

See calculated field: Feb_08_marginsif [focusproduct]=”Long” then 1.64
elseif [focusproduct]=”Int” then 2.15
elseif [focusproduct]=”Home” then 3.52
elseif [focusproduct]=”Pkg” then 1.52
elseif [focusproduct]=”Sats” then 1.98
elseif [focusproduct]=”Wires” then 1.34
End

Step 3: Check the date to see which margins get applied for any given set of data. The date is store in a field known as MEDIA_WEEK.  For this example, if the date occurs before March 1, 2008, use the February 2008 margins.

See calculated field:  Margin_by_units_over_time

If([MEDIA_WEEK]<date(“3/1/2008”))then

[Feb_08_margins]

elseif([MEDIA_WEEK]<date(“4/1/2008”))then

[Mar_08_margins]

elseif([MEDIA_WEEK]<date(“5/1/2008”))then

[Apr_08_margins]

elseif([MEDIA_WEEK]<date(“6/1/2008”))then

[May_08_margins]

elseif([MEDIA_WEEK]<date(“7/1/2008”))then

[Jun_08_margins]

End

Step 4: Calculate the total margin by focus product.  This involves using the unit count multiplied by the margin per unit determined in Step 3 above.

See calculated field:  Total_Margin_by_fp_over_time

if [focusproduct]=”Long” then [units]*[Margin_by_units_over_time]

elseif [focusproduct]=”Int” then [units]*[Margin_by_units_over_time]

elseif [focusproduct]=”Home” then [units]*[Margin_by_units_over_time]

elseif [focusproduct]=”Pkg” then [units]*[Margin_by_units_over_time]

elseif [focusproduct]=”Sats” then [units]*[Margin_by_units_over_time]

elseif [focusproduct]=”Wires” then [units]*[Margin_by_units_over_time]

end

Step 5: Create tables and charts of the margins on a state/time basis

 Click here to access this example.

Final Thoughts

About three years after publishing this article, I received a comment from a person that wanted to develop three-month buckets. You can see the comment below this article. Today I created the solution for them and thought it would be a good idea to include how I did that in this article.

The video shown below describes what I did and how I did it. You can click here to retrieve the Excel file I write to create the calculated field for Tableau.