3 of 3 people found this helpful
That post seems to have been removed. Maybe you can re-post your question?
However, based no your title of the post, there are a couple of possible options. A calculated field can be created that uses the ifnull function. The formula would look similar to the following:
Another possibility is to right click on the measure (in the view) and select Format. Make sure the Pane tab is selected. Under Special Values type in 0 in the text box.
Hope this helps!
Thanks for your response, I have tried both of those solutions but neither have worked for me. This was my original post:
I'm a bit stuck and hope you can help. I've tried your formula
IIF(ISNULL([data]), 0, [data])
but it's not worked. I've also tried
IIF(ISNULL([data]), 0, 1)
and still not luck. Would you be able to have a look at the attached package and see where I'm going wrong? I've searched through the forum for a solution but haven't been able to find anything that works.
Greatly appreciate any help. I've attached the package workbook as well for reference.
PGAdmisStats.twbx.zip 1.6 MB
12 of 12 people found this helpful
The basic issue is that there doesn't seem to be any data for those combinations of dimensions you are using, and Tableau doesn't make up data when there isn't any. "No data" is different from Null, and that's why the ISNULL() function is failing.
However, we can tell Tableau to generate a null when there isn't any data, and that's using the following table calculation: LOOKUP(SUM([Flag]),0).
This table calc returns SUM([Flag]) for the current row, and Null when no value is found. Then you can wrap that in the IIF(ISNULL()) operation to return 0 or a value. alternatively, you could just do the lookup and then Format the calculation to set 0 to be the Text for Special Values.
Workbook is attached.
I have just tried following the steps provided by Jonathan, but am still unable to get this to work. Can this work in any instance? As I am not building a 'data sheet' per say but on my sheet I only need 1 number, however when there is no data tableau leaves it blank. Will this work in any circumstance? I will provide an example if needed.
Can you please let me know if you find any solution for this.I have a similar problem,where i have to display a single number.If there is no data in the field,no data appears in the sheet too.I need to replace empty field with 0.Thanks
if is null then 0 end
On Mon, Mar 2, 2015 at 9:47 PM, Ranadheer Kovvuri <
Thanks for the reply, it did not help me.It doesn't show anything.
2 of 2 people found this helpful
Post a new thread with clear explanation,
Another solution to replacing nulls with o is
iif ( isnull([Sales]), 0,[Sales] )
Refer the following Document to get detailed information.
On Mon, Mar 2, 2015 at 10:00 PM, Ranadheer Kovvuri <
I am facing the same issue. I don't have data after April and I need to show running sum data for fees. I am trying to convert Null to 0 so that I can create running sum value for the months after April as well.
However, I am unable to convert Null to 0 and there is no data shown after April month.
Could you please suggest?
Please post a Tableau packaged workbook with some sample data. There are too many variables in what you describe for me to try to guess what your data looks like, what's in the view, or your desired results.
the zn() function replaces any null value with a zero
Thanks for the workaround of first converting NoData to Null and then evaluating the Nulls. For some reason, this does not seem to work on a worksheet which is completely blank (no table grid with missing values) and all I need to show is a ZERO if the Expression is evaluated as NULL.
I have attached the packaged workbook herewith. Could you please bounce off any ideas you might have to show a ZERO on this worksheet.
Thanks and appreciate your help and time into this!!
Convert NoData to Zero.twbx 522.9 KB
The Sub Region filter is on Alaska and Alaska doesn't exist in the data, so the query that Tableau is issuing to the data source is returning 0 rows. So Tableau isn't even drawing a mark, see the marks count in the bottom:
So there isn't a Null-valued mark that can be turned into 0 via any type of calculation. A number of the techniques that are talked about with no data (in this thread, and links to others) take advantage of Tableau's built-in data densification behaviors, but all of those require there to be at least *some* data to pad with.
Given this particular situation (the query isn't returning anything at all) the one technique that I know of is to:
a) Set up the worksheet so instead of showing a blank space it shows nothing at all when there is no data. We can do this by adding a discrete pill to the view and turning off Show Header, this is the "Real data sheet" in the attached workbook:
b) Create a worksheet that has the same layout that *does* show a 0. This is the "No data sheet" in the attached workbook:
c) Create a dashboard and place the worksheet from a) as a floating worksheet on top of the worksheet from b), then fiddle with the alignment. Here's the dashboard filtered for Alaska:
And now I've gotten rid of Alaska so the COUNTD is returning 3403:
v9.0 workbook is attached.
Convert NoData to Zero jtd.twbx 545.9 KB
Thank you so much for your detailed response! Yes, I agree with you that since there is no mark at all, there is no calculation that could convert NOTHING to ZERO. This workaround is very helpful. Thanks a ton!!
Just to give you background, I am working on an operational dashboard which needs to show the count of exceptions against different scenarios. This dashboard already has so many worksheets since each of those scenarios has a separate data source and ultimately a different database view in the backend. I wish it was possible to reuse the Zero worksheet at multiple places on the same dashboard. Or may be I will just create text boxes for the static Zero but if only we had an option to just copy paste and reuse the same text box already created.
Again, greatly appreciate your time and effort into this!. Have a good one!