Please always send your workbook so we have data to work with
The solution you want takes the form ifnull(Lookup(Min(Dimension),0),0)
Where the dimension could be a measure and the Min could be a different aggregation or an Attr() (that is why we like to see your data
Play with it and you can get it to work
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks for the reply. nothing that I have tried that refers to NULL works because there is no null, no data at all. the sheet that I am referring to is the "Excpn - Exp" sheet. As you can see on this sheet there is just a title with all the filtering applied. The custom filter I was referring to in my original post is "Excpn Exp Cat"
COMS reporting - zero help.twbx 435.4 KB
The issue here is that the filters are eliminating all data from your result set, so an ifnull isn't going to work because you don't even have any rows that are null, you have no rows in your results at all.
One potential way to show '0' as your result would be to modify how you have your filters set up.
What if, instead of filtering for a specific SCO, you add a parameter to the workbook for SCO and then update your exception ID zero calcuation to an if statement:
if ATTR([SCO])=ATTR([Parameters].[SCO]) then COUNTD([Exception ID]) else sum(0) end
If you want the SCO value to remain static, you can leave the parameter control hidden and set it to the value of your choosing.
Updated sample workbook attached.
COMS reporting - zero help.twbx 436.9 KB
so that means you tried what I gave you and it did not work? I have used it for No data situations successfully
Please share your workbook
Another option you could try is the old bait-and-switch.
- Create a viz that displays the "zeroes" that you want.
- Put this on your dashboard
- FLOAT the other viz (the viz that will respond to user filters) on top of this: when the result of this viz is ALL BLANK, it will reveal the viz hiding underneath it (the "zeroes")
This is just an option: using the criteria suggest by Jim Dehner, or Crystal Meyers, I would think you *should* be able to get a single workbook to produce the correct values, rather than resort to dashboard sleight-of-hand (an option which could potentially raise more issues).
When there is no data you won't get anything in dashboard. But as a workaround you need to create dummy table with id or unique field in your data and use the left outer join with that dummy table and then drag the field from blended table so that you will get 0 even if there is no data. even in this case if you use filter to filter this data it will be difficult to display the data for those records which are using in filter. otherwise you can change your filter condition to filter with values and display atleast 0 so that it will display zero values. If you have date values you can use that either in rows or columns and select the option of display missing values so that it will display with zeros....
This doesn't seem to work fully, if I change my filtering and expect numbers all I see is the zero, also that parameter needs to be tied to the SCO filter, so it would need to be dynamic.
Looking for the zero value on the Excpn - Exp sheet.
1 of 3 people found this helpful
Yikes fam, we don't need to throw calculations at native features.
Answering for the TITLE on this one.
Prestep: RIGHT CLICK the PILL you want to format.
#1, or select the field in format.
Use Tableau Desktop Special features.
Or you're likely passing a NULL value in your else, when you could pass a ZERO.
The less iterations of calculations, the better your end user experience will be.
I'm going to recommend working through the ONLINE help, or jump into a beginner training course offered by Tableau Software.
There's 7 Filters, that's a lot. I'm not surprised this isn't playing nice.
How many data sources feed into this viz?
1 of 1 people found this helpful
one data source, but it is a very complex query with about 7 joins. I think I found a work around that will suffice. I am placing the value in the Title, this comes across as NONE when filtered and have multiple returns in the title to push the actual data down when there is a number to show, so that on the dashboard you still just see the Title Value.