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

    Percent difference calculation

    Ryan Pesso

      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'




      Any suggestions?

        • 1. Re: Percent difference calculation
          Naveen B

          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'




          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




          • 2. Re: Percent difference calculation
            Ryan Pesso

            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?