6 Replies Latest reply on Dec 9, 2018 12:27 AM by Ganapathy Palanimuthu

# Calculation comparison with previous year

Hi Tableau community

below is my sample data

 Year Sales 2010 \$100.00 2011 \$200.00 2012 \$150.00 2013 \$130.00 2014 \$130.00 2015 \$300.00

how  to create calculation if my sales increse/decrease? for example

if 2011 > sales 2010 then increase/decrease

Best,

RizalMahmod

• ###### 1. Re: Calculation comparison with previous year

IF SUM(Sales) - LOOKUP(SUM(sales), -1) >0 then "INCREASE" else "DECREASE" end

You could have an ELSEIF in there to check for no change too.

• ###### 2. Re: Calculation comparison with previous year

Here is the solution tested, kindly follow the steps

1. create calculated field "Increase/Decrease"

IF SUM([Sales]) = LOOKUP(SUM([Sales]),-1)

THEN "No Variance"

ELSEIF SUM([Sales]) > LOOKUP(SUM([Sales]),-1)

THEN "Increase"

ELSE "Decrease"

END

2. Take [Year] to Row and make it as Discrete

3. Place "Increase/Decrease", calculated field to Text and also to Color

4. Format the table, if you want

Explanation : minus 1 LOOKUP will get you previous row value.  Please be careful, previous value is based on sort order whether from 2010 to 2015 or 2015 to 2010.  Hence table calculation is to be edited to reflect as below

here is the output.

• ###### 3. Re: Calculation comparison with previous year

Here is am improved calculation field.  year1 should not have any comment, should be blank

IF ISNULL(LOOKUP(SUM([Sales]),-1))

THEN " "

ELSEIF  SUM([Sales]) = LOOKUP(SUM([Sales]),-1)

THEN "No Variance"

ELSEIF SUM([Sales]) > LOOKUP(SUM([Sales]),-1)

THEN "Increase"

ELSE "Decrease"

END

• ###### 4. Re: Calculation comparison with previous year

Thank you GANAPATHY PALANIMUTHU . you are my hero today. this is correct answer i'm looking for.

Best,

RizalMahmod

• ###### 5. Re: Calculation comparison with previous year

Welcome, it's my pleasure

please take note of the edit table calculation explained in the first reply, otherwise change of sort order of year will impact the solution

Best Regards

Ganapathy Palanimuthu