2 Replies Latest reply on Oct 10, 2018 6:42 AM by Ryan Pesso

# Percent difference calculation

Hello World,

I have a percent difference calculation that shows if a google keyword increases or decreases in CTR performance MoM. The calculation works and was simple to do using a quick table calculation.

I now want to build a calculation that if the percent difference calculation is > 0 then 'Up' else 'Down', so I can have up or down arrows depending on if there is a % increase or decrease.

I figured that I could just copy the percent dif calculation and use that formula to create the new calculation and use an if else statement, but this is not giving me accurate results, some numbers that are negative have an up arrow and some positive have a down arrow.

IF (ZN([CTR]) - LOOKUP(ZN([CTR]), -1)) / ABS(LOOKUP(ZN([CTR]), -1)) > 0 THEN 'Up'

else 'Down'

END

Any suggestions?

• ###### 1. Re: Percent difference calculation

Hi Ryan,

Use the sum for CTR if it is not aggregated in the calculated field like below

IF (ZN(SUM([CTR])) - LOOKUP(ZN(SUM([CTR])), -1)) / ABS(LOOKUP(ZN(SUM([CTR])), -1)) > 0 THEN 'Up'

else 'Down'

END

or

Change the compute using because you are using the table calculation so make sure the your addressing and partitioning is correct for the LOOKUP function to calculate

Hope this helps

Plz mark this answer as correct or helpful to close the thread

BR,

NB

• ###### 2. Re: Percent difference calculation

Thanks for the reply!

CTR is a calculation I created that takes (sum of clicks)/(sum of impressions).

When I do SUM or AVG in the Up or down calculation, I get an error that says CTR is already an aggregate.

I know the % dif calculation is correct because I am getting the right numbers.

What do you mean by addressing and partitioning?