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

    kristophe m

      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.