From the top menu, select Analysis > Table Layout > Show Empty Rows. This will give you a row for all values of Prior Insurance Group that are present in the data (or context, if you add a filter to context). However, all values in the pane will show up as blank (in fact, there will be no mark at all) because the data literally does not exist (it's not even NULL -- it's just not there).
Edit: This is specifically for the 3mm table.
Hope that helps!
Here's a solution to problem 1. If you create a calculated field that looks like this:
LOOKUP(COUNT([Policy Number]),0) and use that in place of [Policy Number] on your marks card then it will perform a table calculation that gives you either your target data or null. It will look the same at first, but don't worry! We can right click on the data in the viz and hit Format. From the Fields dropdown, select CNT(Policy Number) and take a look at the Special Values section down at the bottom on the left pane of the screen. In the "Text:" box enter 0% so that those null values are represented just the way you want them.
If that helps mark this as correct and let me know! Good luck!
Try Using IfNULL may be it will help.
1 of 1 people found this helpful
This will only work if the data is actually null. There's a difference between no data and null data, and in this case the data isn't even there to begin with. That means it's technically not null, so using an IFNULL wouldn't return anything.
Right click Lookup in the Measures list on the left. Default Properties -> Number Format. Go to Number (custom) and bump the decimal places down to 0, then put a % in the Suffix box. Voila!
By the way, if the data isn't matching up to what you think it should, right click Lookup in the Marks card and play around with the Edit Table Calculation dialogue box. The Compute Using may be off, but that should be where you find your expected results.
2 of 2 people found this helpful
Your getting great suggestions from every angle! And I think Lisa and Vincent are on the right track with their suggestions for problem #1.
For #2, see my proposed solution above. There is no way I am aware of to get a 0% in that one, but you can at least get all rows to show.
But for #1, I would simplify the approach as follows:
- Use Lisa's first suggestion: ZN(CNT(Policy Number))
- Additionally, create a calculated field with the code Index() and add this to the Detail of Marks.
What Index() does is enable "data densification" and the missing data gets filled in with NULLs that you can change to 0s with ZN().
Edit: VIncent's approach also enables data densification and will work. The complexity here is that it's not just LOOKUP(COUNT([Polity Number]), 0) it is LOOKUP(COUNT([Polity Number]), 0) / TOTAL(COUNT([Policy Number]) and you'll have to set it to compute over the whole table (either explicitly or implicitly). I would find this a bit more complex, but it's a great thought process and works too!
Hope that helps!
Thanks that was a simple solution. I will have to figure something else out for my second question.
Here are a couple of alternate approaches for #2:
- Create a data source that has includes a row for every provider for every month (and multiple rows for each potential filter field - e.g. TSL Number). You might even be able to accomplish this fairly easily using Tableau 10's cross database join.
- Create a simple data source that just has a provider column with every possible provider. You can then use that as the primary data source to show rows for all providers and then blend to your existing data source on Provider and use calculations to get the count of providers from the secondary source (or zero if no match is found).
The difficulty with the second approach is that the filters I see in your view above are going to be problematic (because they are global to the data source if it is a primary data source and not secondary). The first approach could work well, but will take some effort to setup and some care to ensure that you don't artificially inflate values.
One more possibility for #2, and this one should be a "little easier".
- Take your original view (with all the months) and duplicate it.
- Remove CNT(Policy Number) from Text
- Create a calculated field called Last with the code Last() add it to the Detail of Marks, set it to Compute Using > Month (using the drop down on the field)
- Create a calculated field called 3 Month Total with the code WINDOW_SUM(IF [Last] <=2 THEN SUM(1) END)
- The Last calculation will ultimately give us the last 3 months (values of 0, 1, and 2) so 3 Month Total will be the sum of the 3 months
- Create a calculated field called All Provider Total with the code: WINDOW_SUM([3 Month Total])
- Ultimately this will give us the total for all providers for the last 3 months
- Create one final calculated field called % of Total with the code IF [Last] = 0 THEN [3 Month Total] / [All Provider Total] END
- Place % of Total on Text
Now the hard part
- You have to get all the Nested Calculation "Compute Usings" Correct:
- Use the drop down on [% of Total] and select Edit Table Calculation
- In the window, step through each nested calculation and set them as following:
- Last to use Specific Dimensions: Month
- 3 Month Total Month to use Specific Dimensions: Month
- All Provider to use Specific Dimensions: Provider
(In previous versions of Tableau, just use the Compute Using drop down to select Advanced and then move the indicated dimensions over to Addressing)
- using the drop down on the field Last that is on the Detail of Marks make it Discrete and then move it to Filters and select to keep 0
- use the drop down on Month to uncheck Show Header
You should now have a view that has all percents (and 0%) for all Providers (as long as other filters don't remove them; as long as they exist for any month - and not just the last 3 - they will show up)
Personally, I think the Analysis > Table Layout > Show Empty Rows approach is a bit simpler! But Tableau can do almost anything you want!
I must have messed up somewhere because I am right back where I started. I am still missing a row. i will take another crack at it tomorrow. Thanks for all your help