1 2 3 4 Previous Next 58 Replies Latest reply on May 9, 2020 3:22 AM by Surya Reddy Go to original post
• 45. Re: How do we display zero when there is no data ?

I'm running into a similar issue. I can get the Zero (in my case its text saying "not carried") to show when all the markets are showing, but when I try to filter to one market, the zeros ("not carried") is lost. Am i missing something?

• 46. Re: How do we display zero when there is no data ?

You can use

ZN(SUM([Sales])

or

IFNULL(SUM([Sales]),0)

or any of the above doesn't work, you can follow below steps:

Step 1: Right click on SUM([Sales])

Step 2: Click on "Format"

Step 3: Go to "Pane" section

Step 4: Write "0" in Special Values' "Text" field

Let me know if this works.

Regards,

Parikshit

• 47. Re: How do we display zero when there is no data ?

If nothing else suggested in this thread works then add a background image of a single cell with 0 in it to the worksheet. You will have to do a lot of manual adjusting and sizing, but this solution works like a charm.

• 48. Re: How do we display zero when there is no data ?

Hey Alford,

I ran into the same issue. Did you find a solution for this?

Thanks,

Kary.

• 49. Re: How do we display zero when there is no data ?

Hi Nikhil,

I ran into the same issue. It'll be of great help if you could please explain how to do this.

Thanks,

Kary.

• 50. Re: How do we display zero when there is no data ?

I ran into the same issue. Did you find a solution for this?

It would be of great help if you could let me know.

Thanks,

Kary.

• 51. Re: How do we display zero when there is no data ?

Hi,

I am having same issue I tried your solution but its only working at some places . why is that so ?
I am using tableau desktop version 10.3.5
Below is my calculation :

IF [Select Metric]='Total Cost'
THEN
ZN(lookup(sum([Total Cost]),0))

ELSE
ZN(lookup(sum([Quantity 1]),0))

END

Where [select metric] is a text parameter.

• 52. Re: How do we display zero when there is no data ?

I ran into a rather similar issue and none of the proposed solutions worked, as it always returned a blank worksheet (so not even NULL). Even though my problem was slightly different, I found a solution and would like to share it, so Google may find it some day ;-)

The problem was, that I wanted to return TRUE or FALSE, if there are new records within the last few hours (to send out an alert if anything went wrong repeatedly). This could also be used to display '0' if there are no new sales or something like that, like mentioned here:

To solve this, I created a custom field that could look like this and simply compares two periods of time and checks if they have the same number of records. If there is the same number of records, it returns "FALSE":

IF COUNTD(IF DATEDIFF('hour',NOW(),[Stmt Created At DST CET]) < -3

THEN id

ELSE

NULL

END) =

(COUNTD(IF DATEDIFF('hour',NOW(),[Stmt Created At DST CET]) < -6

THEN id

ELSE

NULL

END) -

COUNTD(IF DATEDIFF('hour',NOW(),[Stmt Created At DST CET]) < -3

THEN id

ELSE

NULL

END) )

THEN FALSE

ELSE TRUE

END

I hope anyone out there finds this helpful!

• 53. Re: How do we display zero when there is no data ?

Hi Masters,

Anybody who has resolved or managed to place a "0" when there is no data at all (no records /  no results)

IFNULL, ISNULL and ZN functions didn't work for me as there is no data as a result of my formulas / filters. I am trying to show "0" or a text "None" when there is no result. Please help if anyone has solved this or found work around . I have gone through all the topics related to this issue in this forum, so far cant find any work around for myself.

Regards,

Akito

• 54. Re: How do we display zero when there is no data ?

Hello Akito,

Also for an overview of padding & densification I did a talk at TC19, see https://tc19.tableau.com/learn/sessions/zen-master-you-did-what-your-data-deep-dive-imputing-and-densifying-data for that.

Jonathan

• 55. Re: How do we display zero when there is no data ?

Hi Jonathan Jonathan Drummey,

Thanks for the reply. I am not sure if I am asking something impossible in Tableau. But see attached for test Tableau file (with excel data) and see below snapshot explanation for more information in actual Tableau file that I am working. Maybe this is vert simple thing.

In test file - if I can manage to show below, then I think I can do same in my actual Tableau file

Regards,

Akito

• 56. Re: How do we display zero when there is no data ?

Hi Akito,

Thanks for the workbook & data (FYI next time you can use File->Export Packaged Workbook) so you can have just one file.)

The issue here is due to order of operations, here's the relevant documentation Tableau's Order of Operations - Tableau . In the first screenshot that you shared of your actual view there a) might be not be any data, in which case Tableau doesn't have anything to draw and draws a blank view with 0 marks; or b) based on the filters in play (which are all dimension filters) the query that Tableau issues to the data source might not return any rows, and therefore Tableau draws a blank view with 0 marks.

The workaround I propose here is to work with the order of operations so that the query does return rows, and then make sure we're only counting the right rows, i.e. the rows for the latest month. This a form of SUMIF().

I set up the Latest Report Date calculation with the formula {EXCLUDE : MAX([Report_Date])}. This is a Level of Detail expression that identifies the latest report date across all the available data (assuming there are no other dimensions on Rows, Columns, Pages, or the Marks card) and returns a record-level result (that needs to be aggregated in a view). Then Latest # of Records formula uses IF [Report_Date] = [Latest Report Date] THEN 1 ELSE 0 END, and with the default aggregation of SUM() we get an accurate 3460 records.

Note that by using EXCLUDE it's the latest report date based on dimension filters (since EXCLUDE calcs are processed after dimension filters), and depending on how your data is filtered this might not be accurate. An alternative would be to use {FIXED : MAX([Report_Date])} and that would be computed *before* dimension filters, and any filters that you wanted apply before the FIXED LOD expression would need to be added to context.

I've attached a v2019.4 packaged workbook, let me know if you have any questions!

Jonathan

1 of 1 people found this helpful
• 57. Re: How do we display zero when there is no data ?

Many thanks for the detailed message Jonathan Drummey,

• 58. Re: How do we display zero when there is no data ?

Hi Alford,

I am facing the same issue, did you get any solution?

Thanks,

Surya.

1 2 3 4 Previous Next