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

% Difference Calculation

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()-SUM())/SUM()*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!

• 1. Re: % Difference Calculation

Can you supply some dummy data and maybe a workbook please ?

• 2. Re: % Difference Calculation

Hi ,

Please change your calculated field of the percent with this one :

ZN((ZN(SUM())-ZN(SUM()))/zn(SUM()))

Zn function return 0 if the value is null, in your case you return 1 when the 2 values are 0, that's why you have 100%

Hope it helps

• 3. Re: % Difference Calculation

Your formula worked to get me 0 for the percentage when 2018 = 0 and 2017 = 0 so thank you!

Now I have 2 different % difference columns, one comparing based off of 2018 and the other based off of 2017. I need this combined into a calculation to make a single % difference column.

I really wanted it to be where % difference would give a negative or minus based off of 2017.

Examples:

2018: 5000

2017: 1750

% DIFF: 96.29%

2018: 1750

2017: 5000

% DIFF: -96.29%

2018: 5000

2017: 0

% DIFF: 100%

2018: 0

2017: 5000

% DIFF: -100%

2018: 0

2017: 0

% DIFF: 0

Is this possible for 1 column?

I can't figure this out.

• 4. Re: % Difference Calculation

Please can you explain how did you get %DIFF = 96.29% in the first example:

2018: 5000

2017: 1750

% DIFF: 96.29%

• 5. Re: % Difference Calculation

Actually, that was an error on my part.

I did percent difference. I need to do the percent change. I've been mixing them up.

V2 - V1 / V1 x 100

• 6. Re: % Difference Calculation

Not sure if I am following your comment correctly, but I thin the formula I gave you will work in your case.