6 Replies Latest reply on Oct 14, 2018 5:24 PM by Oussama IMAOUEN

    % Difference Calculation

    Adrian Santana

      Good evening all.

       

      I'm trying to do a % difference for 2 years.

       

      The years are distinct and have nulls as 0.

       

      Formula:

      IF YEAR([ENTRY DATE])=2018 THEN [AMT] ELSE 0 END and then a 2017 version.

       

      So when creating a calculated field for % difference, I have 2018 - 2017 / 2017. This gives me the correct % except if 2018 is 0, it returns 0. So I created IFNULL ((SUM([2018])-SUM([2017]))/SUM([2017])*1, 1), which gives me the correct percent for values except for when both years are 0 then it gives 100%, which is incorrect

       

      In the end what I want is a % difference that gives me a -/+ value regardless of whether 2018 has a null or not.

       

      For example this is what happens:

       

      A) if 2018 = x & 2017 = x then x%, which is correct

      B) if 2018 = x & 2017 = 0 then x%, which is correct

      C) if 2018 = 0 & 2017 = x then 0%, which is incorrect, should be the opposite of B

      D) if 2018 = 0 & 2017 = 0 then 100%, which is incorrect. should be 0 or null

       

      Any help is appreciated. Thanks!