Today’s blog is about the min(1) paradigm for KPI charting in Tableau desktop and how to make advanced KPI charts without needing slow table calculations to do the computations for you. Instead, we will show you how to utilize Tableau features to generate a better KPI solution. Welcome to learning how to create a min(1) KPI dashboard. And if you’re breaking into the data industry, this is a good tutorial for you and advanced gurus.
At times, developers think they need to generate many visualizations to move forward and float them in a specific way to create a dashboard; however, as data sizes increase, enterprises find these dashboards inefficient. To begin unlocking the power of data, we first must master the software!
When I first learned this, it was called a “trick.” But the more I use it, the more I realize it’s a native feature and should be utilized because it helps make very complicated visualizations easily, unlike the <10 MB Google Sheet Error, which inevitably leads to much engineering. For supplemental reading, check out the period-over-period in Tableau.
We use this solution often because it helps avoid a need to edit colors in the colors marks editing screens, which can be difficult and usually generates the need to force the data to show specific values before you can tell Tableau when to change the color of an object. Eventually, meeting people become excited about improving KPI visualizations, which is challenging to do with marks alone. Doing it with text has become a fast approach to generating complex KPIs with a simple min(1).
What’s happening with KPI Dashboards in Tableau?
Making a lot of visualizations to generate a KPI chart is not the correct answer because it will slow down your Tableau Workbook! Instead, we have discovered decreasing the amount of visualization is the best path to optimization and easier cycles of support or additional features. The lack of visualizations means you can do more within the same workspace and keep track of less because it is consolidated into one chart.
A lot of charts means making a lot of data extract requests, a lot of live data requests, a lot of file data requests, a lot of tooltips, and a lot of filters,… Filters do a lot of performance degradation, also known as damaging the user experience due to generating technology inefficiently.
If you have ever been where end users need a faster dashboard, and the KPI dashboard is moving slowly, you have found a great wiki on building KPI charts in Tableau.
Learning how to make optimized Tableau KPI Dashboards is a path to building an operationalized Tableau solution you can utilize with any data source in the future; when you’re done building this one time, you can easily copy and paste this Tableau win into more workbooks.
This is a screenshot of many KPI charts on one visualization; pause here to see how this is possible.
What is the minimum value of 1? (min(1))
What is the minimum value of 1? What is X when computing min(1)=x?
One.
Finding the minimum of 1 isn’t rocket science. However, we must understand this logic to maximize the product’s functionality. Also, did you know min(1) is faster than max(1) or attr(1)?
You can “fix” a value behind the scenes to a static value, which keeps the axis from moving, giving you a conditional background to edit with the color marks card. The text marks card is now available to build conditional text or colors on text or text images.
The number Of Records or the value of 1 in a calculation will handle the generation of your KPI chart.
By the end of the Tableau min1 kpi charts article…
You will know how to make a KPI chart without creating multiple visualizations per user request.
You will know how to keep it simple and easy to support
You will understand what we are doing, why it works, and how to build it
You will have code to copy and paste and screenshots to follow along
Developing a good understanding of how to keep Tableau simple will eliminate the unnecessary bloat and server strain. By removing unoptimized development, you’re removing a wait time per interaction on your development. Most environments will not be happy with less than split second or second response times; it’s intuitive to understand how to improve the speeds of your workbooks without having to rearchitect the data.
“I recommend as data grows, you begin adding relational theory to your data pipeline, ask your data engineers to work through the atomic pieces of your data, bring the data to a 1nf, 2nf, or 3nf state until the data moves faster or vastly decreases in data sizes. It’s not unusual to aggregate second data into monthly data if your end users only need monthly data.”
Tyler Garret, Founder of Dev3lopcom, llc
Please keep it simple when crafting your tableau dashboards; the more simple the development, the easier it will be to maintain solutions in the future.
Using Min(1) offers an easy way to learn the product. However, you need to know the product!
Learning the min(1) solution from end to end will change how you solve problems.
“I gravitated to the min(1) solution because it offers a simple playing field for edits, support isn’t a nightmare, and it’s easy to conditionally color. Mostly, I enjoy that my complexities turn into calculations, the calculations I can either duplicate or copy paste back into the workbook, understanding the difference between these two or whether they nest into each other based on these single click is a large difference when generating calculations that need to be connected or not connected. Before getting too far in this tutorial, I recommend you understand the differences between duplicating the calculations and copying the calculations, and also understand the product has two spaces this becomes a negative development path or positive development path. The two places are calculations and dashboards/visualizations. Test the right click copy and duplicate as you evolve because it will define your speeds in the product for a long time.”
Tyler Garrett, Founder of Dev3lopcom, llc
Begin building your first min1 KPI bar chart.
Thanks for taking the time to start this min1 kpi bar chart tutorial. If you have any questions or get lost in the tutorial, please contact us or leave a comment. Happy to help.
To begin, start your tableau product!
Open a new visualization and build a min(1) bar chart.
a simple bar chart, min1 generates an axis length of 1
Okay, min(1), always 1. Build it.
Start typing min(1) – hit ENTER!
min(1) on sheet2
Not sure how I’m typing in this area? Double-click in the rows or columns below your marks card. First, double-click, and then you will see an input appear. If you want to harden these and not have them become “viz only static calls,” you can drag them into the workbook. I like this method because it’s all upfront, it names the calculations the name of the code, and it makes life relatively fast compared to not understanding this functionality and always building calculations, which leads to always needing to find those calculations, open those calculations, and edit those calculations. Also, when you need to delete the calculation, it takes longer because you have to see it in the workbook, and it’s not sitting in the rows bucket because you made a calculation. Based on this paragraph, you should better understand options and even the best path for you; it depends on what’s fast for you.
In short, let’s walk through these features.
Pro TIP: Tableau desktop offers a quick and easy user experience when adding content to the visualization or SHEET.
In a couple of places, you can double-click and type—rows, Columns, and here… on the bottom of the marks card.
Marks card is free to double-click below the squares. Then, you move it to the mark square of your preference.
Making a KPI Chart in Tableau using Min(1)
You have your min(1) bar established.
Make 3, hold ctrl+mouse1, and drag and drop to duplicate min1 across columns three times. Command+mouse1 click drag and drop for macOS.
Open up the axis on each and change the Fixed end to 1. Zero to One will be the end goal.
Click okay, and your simple min(1) mark will cover the entire pane.
Complete the following two similarly. You’re changing the value of the axis so that you can build a background on your KPI. This background is now easy to color conditionally. If you do a dual axis and make it a shape, now you have a background that can be conditionally colored and a shape that can be conditionally colored or changed. Welcome to a world of “smart KPI building” and the removal of all the strange multi-chart dashboards created due to not knowing these solutions.
Once completed, let’s add profit, sales, and a count of customers on the last KPI Chart.
Drag profit on your first minute (1) and sales on your second minute (1), and generate a calculated field for the final customer name count. I want to see the distinct count of customer names to understand the count of customers doing X.
Once you have these measure values on your KPI charts, you should start to see everything forming, and you can see we have some formatting ahead of us now.
Of course, profit is on the far left, and sales are in the middle, but when you open this solution again, you will not understand what we are offering the end users. To provide this solution to the end users, we need to make it sensible.
I am making the KPI Chart sensible.
Often, KPI charts come with an explanation, so don’t forget to explain what the number or chart means.
Also, pretend your end users are not super technical and can’t read minds.
Please do your best to help people make quick decisions; let’s fill in the blanks with sensible labels!
Click on any of the three to open the correct mark card.
The following steps are self-explanatory: filling in the rest of the labels, making the font subtly bigger, or a bold KPI chart will work for some people.
Think SMART in these steps. Don’t spend 5 hours making a KPI chart look a certain way. Don’t spend 10 hours making tooltips look cool. Ask end users for feedback before wasting time.
Open the ALL marks card and change the color to white.
Cleaning up your KPI charts
Cleaning up the KPI chart is about “simple clicks,” not “hard clicks.”
Hiding headers may hide a label, so let’s discuss.
Hiding headers may help remove the axis, but it may also release your axis’s title.
Some charts work great with the axis as the label; other times, it’s more complex. Feel free to play with different labeling methods.
I prefer labeling in the text pad editor, per marks card because it offers an endless amount of options. I enjoy formatting each marks card globally with the sheet formatting tools OR twiddle around with the mark level edits. Depends on what the end goals, which I enjoy designing on paper or whiteboard before I begin. Drawing pictures and steps is my method of visually seeing the algorithm.
by tyler garrett
Hide headers with a right click on the axis, and uncheck the show header.
Center the text on the “all marks” card because it will update across all marks cards! Work cleverly, not hard!
With text aligning in the middle, our KPI chart is starting to form.
These are personal preference changes; let your end users decide.
The key to utilizing the min(1) is the ability to augment Tableau to a style that fits your formatting needs. KPI charts have many variations; this particular one gives you complete flexibility to a text editor, which provides you with as much flexibility as you can write a calculation. The text can be conditionally colored based on logic, as can the box or min(1) bar in the background.
I prefer the min(1) bar versus a “square mark” sized large because a min(1) bar is predictable and fixable within editing the axis. As you need two marks colored in 1 mark card space, you can use a dual axis or conditionally color the text.
Right-click what you want to format in Tableau Desktop.
Right-click what you want to format is what I ask people to repeat when I teach them Tableau desktop, “right-click what you want to format.”
Today, we right-click the chart. Because… “we right-click what we want to format in Tableau!”
We drive to the “lines” because those are pesky, and someone in a meeting will likely ask you to remove the borders!
Boarders, dividers, tomatoes, potatoes, try and HIDE these and see if it’s the right path. It’s easy to get frustrated finding the right edit; I’m not saying I haven’t gone mad and turned everything off only to find the bar chart had borders turned on.
Sometimes, digging around these formatting menus is more accessible than telling someone the right buttons to hit because no one will memorize all these clicks. You will get better as you use Tableau more; keep clicking!
Open these, click none. Yay!
Notice we almost have it complete. We are still showing the zero line. Boo!
Removing the zero line seems like it gets most of it
Instead of wasting your time, double-check a few more things… zero line, axis ruler, maybe your min(1) has a border around the bar?
Axis ticks, zero lines, and grid lines.
Tired of clicking yet?!
Luckily, in the end, you’re developing your way into a tool that allows anyone to filter this sheet and give them blimp view aggregates! You might as well never build this again; you can swap out measures easily or make a three by 3 with a simple duplication of your sheet.
Omg, KPI charts without borders.
You now have three aggregated measures and a bar to color + tooltips conditionally.
Okay, we have simple KPI charts down. Let’s look at more advanced functionality and how to do it with copy-paste calculations!
Dynamic KPI charting in Tableau, building an on/off flag using dates and parameters.
Duplicate your sheet, and let’s begin our Dynamic KPI charting in Tableau! If you don’t already know, we are using the super store sample set of data that comes default with every installation of Tableau.
Building these dynamic charts or KPI charts is easy if you understand the min(1) steps we explained above and are excited about making on/off flags.
Also, if you’re learning, be willing to start thinking outside of the bucket! We will use that code to deep dive here and make our KPI chart more flexible and user-friendly.
We steal the code from our data buckets to generate three different buckets of data.
if DATETRUNC('month', [Order Date])> DATEADD('month', -([x]+ datediff('month',{MAX([Order Date])},today())) , TODAY()) then "Current Period" //make this 0 elseif DATETRUNC('month', [Order Date])> DATEADD('month', -([x]*2+ datediff('month',{MAX([Order Date])},today())) , TODAY()) then "Previous Period" //make this a 1 else "Filter" //make this a 2 END //[Order date] = date //[x] = parameter
With this new code implemented, you will only need one parameter to begin seeing period-over-period data.
Build a parameter.
call it x
data type integer
current value 1
OKAY
Drag and drop your new period-over-period calculation on the visualization. Exclude Filter.
After you exclude the filter, you’re left with two distinct periods, which is now set by your parameter.
Show parameter control by right-clicking the parameter.
Show the parameter on the sheet.
The parameter is input into your period-over-period algorithm, which takes the number of parameters inputted as months.
When you add more numbers to the input, you’re increasing the number of months. Because we don’t love static solutions, let’s optimize the date bucket solution, too!
Let’s dive back into the period-over-period calculation and make it better for end users. Start with generating another parameter!
Show the parameter and open your calculation.
Update “month” with your new parameter d, the date part.
Notice we change the notes on the bottom of the calculation; leaving comments at the bottom or top is a great way to tell others what you did.
We can simplify our life by removing the “logical-looking text values” and cutting them back to only an integer! Rename your calc to a single letter to facilitate your future development.
At this point, your calculation is relatively efficient and utilizes many parameters. When you save calc or hit OKAY, the settings on the filter will change; check out the following popup.
We expect an error because the previous filter on the “p o p” calculation or period over period calc – was only filtering on a condition. This condition is no longer possible.
The previous condition excluded the “Filter bucket” from our period-over-period solution.
Filter 2.
Filter 2! We are now showing 0, as current and 1 as previous.
This will save a lot of time when generating calculations like;
(if p=0 then (profit) else 0 end) – (if p=1 then (profit) else 0 end)
VS calculations like…
(if [p o p]=”Current period” then [profit] else 0 end) – (if [p o p]=”Previous period” then [profit] else 0 end)
And understanding how slow Tableau works with “strings” is also key to making this calculation fast.
Rename your sheet, call it “KPI-1” and begin discussing how to use this pop calculation to show dynamic coloring.
Finding the difference or change without Table Calculations
Table calculations are powerful for ad-hoc analytics but often can be outsmarted with basic logic or SQL. Outsmarting the need to use Table calculations means your workbook will be faster. Everything below shows you how to generate the difference or change variation for color variations, including an in-depth look at how to develop the solution yourself.
Having two sets of numbers and two sets of dates generates a difference or change in the data. A trend because we have an archive of data; archive data is like saying “past data or historical.”
Whether you’re hunting for a percentage difference or a subtraction difference, finding these are relatively easy with dates.
Next steps:
Write two quick calculations.
These calculations offer two simple drag-and-drop values that represent X amount of days!
Now, we can quickly see how these will start to be utilized.
0 = current period
1= previous period
If the current period is greater than the previous period, is that a negative or a positive? Logically, we use an algorithm to show a HOT or COLD coloring, aka GOOD or BAD.
Sometimes, measured values are not all black and white.
It may seem obvious that more money is a positive or GREEN, but realistically, an uptick or downtick may be green or red.
It’s always best to ask what end users first.
For the sake of this demo/blog, let’s begin using the concept that the current profit is higher than the previous period, which is green. We should celebrate our successes and use green to share this win across our dashboard, but without generating a Table calculation, we need to create a difference between the two values.
Check your work!
using the dashboard to verify calculations work.
In the final steps, make a calculation for sum([0])-sum([1]).
We need to understand what value is positive or negative.
Build another min(1) chart!
Use the parameters and your new difference calculation to build the KPI values!
Bonus points if you generate this KPI-3!showing an increase of 9,679…
We will simulate a user experience by dragging and dropping [Container] to see shipping container options onto the visualization.
Notice in our chart, we don’t have any colors showing Green or Red. However, we have logic.
If the Profit value is less than zero dollars, the value is negative. If it’s greater than zero dollars, the value is positive.
Let’s rename our calculation to save us the pain in future calculations. Rename it to “C” for change.
Now, if C>0, then positive else negative! We don’t want to pretend writing an if statement here is wise; study about Boolean calculations as alternatives.
Generate one final calculation.
You can add this to your visualization and quickly visualize the difference between periods.
Add GreenRed to your Color Marks card
you’ll see it populate your visualization
change the colors to green & red
Maybe you’re familiar with this kind of color, well here’s why I enjoy this kind of KPI charting! With the calculations we have, and logic developed, we can build an advanced color chart within 1 single KPI mark card, without having to do a dual axis.
Using text editor for conditional coloring using simple logic
Using a text editor, you are given a lot of flexibility in a user-friendly space. Trying to explain the dual axis to give an up/down arrow can be more confusing and complex to support for new users.
▲ up arrow
▼ down arrow
By generating two more calculations, you can offer two different things to color in your text editor.
Remove the “containers” dimension and drag it to the filters. Convert to a single value list, aka radio buttons.
omg green
Radio buttons help us simulate a typical KPI experience. Our end-user has a couple of filters, and charts are likely below; filtering this way in the visualization gives us the ability to debug immediately.
Swap to Jumbo Box. Notice the negative background. What if our end user says, “We don’t want a red background.” Or maybe the end user wants 3 conditional coloring variables, and Tableau can only do a dual axis to show two.
omg red
Remove the color GreenRed from the visualization. Drag our two new positive and negative calculations with the text arrows. Put those on the Text mark card.
notice the positive arrow is missing…
Where is the positive arrow?
The positive arrow missing can be a complex few steps to figure out, you need to find each arrow, color it, and the end user will see green or red.
That’s why I rather enjoy this method. Also, using the container filter gives us the ability to see both positive and negative.
This process avoids needing to figure out where the green arrow is located. For what it’s worth if you’re not familiar with this step, I encourage you to try and build the arrows without these steps.
awesome
If you’re experienced, you may enjoy the fact that you’re not having to edit the color marks card, and you can rest assured your workbook will not break as easily as before.
Re-arrange everything according to your “design requirements.”
lovely
Okay, now you’re on the way to understanding how you can
effectively design advanced KPI charts
without developing slow tableau workbooks
and without table calculations
Below are a few final remarks about making your min(1) KPI charts easy to maintain in the future. Not advanced, but rather more for easy support.
outstanding
Ending notes about the min1 solution
Eventually, min(1) has a max, and it becomes harder to support; there are a lot of calculations to maintain, and you have to learn tricks to make it work better. Once you get there, copy-pasting things and duplicating things as needed becomes a priority, and edits become easier as you scale, but it is also a lot to maintain. Consider the amount of work/effort before committing to big asks. There’s always something easier; keep updated on Tableau Community too.
Tips about min(1) KPI for new users
I try to explain to new Tableau users in Training during a Tableau consulting engagement, “Don’t let this become the hammer” Often, Tableau has easier ways to solve the problem, but when things get custom, I always use this solution here.”
Eventually, using the same hammer over and over, it feels like, “This shoe doesn’t fit, and dashboards are slow.” Remember, at times, it’s okay to swap to a visualization, and that’s better because it’s less work for you and Tableau (most of the time).
lots of min(1) which is what though?
Pro tips about Min(1) kpis
Above in the screenshot, notice the layering of “more fields”… does it ever look like this when you’re developing your KPIs/dashboards? This happens, and here’s a fix.
Let’s chat about what’s happening in the Tableau world.
And, before we begin, let’s swap the axis.
min(1)
When we double click on columns or rows, and type min(1), we can see we are typing a calculation, that calculation is a SHEET level only calculation. It doesn’t live anywhere else in the workbook, but it does live in the workbook; from an optimization perspective, don’t bother stressing yourself out or testing what’s best; just consider what’s easiest to support and build faster, and harden later.
Type //something on your min(1) calc…
You type //TheNameYouWantToSee (hold shift, then hit enter), it drops you to a new line, and then you can type min(1)… Or you can open your typed calcs by double clicking, and start from the front of the calc.
End goal, we want our field to show up right ‘meow’ (now).
making the new line is all it takes
By adding the //comment, and a new line, you can leave a note to help you dig through your min(1)’s.
A nice trick to avoid “more fields” becoming Min(1) over and over.
Never fun to support min(1) over and over in heavy KPI charts!
Tableau Pricing has simplified to 3 different user purchases and Tableau aka Salesforce increased their price.
The Tableau price is broken into the users ability to access the Tableau Desktop product, exploring dashboards built by Tableau desktop users, or viewing reports.
Tableau Creator: Creators within your organization encompass individuals who establish connections with data sources and transform that data into valuable insights for both themselves and their colleagues. They typically engage in the creation of reports and disseminate them through methods such as email or shared drives. These data power users frequently engage in ad hoc analyses to address deeper inquiries arising from their data.
Tableau Explorer: Explorers represent the contemporary business users who act as a driving force for organizational change. While their job titles may not explicitly label them as analysts, they possess a comfortable familiarity with data. They are motivated to advance their respective businesses and require the ability to delve deeply into data to discover answers to their unique questions, which often extend beyond the confines of pre-built reports.
Tableau Viewer: Viewers leverage data to enhance their decision-making processes. Tableau Viewers encompass a wide spectrum of individuals, ranging from team members who rely on data for their day-to-day tasks, to department heads who need insights into the progress of critical projects, and even to the CEO, who depends on high-level metrics to gauge the overall health of the organization.
Tableau Creator caters to a diverse range of professionals, including business analysts, vice presidents, data architects, office administrators, and even SQL experts. It empowers them with the flexibility they require to craft on-the-fly data discovery visualizations and engage in self-service analytics.
Furthermore, the latest Tableau pricing, which includes Tableau Creator, introduces a new lightweight ETL (Extract, Transform, Load) tool. This tool adds a layer of efficiency to data management, making it easier to extract, transform, and load data for analysis.
The Explorer and Viewer roles extend this accessibility to a broader audience, granting everyone the capability to access and view work published online. This inclusivity fosters an environment where individuals can freely pose questions and provide answers, all without the typical delays associated with a conventional centralized Business Intelligence model.
As a result, Tableau Creator not only streamlines the development of reports and dashboards but also eliminates the need for extensive back-and-forth email exchanges during the report/dashboard creation process. This enhances collaboration and expedites the decision-making process across your organization.
Tableau Pricing is now bundled into 3 simple plans.
We recommend you work with your Tableau Software sales manager if you are looking to implement Tableau Server. You have 15 days to get your work completed, let them know about your installation, and if it will take longer than 15 days, tell them upfront.
The cost of Tableau is an annual purchase, not a month to month purchase, and they bundle in a Tableau Server user with every purchase too.
Using Tableau Creator or Tableau Desktop is a minimum cost of $900+.
Tableau Creator:
Price: $75 per user per month (billed annually).
What You Get: Tableau Desktop, Tableau Prep Builder, and one Creator license on Tableau Cloud.
Description: Unleash your analytics potential with our Creator package, essential for every deployment. This suite empowers you to discover insights and manage your end-to-end analytics workflow.
Tableau Explorer:
Price: $42 per user per month (billed annually).
What You Get: One Explorer license for Tableau Cloud.
Description: With Tableau Explorer, you can explore trusted data and find answers to your questions quickly, enjoying full self-service analytics capabilities.
Tableau Viewer:
Price: $15 per user per month (billed annually).
What You Get: Access to view and interact with dashboards and visualizations on a secure and user-friendly platform.
Description: The Tableau Viewer allows you to view and engage with dashboards and visualizations easily, making data insights accessible to everyone.
Here’s the information presented in a simple table format:
Tableau Plan
Price per User/Month (Billed Annually)
Included Features
Tableau Creator
$75
– Tableau Desktop – Tableau Prep Builder – One Creator license on Tableau Cloud
Tableau Explorer
$42
– One Explorer license on Tableau Cloud
Tableau Viewer
$15
– View and interact with dashboards and visualizations on a secure platform
Having a solid understanding of the executive overview of Tableau is paramount. Tableau is not just another software solution; it’s a top player in the realm of business intelligence and data analytics. Many organizations, regardless of their size or industry, rely heavily on Tableau to gain insights from their data, make informed decisions, and stay competitive in the market.
The recent shift to monthly licensing in Tableau’s pricing structure signifies a broader industry trend toward greater flexibility and adaptability. This shift makes it easier for organizations to align their analytics capabilities with their evolving needs and financial constraints, opening the door to more accessible, scalable, and cost-effective data analytics solutions.
Being well-versed in Tableau’s executive overview is a strategic advantage for businesses and professionals. It allows them to harness the power of Tableau to visualize and explore data, create interactive dashboards, and generate valuable reports. With its user-friendly interface and robust capabilities, Tableau has the potential to transform the way businesses leverage data for decision-making.
Whether you’re an analyst, executive, or any professional seeking to unlock the potential of data analytics, understanding Tableau’s significance as a widely adopted reporting tool is a critical step toward staying ahead in the data-driven world. By choosing the right Tableau plan, you can tap into its capabilities and drive your organization’s success through data-driven insights.
Tableau Desktop looks great on this MSI laptop.
Our experience with tableau pricing.
We have experience talking to new users, clients, classrooms, and random people in elevators about Tableau pricing, and we are happy to help you learn more. However it’s also good to start the conversation with Tableau about pricing with their software, and learn who your sales representative or account manager will be.
We have experience with Tableau Consulting; installing tableau server, building data products end to end, helping companies monetize their data, building APIs for webhooks to talk to on your internal servers, setting up tableau online aka tableau cloud for your customers, & even building first time KPI dashboards, including design services!
If you have any questions regarding the price of Tableau or if it makes sense for your environment, contact us.
Tableau Dashboard development and end user usage dictates meta data creation or lack thereof. We have a template for you. It helps you formulate a large amount of navigation from a single landing page. A large journey that increases views per visit. This is helpful because it shows how actions like looking at data or increasing the size of charts, will show you the journey people are taking without interviewing them. Pretend if you have thousands of users, and wanted to know if they think one chart or another is more important. This dashboard will teach you the value of adding PowerBI style expand buttons inside of your dashboards and much more.
How you optimize Tableau desktop has a lot to do with how much meta data you can collect
If you know where people travel on your dashboard, you can add features here, make updates, delete content, upgrade content, and whatever you can think about.
Over fixating on creating dashboards that answer everything means you don’t see who views these extra views, it also becomes a slow dashboard compared to a dashboard with less, and often Tableau data products suffer because lack of understanding of what Tableau server (your infrastructure) or Tableau online (sales force infrastructure) tracks about your end users and doesn’t track.
When you’re in a Tableau Training, they don’t mention the importance of “meta data creation,” that’s an advance Tableau Consulting topic, rather they show you features that consolidate user experiences. These features often generate more work and lower the amount of traffic you could be building down an A or B path.
If product managers or directors overseeing the build of a Tableau data product have little to no experience seeing a Tableau dashboard in Production or post development, your data product may be suffering. Similar to Tableau developers with no experience building websites for clients and increasing the conversion from one page to the next are not going to be as good as a web developer who understands they want to track the usage of these dashboards so that they can understand the conversion.
The end game of a Tableau data product completed means your team now has access to meta data for further discovery.
A Tableau Meta data usage strategy..
A piece of your data governance strategy is to discuss how the product usage data will be used.
If your team has no experience using Tableau beyond building charts and fun dashboards, there’s a good chance you will not know about what data is being tracked from an end user perspective because they have never been in a project where this is relevant.
Knowing about meta data captured gives you bonus points when building dashboards in Tableau. When creating data products for large audiences you will want to be sure to capture as much data as humanely possible.
Conclusively…
If you don’t know what data is tracked, you don’t know what data is not tracked.
Not understanding this information or fundamentals of Tableau will limit your dashboards optimization possibilities.
Opportunities to Optimize the User Experience
There are always numerous opportunities to optimize the user experience and many navigations will show you this quickly in Tableau. These optimizations can significantly impact user satisfaction, engagement, and overall success. Here are some key areas to consider when seeking to optimize the user experience:
a) User-Centered Design: Begin with a deep understanding of your target audience, their needs, and preferences. User research, surveys, and usability testing can help in this regard. By placing the user at the center of the design process, you can tailor the experience to meet their expectations.
b) Responsive Design: With the proliferation of devices and screen sizes, ensuring a seamless experience across various platforms is crucial. Responsive design techniques can adapt the user interface to different screen sizes, making content and functionality accessible to a broader audience.
c) Performance Optimization: Users expect speedy and responsive applications. Minimizing load times, reducing latency, and optimizing code can significantly enhance the user experience. This includes implementing techniques like content delivery networks (CDNs), browser caching, and code minification.
d) Personalization: Tailoring the user experience based on individual preferences and behaviors can lead to higher user satisfaction. Implementing personalization features, such as recommendations, customized content, and user-specific settings, can make the user feel more valued and engaged.
e) Accessibility: Accessibility improvements are essential to ensure that all users, including those with disabilities, can access and use your product. Implementing features like alternative text for images, keyboard navigation, and screen reader compatibility is essential for creating an inclusive user experience.
Improving the Most Used User Experience
When improving the most used user experience within a product or service, it’s important to focus on areas that have the greatest impact on your users. Here’s a step-by-step approach:
a) Identify Key Use Cases: Start by analyzing data and user feedback to pinpoint the most frequently used features or aspects of your product. These are the areas where you should concentrate your efforts.
b) User Feedback: Solicit feedback from users who are regular users of these key features. Understand their pain points, challenges, and suggestions for improvement.
c) Streamline Workflows: Simplify and optimize the workflows related to the most used features. Reduce unnecessary steps, automate repetitive tasks, and make the process more intuitive.
d) Performance Enhancements: Ensure that these features are as fast and responsive as possible. Users will appreciate a speedy experience, especially when using core functionalities.
e) User Interface Design: Evaluate the user interface and design to make it more user-friendly. Implement user-centric design principles and consider A/B testing to find the most effective design changes.
f) Testing and Iteration: Continuously test the improvements with real users and gather feedback to make iterative adjustments. A data-driven approach is essential for ongoing refinement.
The Opportunity to Architect a Product That Works Without Manual Intervention
Creating a product that operates without manual intervention is a goal often associated with automation, AI, and smart systems. Here are some key considerations for achieving this:
a) Data-Driven Decision Making: Implement data analytics and machine learning algorithms to enable the product to analyze data and make decisions autonomously. For example, a predictive maintenance system in manufacturing can use sensor data to schedule repairs before equipment fails.
b) Intelligent Automation: Incorporate automation into routine tasks and processes. This can range from chatbots handling customer support inquiries to software that automatically categorizes and sorts incoming emails.
c) Scalable Infrastructure: Ensure that the underlying infrastructure can scale automatically to handle increased demand. This may involve cloud-based services and elastic computing resources.
d) Self-Healing Systems: Build in mechanisms for the product to detect and resolve issues on its own. For instance, a web application can automatically restart failed services or redirect traffic in the event of a server failure.
e) Security and Compliance: Develop robust security and compliance measures to protect the product and its data, especially when it operates without manual intervention. This may include continuous monitoring, intrusion detection, and data encryption.
f) Monitoring and Reporting: Implement comprehensive monitoring and reporting tools that allow you to track the product’s performance and intervene if necessary. This ensures that, even in an automated system, there’s oversight and control.
By architecting a product that can operate autonomously, you can reduce the need for manual intervention, increase efficiency, and provide a more seamless experience for users while maintaining control over critical aspects of the system.
Not knowing what meta data is being tracked about end users will lead to a good data product suffering unnecessarily.
Once you understand what Tableau is tracking and not tracking your dashboards will start to look very different. We have found that clients will stop consolidating dashboards once they understand that consolidation isn’t always the best use case for every visualization or dashboard.
Is your Tableau data product suffering?
We found teams will face these six problems while creating Tableau data products.
Complex Dashboard Design: The Tableau dashboards are overly complex and not designed intuitively. This complexity can make it difficult for users to comprehend the information presented. Improving dashboard design and data visualization techniques can enhance user understanding.
Limited User Insights: While you can track whether users are viewing a dashboard or not through Tableau Server metadata, it doesn’t provide deeper insights into what users find valuable or the specific aspects of the data they care about. To address this, you need to implement more comprehensive analytics and gather user feedback to understand their preferences.
Data Discovery Ambiguity: Users appreciate data discovery within the dashboards, but you lack a clear understanding of their preferences. Implementing user behavior tracking, click-through analysis, and surveys can help you identify what content and features are most relevant to your audience.
Assumption of User Satisfaction: Assuming user satisfaction without direct feedback is risky. Instead, proactively seek user input through surveys or user interviews to gauge their opinions and preferences. This will help you refine your data product to better match user needs and expectations.
Limited User Interaction: Users might only engage when something is wrong with the data product. To promote more regular interaction, consider implementing features that encourage user engagement, such as notifications, personalized content, and features like data alerts to notify users of relevant changes.
Long Remediation Time: Dealing with issues and bug fixes can be time-consuming. To address this, consider implementing better monitoring and alerting systems to detect issues early and resolve them promptly. Investing in continuous integration and deployment (CI/CD) can also help in reducing downtime and improving the agility of your data product maintenance.
In summary, addressing these challenges requires a multifaceted approach that involves redesigning dashboards for better comprehension and this can be accessed by connecting to more end user meta data. Actively seeking user feedback, and implementing user tracking and analytics. Additionally, focusing on improving the support and remediation processes can help ensure that issues are resolved in a timely manner, enhancing the overall user experience with your Tableau data products.
If everything is consolidated, how do you improve?
One quick way to generating a healthy data product is considering the user data. Once you get into that meta data it will teach you more about what to build in the future.
If your team consolidated everything into one view, how does your team know what’s being used most? What if ten of the options on the dashboard are never used but generate a lot of work for the team to support?
Interviewing end users and your Tableau developers is important to creating great data products, however what if there’s hundreds of thousands of end users, and ten thousand of them are non-developers? Using Tableau meta data is important and views on each dashboard offers instant access into what’s being used most.
User experience tip; By tracking what user “learn more” you can study their user journey. Learning more or “navigation drilling” offers a window into understanding what’s important to your end users.
A navigation button in Tableau and moving the end users from one dashboard to another will teach you what users care about.
Use navigation buttons to learn more about measure values, dimensions, and ultimately discover the questions they are asking from the data.
Then it’s up to your team to capitalize on that knowledge to make that data or knowledge easier to access.
navigation object in tableau dashboard
Features in Tableau Dashboards used Incorrectly.
If Tableau dashboarding features are used incorrectly, everything will be more difficult to support or easier. The future of support will depend how you use the features and your ability to build content that is easy to reuse; copy and paste.
Over using features in Tableau is a negative and there’s a large hurdle to jump each time you need to edit or make a simple fix.
Now, the simple fix, is not a simple fix. The simple fix requires everyone to fix the feature you implemented everywhere..
Understand the user experience.
Take this website for example, how do we know people are making it to the bottom of the page without a feature that allows them to jump to another page and then tracking that page?
In web tech things are different, in Tableau server you’re fixed to their platform tracking system.
Unless you embed the Tableau environment, however this will start to get technical very fast.
If you’re trying to implement technology that isn’t native, like implementing heatmaps, then we would know if users make it to the bottom of the page. However this feature slows down the speed of the entire website and may not fit for your Tableau Server environment.
Report consolidation and migration.
We know there’s a lot of demand for consolidating reporting and migrating to different SaaS products. We found once that ‘phase’ of business intelligence ends, companies start to see the reason consolidation is a negative or positive.
Consolidating dashboards using features is a big selling point because Tableau trainers choose to show these features in training and a lot of Tableau developers choose to build dashboards with features to help consolidated, however this is not a good usage of developer time if you’re unable to uncover meta data from the usage of the data products.
Consolidating user experiences in Tableau when building a data product limits your ability to understand what a user is using the most and forces interactions with end users beyond your data product.
This dashboard can work for profit, sales, and everything that can aggregate can be a fun dashboard to build, and insightful for many, however what if people are only using the profit pie chart of the dashboard? Building a user experience to uncover these questions from the end user will help you improve your tableau product development beyond report consolidation and migration phases.
A solution, using Colibri Tableau Dashboards
Our founder Tyler crafted Colibri after 10 years of building Tableau data products. Tyler, founder of dev3lop, worked at Tableau Software as a Full time employee before leaving to create Dev3lop | Consulting Services.
In Tyler’s words;
I added what I feel is an important aspect to generating meta data that will help you understand what is important to your end users who use the product, and it will tell you what data structures are being accessed the most. Drilling into a chart, better screenshots of the chart, and drilling into the data of a chart is the KEY of the entire solution. This amount of navigation gives you a huge foundation of clicks completed. Colibri can be your “template” and it doesn’t need to be tailored to Google analytics.
Today, we would like to highlight the functionality of Date Buckets, which is how we like to think of it mentally, and others call it Period-over-Period Analysis within Tableau Desktop. Both periods are buckets of dates and work great with min(1) kpi dashboards and often used in our Tableau Consulting engagements.
This blog delves into a method for date calculations to be used as trailing periods of time, to gain access to quick change between two periods in Tableau. In other words; We are focusing on identifying the last two periods in your data source, and the end user supplies a value to increase those buckets based on a date part you pick.
This approach enhances the efficiency and clarity of your analytical processes with Tableau and is easy to re-use. There are many ways to write this calculation and this is one way to write the calculation.
between dates filter
In Tableau this between date filter will create two calendar inputs, most executives don’t want to click anything.
It only takes 3 steps to build self generating, automated (not static set filters), date buckets in tableau desktop that trail with your max date in the date column [w].
lol, type this stuff or paste the code coming from this tutorial.
Below please find my quick win tutorial as a means of quickly winning… on any Tableau workbook with a date and a parameter.
We will be using the SuperStore Subset of data.
Which comes with every license of Tableau Desktop. In your data, you probably have a date. Use that date and follow along with these next two steps.
To begin, you need a date, and a parameter.
Step 1, make a date variable named W.
Create a new calculated field in tableau desktop, call it W.
make a simple variable W in place of your date. your date goes in this calculated field.
Now make the parameter.
Step 2, make a parameter variable named X. It’s an integer.
This will be the number of ‘X’ per period of analysis.
make a simple variable X in place of your parameter.
Paste the calculation below in any workbook with a Date and Parameter.
Above, if you followed along, you will not need to make any major changes to the calculation.
if DATETRUNC('month', [W])> DATEADD('month', -([X]+ datediff('month',{MAX([W])},today())) , TODAY()) then "Current Period" //make this 0 elseif DATETRUNC('month', [W])> DATEADD('month', -([X]*2+ datediff('month',{MAX([W])},today())) , TODAY()) then "Previous Period" //make this a 1 else "Filter" //make this a 2 END //[W] = date //[X] = parameter
Drag drop this on to the view, right click filter, filter filter…
Now, only two buckets of time are available. You’re welcome!
Automated period over period analysis in Tableau
You’ve just implemented automated date buckets in Tableau, allowing end-users to control visualizations using the bucket generator. Personally, I find the tool most effective when using it in a daily context rather than a monthly one. However, the monthly option provides a convenient way to encapsulate dates within distinct periods, while the daily granularity offers a simpler and more immediate view.
Having a rapid date divider or bucket automation at your disposal is highly advantageous. It empowers you to visually highlight disparities between two date periods or employ the calculations for logical flagging, subtracting values, and determining differences, all without relying on the software to construct these operations through window calculations.
Optimization date buckets or period over period in Tableau
Optimization #1: remove LOD calculations
Nothing against LOD calcs, except they are slow and built to help users who don’t know SQL.
{max(W)} seeks to find the max date, you can find it easier using a subquery in your select statement. If you don’t know what that means, ask your data architect supporting your environment to add the max(date) as a column, and have it be repeated per row too. They will know what to do or you need a new data architect.
Optimization #2: stop using % difference or difference table calculations
Nothing against table calculations, except they are slow and built to help users who don’t know SQL.
Optimization #3: change strings to integers.
Nothing against strings, except they are slow.
It’s likely not your fault that you’re using strings in 2018 with if statements, it’s probably because someone taught you who also did not know how to write optimized Tableau calculations.
Optimization #4: ‘month’ date part… add a swapper.
The Datetrunc is used to round the dates to the nearest relative date part, that’s just how I explain it easily.
Date part can be a parameter.
DATEPART(date_part, date, [start_of_week])
NO I Don’t mean the Function Datepart.
DATETRUNC(date_part, date, [start_of_week])
YES I Mean Date_part, which is scattered in the calculation and easy enough to replace with a parameter full of date_parts. Now end user can play a bit more.
Optimization #5: remove max(date), add an end date parameter…
Remove {max(date)} or the subquery of max(date) explained above because you can give your end user the opportunity to change the end date using parameter.
As a company spokesperson noted, the new tableau dashboard that helps users visualize Google analytics was invented out of necessity.
“When the team at Dev3lop first started blogging on knime.dev, dev3lop.com, and other websites, everybody quickly realized that their data was disappearing and that they were not tracking it collectively,” the spokesperson noted, adding that this inspired Dev3lop to begin building out a process to bring all of their data into one dashboard.
“Also, because the free reporting tools that are available are a bit limiting in terms of helping people understand their traffic collectively, the new tableau dashboard was created to allow people to see everything at once, without having to swap tabs.”
The new analytics tableau dashboard is a free download that is readily accessible to anyone who would like to use it. The Colibri end to end solution will allow people to research their end user website patterns, which in turn will help them to better understand the major search engine’s analytics properties.
The measure values used in the Colibri Google Analytics Tableau Dashboards includes the time, in seconds, that a user spent on a particular page, as well as unique page views, which is the number of sessions during which the specified page was viewed at least once. The total duration of user’s sessions, total number of sessions, and time on screen are also measured thanks to Colibri, along with other values.
Dev3lop is excited about the recent launch of the Colibri analytics tableau dashboard-which is Spanish for “hummingbird.”
“Just as the hummingbird is essential for plant reproduction and genetic diversity in the plants they help pollinate, as we improve the tableau dashboard, we will continue to release new and alternative versions to help people improve the diversity of their reporting ecosystem,” the spokesperson noted.
About Dev3lop:
Dev3lop.com is a grassroots tech startup based out of Austin, Texas. They offer tailored consulting solutions to their customers across an array of services, with a major focus on data analytics and tableau consulting service engagements. They have also launched a new task scheduler software called Canopys. For more information, please visit https://dev3lop.com.
When building a data source on Google Sheets in Tableau Desktop 10mb is the max per Google Sheet. However, what if we could connect to more than one Google Sheet at the same time?
Google Sheets works wonders with Tableau Public because it allows for Tableau Public to read data from Google Sheets once per day. This enables everyone the capability to use an online cloud data source to update their Tableau Desktop dashboards.
Introduction
In this blog, we will discuss connecting to multiple Google Sheets in one connection. If “large sheets removed” is not sufficient, and you’re willing to break apart your sheets into multiple sheets manually or with an engineer, we will find this article helpful. We break apart each element, how it works, and explain how your engineer may begin breaking down the solution.
Tableau currently has no built in feature to allow this to happen, however they do have a feature you can setup to make it automatically connect to Google Sheets! Tableau suggests this isn’t possible and the only way to make it work is to use LESS DATA, but what if you have big data?
We built this blog to help you begin the journey of connecting to many sheets. You will want to demo this as a possible solution to show your engineering team to automatically create these Google Sheets (we are here to help too).
Error explained
If you begin connecting to a Google Sheet in Tableau Desktop >10mb, you will see various popups, depending on your operating system, explaining an error has occurred with Google Sheets.
Unable to complete action error message
Did you recently see this error?
Unable to complete action The Google Sheets service reported an unrecognized error when processing this request. This file is too large to be exported.
An error occurred while communicating with Google Sheets, <10mb Google sheets error message in Tableau desktop.
A good question to start asking, “will my data split into other sheets easily?”
In the example below we are going to speak towards an 80mb Google Sheet that will not work in Tableau.
TechWorkaround explained
The Tableau Desktop Wildcard (automatic) feature will capture Google Workbooks (contains google sheets) and google sheets in a workbook(s). It will “automate” building connections to multiple 10mb workbooks or sheets, by establishing a stack of data that will resemble your end goals. Similar to using Union All in SQL.
Stone Working Analogy
If you’re unfamiliar with union in SQL, think of your data stacking on each other like bricks on a brick wall. Data engineers and brick wall experts have similar jobs. They are solving a problem by stacking side by side or on top of each other.
We will focus on a “workbooks” example, where we will stack bricks of data on each other.
Using a matching pattern(xxx*), we are able to union similar named data storage.
Example; there are four regions in our data, each region is <10mbs, about 80mb total.
Four regions:
SOUTH 10mb
NORTH 10mb
EAST 10 mb
WEST 50 mb*
A Use Case Explaining the Solution
Step 0; union means the sheets need the same column headers. Thanks for learning about unions!
Step 1; build 8 googlesheets… (new workbooks, not new sheets, this works with sheets however I’m using workbooks for now)
Step2; name each google sheet workbook “Table-Demo_EXAMPLE” etc… and you will have the following.
Table-Demo_SOUTH
Table-Demo_NORTH
Table-Demo_EAST
Table-Demo_WEST_1
Table-Demo_WEST_2
Table-Demo_WEST_3
Table-Demo_WEST_4
Table123-Demo_WEST_5
Protip; Table123-Demo_WEST_5 will not be included in this exercise because it’s not named Table-Demo_. Wildcard allows you the ability to filter to the things you need. If you name your Google Sheets “Table-Demo_” our wildcard solution automates connection to that google sheet, there’s no need to connect to the extra google sheet if you’re setting up the solution as explained.
Now that we have an understanding of how a wildcard will work, let’s discuss the end to end.
How to setup >10mb union
To increase size of google sheets greater than 10 megabytes, and increase your overall Google Sheets insights in tableau desktop, you need to get good with the Union wildcard!
Connect to the googlesheet. Tableau desktop made this workflow a one click button on left side of opening screen. Requiring two clicks in total.
Walk through the Google authentication, choose which email with many similar tables for wildcard. This means you need to go and change the names of the Tables you wish to put together.
The renaming part needs to be a part of an automated process, you may want to do, using the Google Sheets API also known as the Google API, we found success automatically creating a new Google Sheet, and automatically naming the sheet similarly, which improved a client engagement during a tableau consulting engagement that had a lot of data engineering consulting to generate the solution. If data is constantly morphing, there may be a need to delete old sheets, we found clearing the sheet and re-populating data was the easiest method for fresh cleans. However lets get focused on the manual process because it’s a similar architecture. We found naming tables differently between tests helped us with testing/troubleshooting, and found Google Sheets had some strange hiccups that are easier to avoid by removing old tests completely.
Discussing Data Structure tips for >10mb Google Sheets
Here’s a good time to start making sure column headers are the same. If not it will continue to make a column, which will lead you down the path of learning how to solve for dynamic parameters due to string values being many to many.
Convert to union…
Very important step, drop down carrot and find the Convert to union click.
This workaround allows you to connect once, to all sheets similarly named (using wild cards) VS connecting to all the different google sheet workbooks. This allows you to remove many data sources and transition into one data source.
The Wildcard Union Screen in Tableau
Tableau offers a feature to union more than one google sheet together, which enables users to quickly build large cloud data storages on Tableau public, or internally.
Example; Tables-Demo_* will find anything with Tables-Demo_ as the start of the sheet name.
Helpful related resources and documentation.
Below are documents, notes, and community posts from other Tableau authors.
The 10mb limit with Google Sheets is ambiguous when testing the number with true CSV file sizes and better to determine a way of “stopping” the data before it gets big.
Some interesting things to think through, we found 7mb, 10.3mb, 12.9mb, and 19.1mb CSV files coming from single Google Sheet connections and no popup error stopping Tableau Desktop from connecting to the data. Don’t consider this size to be your break/test.
Screenshot demonstrating various CSV files downloaded from Google Sheets – Tested Oct 28, 2021
Good to note; This is the size of the csv when downloaded via the Google Sheets/Data/. Your team may get hung up on this process, and we found it’s better to focus on a row count if you’re not using full stack engineering to complete the task.
Thank you for reading. If you are interesting in implementing a process that uses Google API, contact us to learn more.
Researched & Written by, Tyler Garrett founder of Dev3lop | Consulting Services.
