All tagged Tableau Calculations
FIXED Level of Detail Functions (LODs) let you specify the level of detail you want to aggregate a particular measure at. This allows us to work around the natural limitations of the level of detail in the worksheet we're working in.
FIXED LODs are a great place to start as they are the definitive, essential LOD. The two others (EXCLUDE and INCLUDE) are less common, but functional in their own unique ways. If you want to simplify and just learn one LOD type, FIXED is the one for you!
Do you need to publish public Tableau reports but suppress small populations because of sensitivity concerns?
If you work in healthcare, education or another government entity, there's a good chance your Tableau reports contain sensitive information that needs to be suppressed. For example, many organizations we work with need to suppress populations when they are less than five. For example, if we filtered a courts dashboards to see how many court cases there were in Whatcom county for 12-14 year old, Pacific Islander females in 2017 and there were three cases, we would need to suppress that figure because it's less than five.
Adding comments to fields in Tableau is important to document your work and keep things organized!
For example, you might have two fields with similar names! Adding a comment to them can help you quickly distinguished them in the data pane.
Have you ever seen the error: “Cannot mix aggregate and non-aggregate arguments in this function?” If you’ve worked with Tableau for any period of time, you probably have! You could have seen this in an IF statement, a CASE statement, or just a regular calculation!
The big idea is that we can’t aggregate part of our calculated field while not aggregating another part of the same calculated field.
This is because aggregated functions run at a "summary" level, while a non-aggregated function runs at a “row by row” level. So, when we try to use these in one function, Tableau gets confused at exactly how to execute what we’re asking!
In Excel, we can use Text to Columns to split a string. But what about in Tableau?
Tableau has a built-in function called SPLIT() that allows us to split strings into multiple columns. We’ll take a look at how to indicate to Tableau the number of columns we want to see and how to understand where to split the string.
Want to see how to split names, email address or phone numbers to isolate the meaningful information from the text string? Check out these examples!
Do you want to use Tableau to look at running headcount over time? For example, you work with employee level data which has Start Dates and End Dates and you'd like to be able to see how the total active headcount at the company has changed over the last 8 quarters.
That's something Tableau can do, but it isn't going to work out of the box. It's going to take some creative data structuring and calculations to get things working.
Do you ever have a data set with US State names and it would be really handy to save some space in your visualizations by displaying state abbreviations instead of full state names? Or how about a situation where you want to display country abbreviations instead of the full country name? This happens to me often!
It feels like every year or two I run across a need for this and end up writing the same calculation from scratch over and over again. No more! This video shows how to write calculations which convert state and country names to their abbreviations and provides a Tableau Workbook with the calculations you can copy and paste and never have to write them from scratch again!
Does your data set have multiple date fields? For instance, maybe you have both an "Order Date" and a "Delivery Date". Or maybe you have both a "Hire Date" and a "Termination Date".
Sometimes, you need to filter on both date fields at once to answer questions like, "How many people were hired and how many people were terminated in the Marketing Department this year?"
Sometimes, you have too many values in a dimension on the rows shelf in your Tableau worksheet and end up with a scroll bar. Annoying, right? Scroll bars mean your users are less likely to see the data at the bottom of your worksheet because it it out of sight and out of mind.
One idea I've heard discussed is, what if you could break the data into multiple columns? For example, instead of displaying 50 states as a single column of 50 rows, could you display it at 2 columns or 25 rows?
Did you know you can reference Tableau Sets in calculations? Most Tableau users don’t.
Referencing a set in a calculation provides you the ability to filter a calculation, rather than an entire worksheet, based on end-user selection.
Why is that valuable?
Many organizations report against a fiscal calendar. If your organization's fiscal year doesn't start in January and you've tried to display values against fiscal periods in Tableau, you probably know exactly what I'm talking about.
When fiscal date periods are being compared, it's usually Fiscal Years or Fiscal Quarters. Let's imagine you are asked to build a chart which compares business segment values from the current Fiscal Quarter to Date versus the same period last year. Where would you start?
Let's say you want to filter a Tableau worksheet to only contain data from the latest date in the data set. That's pretty simple if the latest date is consistently today or yesterday. You can just use a relative date filter! But what if the most recent date in the data set is less consistent?…
Tableau is a great tool for operationalizing time period comparisons. One of the classics is comparing quarters.
For example, you might want to know how this quarter compares to last quarter. That's a great way to compare values if your business isn't very seasonal. However, what if you sell sunscreen? You can bet that comparing fall to summer is always going to look like a bummer.
Aggregations are functions which allow you to consolidate numerous rows of values into a single output. SUM, AVG, MIN, MAX, COUNT and COUNTD are some of the most used aggregations in Tableau.
Aggregations play an important role in Tableau calculations. If they aren't used properly, calculations will return incorrect results!
Do you want to keep all data associated with a value which has multiple records in a data set? Maybe it's how many students attended multiple schools, how many patients visited a hospital multiple times or how many customers made multiple purchases.
Do you need to calculate tenure in Tableau? You're probably looking for the DATEDIFF function! You indicate your date unit, a start date, an end date and let Tableau do it's thing. Simple as that, right?
Not necessarily!
