Drilling with Set Actions
If you’ve ever tried to use the default drill functionality within Tableau, you know that it could be a more user friendly experience. The default table drill functionality opens all of the options at the next drill level which can force a user to lose sight of the data upon which they’re focusing. A more user-friendly option enables the user to only drill into a specific selected value where focus and attention can be maintained. This is otherwise known as asymmetric drill down.
Fortunately as of version 2018.3, Tableau has added Set Actions as a new functionality. At a high level, developers can take an existing set and update its values based upon a user’s actions in the visualization. The set can be employed via a calculated field within the visualization, via direct placement in the visualization or on the marks card property.
In lay terms this means empowering a user with more interactivity to impact their analyses.
In this first video, I’ll demonstrate a use of set actions on an NBA data set. We’ll drill from Conference to Division to Team to Player. This tip will be easily applicable to your Tableau data. And with the bonus tree-map tip you’ll release your inner Piet Mondrian.
Feel free to interact with the set action example on Tableau Public and then download and dissect the workbook.
Drilling with Level of Detail (LOD) Calculations
If you want to stay with a classic approach, a nice Level of Detail (LOD) workaround can be employed to drill into the next level. Here is a tip that accomplishes a similar outcome where I demonstrate a technique originally presented by Marc Rueter at Tableau Conference 2017.
Now that I’ve equipped you with the knowledge to incorporate customized drilling functionality into your analyses, go forth and do some great things with your data!
Image Copyright dzxy on 123rf.com
This video represents part two in my Market Basket Analysis series.
The steps in the post were inspired by the book Tableau Unlimited written by former co-worker of mine, Chandraish Sinha. I wasn’t planning to construct another market basket analysis video but when I saw the approach outlined in his book, I felt like it warranted sharing with my readers and followers.
In this version we’ll use default Tableau Superstore data to show the relationship between sub-categories on an Order; all without using a self table join. The visualization and analysis is driven by a user selection parameter.
Once the user selects a sub-category, the bar chart visualization updates to reflect the number of associated sub-category items on the same order.
Watch the video and as always get out there and do some great things with your data!
Feel free to also check out Part 1 here where we create a simpler correlation matrix version that shows all the sub-category relationships in one visual.
A favored analysis technique employed by retailers to help them understand the purchase behavior of their customers is the market basket analysis. When you log on to Amazon, most likely you’ve noticed the “Frequently Bought Together” section where Jeff Bezos and company would like to cross-sell you additional products based upon the purchase history of other people who have purchased the same item.
Market Basket Analysis influences how retailers institute sales promotions, loyalty programs, cross-selling/up-selling and even store layouts.
If a retailer observes that most people who purchase Coca-Cola also purchase a package of Doritos (I know they’re competing companies), then it may not make sense to discount both items at once as the consumer might have purchased the associated item at full price anyhow. Understanding the correlation between products is powerful information.
In this video, we’ll use Tableau Superstore data to perform a simple market basket analysis.
Feel free to interact with this market basket analysis on Tableau Public and then download and dissect the workbook.
Watch the video and as always get out there and do some great things with your data.
Feel free to also check out Part 2 here where we’ll create an analysis driven by a user selection parameter.
In this latest video, I’ll explain how to use a handy DAX function in Power BI in order to group dates together for reporting. We’ll examine a dashboard that contains fields corresponding to purchase item, purchase date and purchase cost. We’ll then create a calculated column and use the SWITCH function in Power BI to perform our date grouping on the purchase date.
Watch the video to learn how to group dates into the following aging buckets, which can be customized to fit your specific need.
- 0-15 Days
- 16-30 Days
- 31-59 Days
- 60+ Days
If you are familiar with SQL, then you’ll recognize that the SWITCH function is very similar to the CASE statement; which is SQL’s way of handling IF/THEN logic.
Even though we’re creating a calculated column within Power BI itself, best practice is to push calculated fields to the source when possible. The closer calculated fields are to the underlying source data, the better the performance of the dashboard.
SQL is the lifeblood of any data professional. If you can’t leverage SQL and you work with data, your life will be more difficult than it needs to be.
In this video I am using SQL Server Express to turn a simple normalized dataset into a pivoted dataset. This is not a beginner video as I assume you are familiar with basic SQL concepts.
T-SQL is Microsoft’s SQL language that contains additional functions and capabilities over and above ANSI standards. We’ll use some of these functions to turn the following data set that displays average rents in major American cities into a pivoted denormalized dataset.
The City values in the City column will become individual columns in a new pivoted dataset with their respective Average Rent values appearing underneath.
We’re going to transform this:
Notice how the city values are now column heads and the respective Average Rent values are underneath.
Make sure you watch the video but here is the code used in the example.
IF OBJECT_ID('tempdb..##TBL_TEMP') IS NOT NULL
DROP TABLE ##TBL_TEMP
--This parameter will hold the dynamically created SQL script
DECLARE @SQLQuery AS NVARCHAR(MAX)
--This parameter will hold the Pivoted Column values
DECLARE @PivotColumns AS NVARCHAR(MAX)
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([City])
/* UNCOMMENT TO SEE THE NEW COLUMN NAMES THAT WILL BE CREATED */
--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN
SET @SQLQuery =
N'SELECT [City Code],[Metro],[County],[State],[Population Rank],' + @PivotColumns + '
PIVOT( MAX([Average Rent])
FOR [City] IN (' + @PivotColumns + ')) AS Q'
/* UNCOMMENT TO SEE THE DYNAMICALLY CREATED SQL STATEMENT */
--Execute dynamic query
EXEC sp_executesql @SQLQuery
/* VIEW PIVOTED TABLE RESULTS */
Select * from ##TBL_TEMP
Big shoutout to StackOverflow for help with this example.
The humble bar chart is the heart and soul of any visualization tool and is the most effective way to compare individual categorical values. We as humans are very adept at detecting small differences in length from a common baseline .
To quote the Harvard Business Review , “The ability to create smart data visualizations was once a nice-to-have skill. But in today’s complex business world, where the amount of data is overwhelming, being able to create and communicate through compelling data visualizations is a must-have skill for managers.”
If you’re going to start learning a new visualization tool, there is no better place to start than with bar chart basics. In this video I will share how to place a “percent of total” measure (i.e. value) on a Power BI bar chart. We’ll also briefly touch upon customizing the chart’s diverging color scheme.
Since Microsoft is basically giving away Power BI Desktop for free, it may become as ubiquitous as Excel. Don’t be left out!
 Cotgreave, A., Shaffer, J., Wexler, S. (2017). The Big Book of Dashboards: Visualizing Your Data Using Real-World Business Scenarios. Hoboken, NJ: John Wiley & Sons, Inc.
In this video I demonstrate a couple of methods that will display the total values of your stacked bar charts in Tableau. The first method deals with a dual axis approach while the second method involves individual cell reference lines. Both approaches accomplish the same objective. Hope you enjoy this tip!
If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel.