1 2 3 4 57 Replies Latest reply on Jan 21, 2020 12:36 AM by Akhytbyek Ontugan Go to original post
• ###### 30. Re: How do we display zero when there is no data ?

Unfortunately I cannot create the work book with dummy data as I see this could be the issue with the data in the data base. If the sales people have any sales value in that time window, the calculated field provided by Swarup K works. But in my case a Sales person working in 2015 is off the Organisation say after 6 months, then there is NO underlying data for that sales person, whatsoever from that time on in this case the 0's become blank spaces if I filter for say week 34 of 2016, or just after the date he is no longer there then the Zero's disappear to blanks for the sales person no longer in the Organisation

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

As a start, could you share:

1. how your calculation looks like ?
2. a screenshot of your Tableau Workspace ?

This would be helpful for your helpers, which by now are as many as 12.

And of course, make everything sensitive anonymous.

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

The simplest way to sort this out, ruling out Tableau solutions, is to create your missing data within SQL before pulling into tableau.

Create a distinct list of all sales people/time period combinations and left join the actual sales data to that.

Sent from Samsung Mobile

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

have you tried ifnull() yet like i suggested above?

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

I'm going to second what Andrew said. We can pad out the data in Tableau and get this to work by jumping through some hoops, but life is a lot simpler by having real records in the data.

The reason why the date filter removes results is due to Tableau's order of operations. The data densification that Tableau does that pads in 0's (such as using the ZN(LOOKUP([measure],0)) trick) happens *after* dimension filters are applied so if the dimension filters remove too much data (i.e. make it too sparse) then data densification doesn't have enough to work with. There are a variety of ways we can try to get to this to work however we really need to know what your final goal is because that can determine which route works best (or works at all).

If you'd like us to help you I suggest you use Superstore to mock up something where in place of "salesperson" use Customer Name, not every customer has sales on every day so it should be possible for you to come up with something that resembles your situation and the goal you are trying to reach.

Jonathan

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

Ifnull() does not work because there is no data

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

0 show up for some sales people for entire date range (dates). Why is zero showing up for some people ? later on debugging at a deeper level I find that 0 shows up if they have at-least some sales in that time window, if there is no data in that time window or the sales person is off the Organization its blank

When I move date filter in the right you see that Pete and gabri turn to blank from 0

also some sales folks are no longer in the organisation and some are new, so they do not have any data in the time window

Formula used :

IF YEAR([Close Date])= 2016 THEN [Max Sum Loc] END),0)), provide by Swarup K

The data set is is a couple of table with left joins from the Data base MS SQL server

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

IF YEAR([Close Date])= 2016 THEN [Max Sum Loc] END),0)), provide by Swarup K

Could you include the missing part in the beginning of your formula?

Could you share the calculation of [Max Sum Loc]?

Could you show a screenshot of your Tableau Workspace ?

Areas surrounded with orange are the most interesting,

namely Columns, Rows, Filters, Marks and Status Bar.

Just grey out whatever is sensitive as you already have been doing.

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

ZN(LOOKUP(SUM(IF YEAR([Close Date])= 2016 THEN [Max Sum Loc] END),0)) in the calculated field Zero 2016

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

Thanks

Is  [Max Sum Loc]  a calculation?

If yes, could you share that calculation too?

Knowing all the formulas, filters and pills might make it possible for helpers to "re-create" the issue with their own dummy data and workbook.

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

Max Sum Loc is direct field from the DB

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

To other helpers:

The reason that there are more rows than marks is because  Aditya's  workbook is set to show empty rows as stated here:  Re: How do we display zero when there is no data ?

I think you now have provided enough information to "re-produce" the issue with dummy data in a packaged workbook.

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

I think I understand now.

When choosing Analysis > Table Layout > Show Empty Rows, these extra empty rows are not affected by ZN(LOOKUP(SUM([Sales]),0)), but show nothing (blank).

If these can be formatted, I don't know, but it doesn't look like they can, and this old comment by Jonathan Drummey supports this conclusion:

Turn on Analysis->Table Layout->Show Empty Columns. This will make Tableau fill in the crosstab, but in most cases you wouldn't be able to get Tableau to show anything in those cells. The kind of padding done by Show Empty Columns and Show Empty Rows is mostly not addressable by table calculations, not fillable by Field->Pane tab->Special Values->Text, etc., it's just for visual display.  Re: Force Blanks to Show.

1. Could you share your two screenshots again, but this time without calculation, so Abc is seen?

2. In addition, could you share number of marks in both? I assume it changes!

But maybe you don't have to do this, because it seems that empty rows cannot be formatted!

Attached Workbook Version:  9.0

.

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

I agree that Show Empty Rows is likely to be in use here, however it could also be the change Tableau made in v9.0 to count only “visible” marks, one effect is that Null values are no longer counted in the marks count. So the view could be densified via another means and get the same results. See http://drawingwithnumbers.artisart.org/when-576-567-528-456-counting-marks/ for more details.

Jonathan

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

Have you attempted to simply format that field, for example:

1 of 1 people found this helpful
1 2 3 4