3 Replies Latest reply on Nov 14, 2012 9:29 AM by Shawn Wallwork

# Exclude last year from % difference to previous year calculation

I'm showing a booking trend and the % difference of bookings from the previous year on a dual axis chart. Please see the attached screenshot.

Due to the fact that our current Fiscal year is still ongoing I want to exclude the % difference calculation from 2012 to 2013 (here -55,1%) from the chart but the total booking bar for 2013 should stay. How can I do this?

Thanks for any idea!

Grit

• ###### 1. Re: Exclude last year from % difference to previous year calculation

Hi Grit, I think this is what you're looking for:

1. Create a calculated field like this     IF [Order Date]< DATE(#1/1/2012#) THEN [Sales] END
2. Put this on your right-Y axis instead of what you've got there now.
3. Add the YOY quick table calc, and Tableau will create this calc: (ZN(SUM([Exclude 2012])) - LOOKUP(ZN(SUM([Exclude 2012])), -1)) / ABS(LOOKUP(ZN(SUM([Exclude 2012])), -1))
4. Now go into the Edit Table Calculation\Customize and delete all the references to ZN. This gets rid of the 2012 line at zero %.

Hope this works for you.

--Shawn

• ###### 2. Re: Exclude last year from % difference to previous year calculation

Hi Shawn,

thank you for the time you spent to help me to find a solution!

I found a solution that is similar to yours.

Cheers

Grit

• ###### 3. Re: Exclude last year from % difference to previous year calculation

Grit, you're welcome as always.

--Shawn