FYI, the way I usually deal with this situation (where I always want to
show a value no matter the underlying data) is to pre-process the Tableau
data data source to pad it out in advance of Tableau so that way there's at
least one record for every combination of dimensions that I need (in fact
right now I'm taking a break from building a query to do just that).
On Thu, May 5, 2016 at 10:36 AM, Namrata Sawant <
Another great thread response from Mr. Drummey -- love it!
I'm wondering if anyone has ever seen the data in your table duplicate when you add in the Null Lookup.. Once I added that in, I am able to get my table to show 0 where there previously was no data, but then all of my rows duplicate numbers. I am unable to attach the book I am working on as it is proprietary information to my company, but something like this....
Department In Out
A 10 1
C 20 1
D 15 5
After Adding in Null:
Department In Out
A 10 1
B 10 0
C 20 1
D 15 5
You'll notice that I now indeed have 0's but all my data in the rows are duplicated. My Null Lookup uses the formula LOOKUP(COUNT([Dimension]),0) but I also noticed in the calc box it says "results are calculated along table (across)" and I'm wondering if that has something to do with it? I am newer to Tableau and realize this may be vague but appreciate any help I can possibly get!
Sara - did you ever get a response? I have the same issue.
Hi Danny (and Sara),
The information provided wasn’t enough for us to give an answer… We’d need to see at least a screenshot of the view (including Rows, Columns, Pages, and the Marks Card) and know the addressing (compute using) of each table calculation.
There's actually another way to do this, if adding table calculations is not something that you want to do. What I did was as follows, assuming the following: [field2] <-calculated from [field1] <-calculated from [field] or expression on [field].
(1) use either IFNULL(sum([field]), 0) or ZN( sum([field]))--or IFNULL([field]) or ZN([field]) if the expression isn't an aggregate computation--in generating the calculated field [field1] as part of the expression assignment (perhaps embedded in some IF THEN ELSE logic, for example); and then,
(2) instead of directly using this calculated field [field1], create a 2nd calculated field, [field2], which references the calculation of the 1st one, [field1]. It is the calculation of [field2] that will be the one used in the data rendering (tabular, graph, etc.), rather than the primary calculation of [field1]. It is in this calculation of the 2nd field where I placed the IFNULL([field1], 0) as the sole content of the calculation for [field2]. Then, instead of using the original [field1] in rendering, use [field2]. This displays zeroes instead of empty cells.
Note that the use case for this is forward-looking values, such as rendering Current Year Financial/Sales/Widget numbers, Jan - Dec, where in any given current viewing date (say, controlled by a month/year based Parameter drop down menu), you don't have any data available to render a valid value. For users, empty cells are not acceptable; therefore, you need to be able to display zeroes instead--a more user friendly rendering.
It may well be that the use of IFNULL(), ZN() functions on [field] to calculate [field1] may not be necessary, and the significant factor is the use of IFNULL() against [field1] to generate [field2]. I didn't test that aspect of the solution.
I'm using Tableau 10.x for this. Maybe this didn't exist in older versions.
I really need a solution for this too. Has anyone discovered a solution? My problem is that I can change some of the columns to not show "Null" in "Edit Alias...". But then I reload the spreadsheet with changes in it and it goes back to showing the "Null". Also, it doesn't work for all columns.
Can we look at your packaged workbook to understand what you are doing and see if someone can help?
I'm somewhat new to Tableau, so I don't know that this will solve your specific issue, but rather than creating calculated fields, etc, I just went to the the measure values, clicked "Edit in Shelf", then encapsulated the aggregation with a couple of the recommendations from this thread and it worked (a million thanks to those folks!):
This has worked for all of my worksheets so far and I only have to edit the formula in that one place.
Note: if the name of that measure value appears in the worksheet I then I do have to use the "Edit Alias" function so that it isn't showing that entire calculation.
I am a first time participant in the community and have found that I am experiencing this issue as well! Particularly when using additional marks on color.
It appears that even though you are forcing the 0s using ZN or Lookup, when a color measure is added the value is still treated as "Null" and will negatively impact your visual. Please see the screenshots I've provided.
The Rate calculation provided is using the ZN function, but I have also tried using the methods above (i.e. LOOKUP, using a 2nd field to reference the 1st calculated field). The color measure is needed to identify whether the value calculated falls within Target, Threshold, or Distinguished. I had to add the same measure used for the color mark to the rows or column shelf to identify the issue.
The desired goal is to use the color mark with the provided ZN or look-up functions so that it keeps the integrity of the calculation. Since this calculation is aggregated over a date range, the issue only occurs for those selections that had months of null data (even though it is truly 0).
I apologize in advance if I have broken any community posting rules, but any insight is much appreciated!
Before Color Mark
Color Mark Added
Mark added to Color and Row Shelf identifies what is causing the issue
Benchmark Color Calculation (I am wondering if there is something that can be corrected within the calculation) or if this is a caveat of using the ZN/LOOKUP function).
Hi i have data for jan,feb,may,apr,may,oct,nov of 2018 .When i select the option of show missing values i get the months jun,jul,aug,sep as well then using the above mentioned lookup calc i'm showing null values as 0.In the same way i have to show for the month of December, but december is missing ? The client wants to show all the 12 months with/without data
1 of 1 people found this helpful
Sorry for the late reply on this. Tableau's data densification features (of which Show Missing Values is one) only pad within the range of dates that are in the data. Therefore you're going need to do some padding outside of Tableau to get the desired results.
Yes I understand Thanks Jonathan
Thanks so much for your enlightening posts on this topic (also Joe Mako's). Your detailed clarifications are so helpful.
Not sure if this is a "special case" of data densification or is covered by the rules/principles you describe - however, I don't know how to force densification on in this common situation.
The attached workbook is an example. In my crosstabs I regularly use the "Dummy Measure" workaround to produce axes that allow flexibility in formatting. When I do this, I can't seem to get densification to work. Any thoughts appreciated. Thanks!
Your view is densified…if you right-click in the whitespace in the view and choose View Data… you’ll see 4 rows. You can also see the densification if you remove the MIN(0) Dummy Measure pill.What’s happening is an order of operations issue: In the view you have a continuous axis with the Dummy Measure that has a value of MIN(0), which is a regular aggregate computed before densification, so since Tom/Feb 28 doesn’t exist in the data there is no value for the Dummy Measure so Tableau doesn’t plot anything at that location.
If you wrap a ZN() around the MIN(0) then the ZN() will be computed after densification and that additional mark will be displayed.
This is a really old thread and in earlier versions of Tableau if we didn’t have data preparation capabilities we had to do more densification in Tableau, and as you can see from this question the densification depends on understanding order of operations and other aspects of Tableau. Nowadays with cross-database joins, join calculations, Level of Detail Expressions, and Tableau Prep we’ve got a lot more ability to pad out the data within the data source (vs. densification that is in the view) I find those work a lot more reliably than depending on densification features.