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.
You can set nulls for show in default position or create calculations like this:
IFNULL([Sales],0) and use instead Sales
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"
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
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 <email@example.com>
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.
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?"
my data is downloaded for external source into csv/xls
1 of 1 people found this helpful
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.
good point! Didn't think of that. Many thanks. Will try this and will let you know of outcome next week!
many thanks guys!
Many thanks! this worked guys!
looks like I jumped the gun here. nothing's changed :/
I use the calculated fields, did not help :/
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.
3) add report. Link it. So for me it shows:
left data source: Calendar
Right data Source: Created Date.
Using this tutorial:
Open Tableau Desktop and connect to your data source. This example uses the Sample – Superstore data source.
Select Analysis > Create Calculated Field.
In the Calculated Field dialog box, do the following:
- Name the field ZN Lookup.
- In the formula area, enter: ZN(LOOKUP(SUM([Sales]),0))
Populate empty cells with zeros
From the Dimensions area of the Data pane, drag Category to Columns.
In the Dimensions area of the Data pane, right-click (Control-click on a Mac) Category and chooseDuplicate.
From the Dimensions area of the Data pane, drag Category (copy) to Rows.
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.
Post a sample of your data please.
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
Reports_example.xlsx 146.1 KB