4 Replies Latest reply on Jul 28, 2016 8:37 AM by Emily Shen

# calculate difference across a dimension when prior value missing

Hi,

I am trying to calculate difference across years, but say one year is missing. The data is like this below

YearTypeSales
2010A100
2011A110
2013A140
2014A150

When I use a simple table calculation, the result in Tableau is like this

so even though 2012 is missing, it is calculating the difference from 2011. What I want is when prior period/value is missing, do not calculate, leave as empty or a null value. Is this possible?

Thanks for any help!!

• ###### 1. Re: calculate difference across a dimension when prior value missing

To be clear, instead of the 30 showing for 2013 you would expect nothing to show or a zero because 2012 is missing?

1 of 1 people found this helpful
• ###### 2. Re: calculate difference across a dimension when prior value missing

Yes, nothing to show would be good, I don't want zero. Thanks!

• ###### 3. Re: calculate difference across a dimension when prior value missing

The first solution that comes to mind is to show the difference only for years with non missed previous year.

Difference calculation: sum([sales])-LOOKUP(sum([sales]),-1)
Proper year condition: attr([year])-1=LOOKUP(attr([year]),-1)

*Use attr() to avoid error 'Cannot mix aggregate and non-aggregate arguments'

1 of 1 people found this helpful
• ###### 4. Re: calculate difference across a dimension when prior value missing

Vasily,

This solves my problem! You are awesome!! Thank you!