7 Replies Latest reply on Feb 29, 2016 10:04 PM by Yuri Fal

# Null values in Table Calculation

Hi all,

I'm having trouble getting Tableau to handle null values appropriately in a table calculation. I've created a Year over Year table calc that shows the difference in number of orders between two years based on region and product category (calculating difference from order date level of detail):

For example, as indicated in the above table, my Office Supply orders in the Atlantic region increased by 3 between years 2011 and 2012. Similarly, my orders for technology products in Atlantic increased by 26. The problem is that this table calculation will not treat null values as 0, so if I had no orders in one year and 10 in the next, the table value will appear blank. An example of this is with Furniture products, which I only started selling in 2012. (see table below):

I would expect my YoY order difference calculation to show that between years 2011 and 2012 I increased my furniture sales by 51 in Atlantic, 21 in Northwest, etc, but instead it just shows blank. I've tried using the ZN and IFNULL functions to no avail. Any help would be very appreciated!!

• ###### 1. Re: Null values in Table Calculation

Hi spenser.

It happens that you'd like to follow a recipe

Rody Zakovich in his recent Fun Brain Teaser:

Got a fun Educational Brain Teaser

by flagging Show Missing Values

on an Order Date pill when on Columns.

Then you could move it back to Details,

and then enable data densification (i.e. more Marks

for each Year-Category-Region combination on a view)

by placing an index() discrete (blue) pill on Columns.

The rest is simple.

Please find the attached wb w/mods.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Null values in Table Calculation

Thanks Yuriy! This does the trick (although I can't quite describe what's going on to make it work).  My one follow-up questions about this solution is how can I add column grand totals to the sheet? When I attempt to add column totals Tableau tells me I can't use totals with a discrete measure (the Index function). Is there any way around this? I suppose I could create another sheet that doesn't use use the region measure and line the two up on a dashboard, but that seems tedious.

Thanks!!

• ###### 3. Re: Null values in Table Calculation

Hi spenser,

Since index() is used to filter 2012 year,

you could place Years pill on Columns

and remove index() pill from a view.

Please find the attached wb w/mods on Sheet 7a

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Null values in Table Calculation

Thanks! Does this work with row totals as well? Ideally I'd be able to get both row and column totals, any thoughts??

• ###### 5. Re: Null values in Table Calculation

You'll see.

• ###### 6. Re: Null values in Table Calculation

Hmm- so I gave it a shot and they're showing up blank for me. Any thoughts?

• ###### 7. Re: Null values in Table Calculation

Hi spenser,

You could shuffle pills in Columns

(Order Date first, Category second)

then do Analysis --> Totals --> Show all Subtotals.

So Year subtotals get the expected result.

Rename a subtotal header and you've done.