1 2 Previous Next 22 Replies Latest reply on Oct 10, 2016 7:13 AM by Luciano Vasconcelos

if there is no data - I have gaps in charts - how to solve it?

Hi Guys, here's a simple (for you) but a bit disturbingly hard for me:

I try to draw some charts on weekly basis. In some cases, my data source does not contain any data for particular week, and then I have gaps in my chart.

Now, if I de-select “show missing values”, then chart is without gaps, but then I have weeks missing.

Is there any way to actually show all weeks and all lines connected, despite the fact if the data is here or missing, with values for those weeks being as zeroes?

• 1. Re: if there is no data - I have gaps in charts - how to solve it?

Hi Somonas,

Use the following formula.

LOOKUP(ZN(SUM([Value])),0)  This forces a table calculation and uses a technique known as domain padding. This will force a calculation for each partition in table. And because there is no data for that table the response is null. the ZN() function returns 0 anytime it seems a null. LOOKUP is a function that forces the table calc and it is being set to 0 so it looks up its own position within the table.

Best Regards,

Carl Slifer

InterWorks

• 2. Re: if there is no data - I have gaps in charts - how to solve it?

Hi.

You can set nulls for show in default position or create calculations like this:

• 3. Re: if there is no data - I have gaps in charts - how to solve it?

See this one.

• 4. Re: if there is no data - I have gaps in charts - how to solve it?

well, I cannot load the file. I use 9.2 version, maybe that's the reason?

However, the solution did not work, because on the particular week, lets say week 6, there is no entry at all.

In this view, let's say, week 5 has entry with any value. Week 7 has entry with any value. but there is absolutely no entry for week 6.

so it creates a gap.

I created a calculation field used the formula as this:

IFNULL([Total Amount (All Items)],0)

But it does not solve the problem. data on week 6 does not exist at all.

LOOKUP(ZN(SUM([Total Amount (All Items)])),0) gives me result as "Undefined"

• 5. Re: if there is no data - I have gaps in charts - how to solve it?

Carl, Many thanks. but looks like it is not working.

LOOKUP(ZN(SUM([Total Amount (All Items)])),0) - this is the formula I used.

Imagine that my data is like a table of 3 lines and 3 columns

COL 1               COL 2               COL 3

Sale name          Week               amount

Sale 1               week 5               5000

Sale 2               week 5               600

Sale 3               week 7               1000

So in this case, when I want to show, how the sales changed during the time between week   5 and week 7, I have a gap in week 6. Data does not exist. simple as that.

so is there any way to make chart to drop values to 0 for example, if the data is not here?

Data source I got, is like 2000-5000 lines and for me it is not really feasible to enter "0" manually for each week for any criteria as dummy data

• 6. Re: if there is no data - I have gaps in charts - how to solve it?

You need to use real dates. So that it is range aware. Week 5, Week 6, etc

are not real dates. Without real dates you will need to join this data onto

a master data set with Week 1, Week 2, etc all the weeks listed out.

There's no way to 'create' something that flat out doesn't exist. I'd fix

your DB methodology before trying to force tableau to do something.

On 7 October 2016 at 14:27, Simonas Paulaitis <tableaucommunity@tableau.com>

• 7. Re: if there is no data - I have gaps in charts - how to solve it?

Simonas, these answers provide solutions for null values but you need data.

One suggestion is you build a full calendar and use it on a left join.

Doing it you'll have all date values with null values where your data source does not contain info.

• 8. Re: if there is no data - I have gaps in charts - how to solve it?

Carl Slifer

yeah, I use the actual dates. it is like 3/02/2016 4/2/2016 and so on. and there is no data for the time period which is, for example, in week 6.

I use real dates in the bottom, and group them by week.

My data has few more gaps, as you might see. and I have numerous categories where I have to create such chart, so really, It is hard to create data with "0" for particular week for particular category :/

Luciano Vasconcelos what do you mean by "build a full calendar on a left join?"

• 9. Re: if there is no data - I have gaps in charts - how to solve it?

You build a sheet in excel with a full calendar:

 Date 01/01/2016 02/01/2016 03/01/2016

and go on .....

On your data source it will be the first table. You will link your data on right of it using date field.

As you calendar is complete, you will have all weeks available with some of them without data.

With this data model you can use Ifnull clause.

1 of 1 people found this helpful
• 10. Re: if there is no data - I have gaps in charts - how to solve it?

good point! Didn't think of that. Many thanks. Will try this and will let you know of outcome next week!

many thanks guys!

• 11. Re: if there is no data - I have gaps in charts - how to solve it?

Many thanks! this worked guys!

EDIT:

looks like I jumped the gun here. nothing's changed :/

I use the calculated fields, did not help :/

• 12. Re: if there is no data - I have gaps in charts - how to solve it?

Maybe I did something wrong?

Problem, if nothing created on the particular date, let's say - 1/1/2016, that data line does not exist. If chart is drawn using the week number (from the actual dates) it draws me the grapsh with gaps. I would like to have 0 on those particular weeks...

1) Create new sheet, called calendar. add all days from 1/1/2016 to 31/12/2016

2) Add that sheet as first, so it is showed on the left.

Join (LEFT)

left data source: Calendar

Right data Source: Created Date.

Using this tutorial:

Step 1

Open Tableau Desktop and connect to your data source. This example uses the Sample – Superstore data source.

Step 2

Select Analysis > Create Calculated Field.

Step 3

In the Calculated Field dialog box, do the following:

1. Name the field ZN Lookup.
2. In the formula area, enter: ZN(LOOKUP(SUM([Sales]),0))

Populate empty cells with zeros

Step 1

From the Dimensions area of the Data pane, drag Category to Columns.

Step 2

In the Dimensions area of the Data pane, right-click (Control-click on a Mac) Category and chooseDuplicate.

Step 3

From the Dimensions area of the Data pane, drag Category (copy) to Rows.

Step 4

From the Measures area of the Data pane, drag ZN Lookup to Text. Empty cells are now populated with zeroes.

So now, my graph looks like I want - if week 6 was not there, it is empty, so it is 0. that's awesome. But this works just as SUM. I want same data to use on secondary axis as COUNT. But in this case, I cannot convert this ZN Lookup to count. the Measure field is grayed out.

Of course, I can switch, and have SUM as LINE and COUNT as bars, but I would prefer it other wise. Any solution here?

The ZN Lookup is in the Measures pane, by the way.

• 14. Re: if there is no data - I have gaps in charts - how to solve it?

Well, I can try...

here's a simple excel sheet with some random data, which looks like the data I would use and more or less required output (three charts).

trying to recreate it in Tableau...

and actually, I got even more complex task with the data, but I can try to think about it only when I recreate my dashboard

1 2 Previous Next