It’s time for another Quick Tip in Tableau Desktop!
Have you ever had a pesky data set in Tableau that has Year, Quarter, Month, Day, Minutes as separate fields? This structure can be useful for certain analyses, but a pain-in-the-butt for other analyses that just need a single regular date field.
Have no fear! There is a simple fix for this.
It’s called the MAKEDATE function and it helps you make a date out of the fields you have.
Here’s a quick, soundless video that shows the function:
It’s as simple as this: MAKEDATE(year, month, day). That’s it. Now you have a continuous date field that behaves how you would want your date data to behave.
A Final Note
Tableau can actually “MAKE” a bunch of useful calculated fields that enable tons of analyses. I like to call these the MAKE-it-happen family… mostly because I can’t think of a better name (suggestions are very welcome). They include:
They’re transformative both literally and figuratively by enabling developers to convert data directly in Tableau. Things like MAKEPOINT and MAKELINE had to be done outside of Tableau back in the “good ole days”. But fortunately, it turns out that we’re in the good ole days right now and they keep getting better! Queue a great The Office quote…
Thanks for reading!
Success! You're on the list.
Whoops! There was an error and we couldn't process your subscription. Please reload the page and try again.
Sometimes Tableau’s UI can be misleading. If you’ve had the NULL values indicator pop up in the bottom-right corner of one of your worksheets, then this is one of those misleading times.
A simple click on the indicator would suggest there are two options to handle NULLs. The first would be to filter the data. The second option is to show data at the default position.
This window is standard for non-geographic worksheets. Geographic worksheets will instead show this window:
But there are actually other options available outside of these!
The Quick Tip
Let’s say you want to leave everything as-is, but don’t want to have that ugly NULL bubble in the bottom right corner of your worksheet view. The way you can do that is LITERALLY THE EASIEST THING EVER. Yet I didn’t learn this until a couple years until my Tableau journey. Ok, now don’t be mad when I tell you this…
Right click the indicator and select Hide.
Yup. That’s it. Here’s a gif of me doing it:
Be aware that Tableau will choose the specific behavior of hiding the indicator. This means that for something like line charts, it could hide the indicator and keep the line connected. But if you wanted to control that behavior and break the line instead while hiding the indicator, you can use the method described below.
Another NULL Formatting Tip
There’s an additional method to handling NULL values in Tableau. This involves changing the formatting of the specific field.
Right click any field, and select Format.
Click on the Pane tab of the Format window.
At the very bottom you’ll have a Special Values section for Text and Marks. Here you can enter an alias for the text values or select from one of four dropdown options for the Marks.
That’s it. Hope this tip helps! Please send me an email (dan@mywebsiteURL) if this post helps or if there are other topics you’d like covered!
Success! You're on the list.
Whoops! There was an error and we couldn't process your subscription. Please reload the page and try again.
Did you know that Tableau Desktop has the option to filter the data pane by field types on top of just their names? With the introduction of data models in Tableau Desktop, I see a lot of users frustrated with the seemingly crowded data pane. Before the data model, you used to have dimensions and measures in separate sections in the data pane, but now everything is grouped by tables of your data source. The good news is that Tableau actually still has an intuitive way to quickly see the fields you need.
As of 2020.3 you can filter your searches by 4 different queries:
C: filters for calculated fields
M: filters for measures
D: filters for dimensions
F: filters by comments
By typing C: or any of the other options at the beginning of your search, you’ll filter all of the fields based on that category. After the type filter, just put the field name you’re looking for to filter further.
There are technically two ways to filter your searches. You can manually type in the values above, or you can use the filter button located to the right of the search bar. Take a look at the GIF below to see.
You might’ve come across my previous post about fixing empty layout containers in Tableau. That post was about fixing a specific bug when everything else is configured correctly. This post is about a few common configuration issues that are usually the issue (before encountering the bug I’ve previously discussed). Go through these bullet points to make sure they’re configured correctly before trying other solutions.
You have a horizontal or vertical layout container. But the elements don’t automatically size correctly. Either a gap is left at the end of the container, worksheets don’t swap like they’re supposed to, or the worksheets don’t fill up the container.
1. Make sure the elements aren’t set to Fixed Width or Fixed Height
By activating the objects inside the container, you can see if the width or height is fixed. The little push-pin will be filled in white if width or height is fixed, or have no fill if it’s a dynamic width or height.
Something to keep in mind: You might not see the Fix Width or Fix Height option depending on your dashboard layout, and settings for the container. If you have “Distribute Contents Evenly” checked, these won’t show up. If your container is nested in other Tiled containers or multiple other containers, they might not show up either.
2. Activate each sheet in the container and set the sizing to Entire View, Fit Width (if a horizontal container), or Fit Height (if a vertical container)
Make sure each worksheet object is set to fit to the appropriate dynamic option. There are two places you can do this on the dashboard.
In the dropdown menu of the worksheet object:
Or in the bar at the top of the window:
The following points are relevant if you’re doing a sheet swap
3. Hide all the titles of the worksheets
Right click all of the sheet titles in the container and hide them. If you need them to be there, create a text box with dynamic text based on which sheet is visible.
4. Make sure there is a discrete field on either your rows or columns for the sheets involved in the swap
Go back to the individual worksheets that are in your container. Make sure they have a discrete pill on rows or columns. This should be a static field with a single value and should be set as discrete. I use a static field with the value MIN(0) set as discrete. Then right click the field and deselect “Show Headers”. This will make the field disappear from the view.
I don’t care what anyone else says when it comes to the most important Tableau concept. Ok, maybe that’s a little harsh… but for me, there is one that trumps all. This concept drives every single calculation and visualization on a dashboard. If a developer doesn’t understand it, it will cause wildly inaccurate analyses to be published. It’s the most frequent cause with newer developers as to why Tableau is “doing this weird thing”.
It’s the number one issue I come across when troubleshooting other peoples’ dashboards. It’s also the number one thing that beginners struggle with. As soon as a beginner masters this concept, it immediately catapults them into intermediate or expert territory. This is because of the creative options and intentions it opens up. So what is it?
Level of Detail
That’s right, you’ve heard it before. If you haven’t been around Tableau long, you’ll probably think it’s just those fancy expressions you’ve heard about but try to avoid using. Unfortunately or fortunately, it’s actually much bigger than just level of detail expressions. It’s the entire conceptual framework that impacts how and where data is aggregated and rendered in Tableau.
In case you skimmed over it, let me repeat. Level of detail is the entire conceptual framework that impacts how data is aggregated and rendered in Tableau.
The terminology here might be a little different than what is expressed officially by Tableau. Tableau talks more about aggregation, granularity, etc. For me, the level of detail concept encompasses these other concepts. At the finest level of detail, you’ll have no aggregation and the highest possible granularity. At the broadest level of detail you’ll have the maximum possible aggregation (like table calculations) and the lowest granularity. Ironically enough, granularity actually has the words “level of detail” in its second dictionary definition.
So ultimately, level of detail is the result of the combination of aggregation and granularity settings on your dashboard. You can have a row level of detail, an aggregate level of detail, or a table level of detail. Depending on your calculated field, you can force aggregation at a certain level of detail.
Why It’s So Important
There are many subtle and not-so-subtle ways Tableau allows a developer to control the level of detail in which data is aggregated and rendered. Through calculated fields, a developer can make:
relatively static calculations using Level of Detail expressions
this is explicit control of the Level of Detail displayed by Tableau
dynamic calculations that take place at different levels in the data
this includes selecting the level in which table calculations compute and are relative to
through the “Aggregate Measures” option in the Analysis menu
Knowing exactly how Tableau will handle aggregations and granularity will tell you exactly what level of detail your analysis is taking place. Is your measure being aggregated? Is it aggregated by a specific dimension? Is your dashboard visual being rendered for the entire data set or just the current year? There are tons of situations where you have to know what level of detail will be calculated and displayed on your dashboard. That way you can know that it is time for a fixed level of detail or not.
A common error I see with beginners is a claim that “Tableau is calculating things wrong”. They’ll run a calculation in Excel to check an average or sum after filtering on a column. This won’t match what Tableau is displaying. 99% of the time it’s because Tableau is aggregating at a different level of detail than expected because of the dimensions on the dashboard.
Note 2: Updated again on Aug 13th, 2020. URLs work again in Tableau Public but this CSV export method does not work in Tableau Public. It still works in Tableau Server and Tableau Online.
Tableau has a standard out-of-the-box way to download data from workbooks (if permissions permit for the user). But for new users who are just clicking on a URL to view a dashboard, they don’t want to learn new menus to figure out a simple export. So if you want to give an easy, 1-click option, you can add a button with a link. This can be configured to download specific worksheet data or the full data set. For this demo we’re just going show how to download the whole data set from the first sheet displayed on the dashboard.
Unfortunately, URLs aren’t working in Tableau Public as of this publishing, so you’ll have to try it out on your own server.
If you’ve been using Tableau Desktop or Tableau Public to design workbooks, there’s an issue you most likely have run into while configuring layouts. In fact, there are several posts in the Tableau Community Forums trying to get an answer to this exact problem. All remain unanswered. Post one, post two, post three. As a note, the last version I’ve used as of this post is 2020.1 and the issue was still present on certain dashboards.
Fortunately, I have a solution for you!
To preface this solution, it seems as if this is actually a bug in Tableau, and this is only a workaround to that bug. Hopefully in the near future this is fixed and this post becomes irrelevant!
You have a horizontal or vertical layout container. Your worksheets for the container are configured to one of the proper dynamic sizing options (fit height, fit width, or entire view). Yet when you put these worksheets inside of the container, there is always a little empty space at the end of the container.
It looks like a little grayed out area with diagonal slashes through it. Like this area highlighted in red (this photo was borrowed from the first linked Community post above):
To preface, before trying the below solution, you should explore the easier and more common fixes first. Most of the time the common fixes cover some small configuration issue that can cause this blank space to appear. These common fixes are:
Make sure you’ve configured the worksheets to fit automatically with either Fit Width, Fit Height, or Entire View
Clear any manual sizing for the worksheet
Remove the sheet and add it again
If none of those work, there is a another solution. This involves a little bit of switching back and forth. Although you probably have the worksheets configured to one of the dynamic options, Tableau is actually using the manual sizing of your axis to determine the maximum size the worksheet will take on when dropped in your container.
So what does this mean? This means you’ll have to do the following in order for your worksheet to fill the entire container.
Go back to the worksheet(s) you want to fill the container with
Toggle the worksheet fit to “Standard”
Manually size the x-axis (if it’s in a horizontal container) or y-axis (if it’s in a vertical container) to be larger than the container you’re dropping it in
Toggle the worksheet fit back to one of the dynamic options (Fit Width, Fit Height, or Entire View)
Ta-da, go back to the dashboard and view your container. The blank space should be gone
As a note, this sizing issue usually only happens when discrete dimensions are on the axis that determines the container fit (x-axis for horizontal and y-axis for vertical). I have not seen it happen in circumstances outside of that, but the fix should be the same. But what if the sizing issue is happening in containers that only have filters, parameters, legends, etc.? Let’s cover it in the next section.
A Related Problem and Solution: Containers with Filters, Parameters, and Other Static Things
So what if this same sizing issue is happening with a container that just hold filters, parameters, legends, or other more statically-sized elements?
The recommended action here is to manually set the height or width of the element to equal the height or width of the container. You can do this by clicking on the drop down arrow for the element, then clicking on “Edit Width” or “Edit Height”. Make sure to use the Layout tab to first find the size of the container you’re trying to fill.
1. Using the layout tab, select the target horizontal or vertical container you’d like to fill.
2. Click on the dropdown for the element you are trying to make fit the entire container.
3. Set the pixel value equivalent to the height or width of the container you are trying to fill completely.
That’s all there is to it! Let me know if you found this useful or if this didn’t solve the issue for you.