3 Replies Latest reply on Mar 3, 2016 3:04 AM by V Babu

# Calculate % Difference Between Same Month from Previous Year and Show N/A for Null values

I have a graph with two lines comparing sales between 2015 and 2014. I have a table calculation that calculates the difference between the month (e.g. Jan 2015 compared to Jan 2014). It shows the the % difference in the tooltip for 2015 but for 2014, it just shows a blank space but I want it to show something like "N/A". I created a calculated field by dragging the table calculation into the formula textbox:

IF ISNULL((ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))) THEN "N/A"

ELSE str((ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)) * 100)

END

But the result calculates the current month vs the previous month (e.g. Feb 2015 compared to Jan 2015).  How can I calculate to compare the % difference for the same month, previous year? I'm also open to keeping the table calculation I already have on Sum([Sales]). I just need a way to show "N/A" for the % difference for the 2014 line (because 203 data is not available). I attached a workbook to show what I have.

• ###### 1. Re: Calculate % Difference Between Same Month from Previous Year and Show N/A for Null values

I can't open your workbook due to Tableau version differences however you can try the following to lose the NULL values. Format the YoY measure, and type N/A in the Special Values section I've highlighted.

Depending on how your data is set up you may be able to change your calculation to look at the same month previous year by changing the '-1' (i.e. comparing to the previous month) in your Lookup functions to '-12' (i.e. comparing to the month 12 month values prior...which assumes you have data for every month for the previous 12 months)

• ###### 2. Re: Calculate % Difference Between Same Month from Previous Year and Show N/A for Null values

Hi,

To compare the % difference for the same month, previous year

Edit the table calculation ..Advanced as below

But in your case Instead of having another Calculated Field .. Right click Sum(Sales) in Label shelf..Format ..include Text NA. Done..!!

• ###### 3. Re: Calculate % Difference Between Same Month from Previous Year and Show N/A for Null values

Hi Kristophe,