2 Replies Latest reply on Jul 25, 2016 6:18 AM by keerthana kumar

# LY sales calculation with lookup

Hi All,

My scenario today is to show TY Vs LY Sales across Year ..Quater..Month (Hierarchy) in a bar graph as below.(Using Tableau 9.2)

Requirement :

1. Show by Fiscal year of Order Date , Apr 2011 -March 2012 is considered as FY 2011 [Achieved]

2. LY Sales -  Formula used LOOKUP(SUM([Sales]),-1)

Issue :Everything was fine until I found Customer 'Tamara chand' ....Who did  sale in Q3 of 2011 and 2013 but not in Q3 of 2012

Hence in my graph LY sales bar is showing \$100 which actually he did in 2011 which should not be shown as LY sales in 2013 (in 2012 Q3 no sales) .

Is there any way workaround to correct this in tableau itself ??

• ###### 1. Re: LY sales calculation with lookup

Hi Kumar,

Try with the zn function (converting null values into 0):

LOOKUP(zn(SUM([Sales])),-1)

• ###### 2. Re: LY sales calculation with lookup

Hi ..

Thanks for the reply . Here in my scenario its not making any difference as for that particual customer there is no entry for the entire month of November in 2012.

so LOOKUP(zn(SUM([Sales])),-1)   -1  is taking 2011 values.