Over the last month, I’ve come across several problems that clients were encountering with their visuals. In this post I’d like to talk about three of them. These three problems were similar in the sense that they were all solved with lesser known table calculation functions. In each case, I’ll present the problem and how it was solved. I will be using the Superstore dataset to recreate the scenarios that were encountered.
A client had a simple bar chart showing sales by month for the current year. They wanted to hide the last bar since it represented a partial month due to the fact that we were only halfway through June. This problem was a perfect candidate to be solved with the LAST () table calculation function.
The LAST () function returns the number of cells from the current cell to the last cell in the partition. This means that if we have six bars representing January to June, June would have a value of 0 since it is the last bar and January would have a value of 5 since it is 5 bars away from the last bar. To demonstrate this, I created a calculation called “Last Label” with the formula LAST () and put this on the label shelf.
To remove the last bar, we can create a calculated field called “Last Filter” with the formula LAST () = 0. This will return either true or false. We can put this field on the filter shelf and select false. This removes the partial month.
A client had a simple bar chart showing sales broken down by segment and product category. They wanted to emphasize the product category with the most sales per segment. This problem was a perfect candidate to use the WINDOW_MAX () table calculation function.
The WINDOW_MAX () function returns the maximum of the expression within the partition. If you are unfamiliar with the concept of a partition, I would encourage you to consider taking our Tableau Master Training (link to master class). Understanding addressing and partitioning is fundamental in correctly using table calculations in Tableau. You can think of a partition as the area of your chart in which the function WINDOW_MAX will look at to compute the maximum value of the expression that we specify.
We can use the WINDOW_MAX function to identify the product category with the most sales per segment. Then we can colour this bar so that it stands out against the other product categories. We can create a calculation “Is Window Max?” with the formula SUM([Sales]) = WINDOW_MAX(SUM([Sales])).
Let’s put this field on the colour shelf. You can see that Tableau is currently colouring the largest bar across the entire visual with a different colour. This is indicating that the current partition is in fact the entire table. This is not quite what we want. Let’s click on the “Is Window Max” pill and change the “Compute Using” value to “Pane (down)”. There we have it. Tableau is now colouring the product category with the highest sales per segment.
A client had a visual that displayed sales by month for the past two years. The visual also showed subtotals and grand totals. This was a requirement. Displaying subtotals and grand totals created the problem of shrinking the monthly bars because the large total values increased the axis range of the chart. This made it difficult to see the monthly sales values and those were the most important part of the visual. The client wanted to display the subtotals and the grand total value but it didn’t have to be in the form of a bar. It could simply be a label. This problem was a perfect candidate for the SIZE () table calculation function.
The SIZE () function returns the number of cells in the partition. If you are already knowledgeable about table calculations, then you might know that totals are their own partition. We can take advantage of this to solve the problem.
Our desired result will be to display the bars normally for the monthly figures. For subtotals and the grand total, we will display a label rather than a bar so we don’t skew the axis range. Let’s create a calculation called “Size” with the formula SIZE (). We’ll put this on the label shelf to see how this works. Let’s also change the addressing to “Pane (across)” so that our calculation is computing per year.
The monthly bars all have a label of 12. This is because there are 12 months per year. In other words, there are 12 cells or marks per partition. The subtotals and grand total on the other hand all have a value of 1. This is because totals are their own partition. The 2018 subtotal sales value is not lumped together with the 2018 monthly sales partition. It exists separately and is its own partition.
We can use this to our advantage in our next calculation. Let’s create a calculation called “Sales for Months” with the formula IF SIZE () = 1 THEN 0 ELSE SUM([Sales]) END. This calculation goes to each bar and checks how many bars are within the partition in which that bar resides in. For all the monthly bars, the size calculation will return 12, so our new field returns SUM([Sales]) due to the ELSE condition. For the subtotals and grand total, the size calculation returns a 1 and as a result the “Sales for Months” calculation will return a 0. This means that when we encounter a total, we won’t be displaying a bar.
Let’s replace SUM([Sales]) on the rows shelf with the “Sales for Months” field. Let’s make sure the addressing is “Pane (across)”. Let’s also put SUM([Sales]) on the label shelf. There we have it. We are no longer displaying bars for our totals which allows us to see the sales trend across months much easier now that our axis scale isn’t inflated due to the total values.
It is also worth mentioning that this approach won’t work if your partition legitimately has only one bar. For example, if you were displaying sales by year and we were in the month of January, then the partition would only have one bar. Consequently, the size function would return a value of 1 and the “Sales for Months” formula would treat January as a total.
And there you have it. Three visualization challenges solved with lesser known table calculations functions.
If you’re encountering a challenge with a Tableau visual, contact one of our Tableau professionals for assistance
Automate and Digitize: Where Should Credit Unions Start to Build Efficiency?
Credit unions that want to improve efficiency should start by identifying their pain points, building a business case for digitization/automation, conducting a pilot program, implementing quick wins, and exploring new opportunities.
CAO Leadership Series: Municipal Budget Development
Addressing the challenges of municipal budget planning requires a holistic and forward-thinking approach. It necessitates active and continuous engagement with community members, proactive risk reduction strategies, and efficient procurement practices.
Unlocking Security Excellence: Essential IAM & RBAC Best Practices for Robust Application Access and IT Risk Management
The rise of new technologies and the ever-evolving IT landscape have necessitated the implementation of a robust Identity and Access Management (IAM) system accompanied by a Role-Based Access Control (RBAC) framework.
How Canadian Credit Unions Can Leverage ESG Principles and Technology to Reach a Younger Generation of Members
The incorporation of ESG principles by CCUs, coupled with the effective use of technology and targeted marketing, presents a powerful strategy for attracting younger members and securing future growth.
Building High Performing Teams: The 8 Components of Resilience
In a world where change is the only constant, organizations are awakening to the undeniable truth – resilience is the secret weapon for survival and success.
Natural Language in Data Visualization: A Showdown Between Tableau and Power BI
Two industry-leading data visualization tools, Tableau and Power BI, both offer the ability to query data using natural language. But how do they stack up?
7 Drivers of Economic Development
These seven drivers of economic development bring new money into the municipalities, accelerate the velocity of money within the city, increase the engagement of citizens, and propel the generation of new ideas, technologies, talent, success stories, wealth, and global rankings.
Steering Through Uncertainty: The Impact of IFRS 17 on Risk Management and Control Strategies
With a strong emphasis on accuracy and integrity, insurers are faced with the task of redefining their control environments and governance structures for financial reporting.
Analytical Data Mart vs. Data Lake: Which Approach is Better for Your Analytics?
Welcome to the world of data-driven organizations where it is crucial to have a well governed repository to efficiently store and manage your valuable data.
Mastering IFRS 17 with a Strategic Target Operating Model
When applied specifically to the realm of IFRS 17, a strategic Target Operating Model provides a high-level view of the end-to-end solution design, processes, controls, and close schedule required to execute the new finance model.
The Push for Companies to Prioritize Leadership Development
Leaders have been expected to do more than ever in the past few years. Navigating through uncertainty, dealing with new challenges, and responding to rapid change have all become commonplace demands for management teams.
Navigating a Hybrid Work Environment with Gen Z Employees
Millennials, who have dominated the workforce for the past decade, are now ceding the stage to the next generation of employees – Generation Z.
Developing Early Career Talent: 5 Strategies for Success
A robust and effective early career talent development program is essential for companies looking to grow their future leaders from within.
How to Capitalize on Your IFRS 17 Investment
With guidance and support insurers can move from IFRS 17 compliance to business as usual (BAU) and fully capitalize on their investment.
Leading & Engaging Gen Zs – The Bold Approach
Gen Zs are the new age workforce that is gradually changing the landscape of the corporate world. Leading and engaging Gen Zs in this environment requires a bold approach.
Power BI vs Tableau – Which is Better?
Although Tableau and Power BI are similar business intelligence tools, there are key differences that organizations should be aware of when considering analytical requirements.