1 Reply Latest reply on Jan 24, 2017 10:58 AM by Jamieson Christian

    Help with YOY calc

    Tim Mullady

      Hello,

       

      I wanted to see if someone can help me with a YOY calculation.

       

      I have a data set with the following columns.  2017 and 2016 are room nights

       

      WEEK#, REGION, DESTINATION, CHAIN, HOTEL, 2017, 2016

       

      I'm calculating YOY properly with the following calculated feild

       

      IF [2016] = 0 AND [2017] = 0 then 0

      ELSEIF [2016] = 0 AND [2017] > 0 then 100

      ELSEIF [2017] = 0 AND [2016] > 0 then -100

      ElSE

      ([2017] - [2016]) / [2016]

      END

       

      Everything thing is working fine for YOY calculation on the Hotel Level but this is the furthest drill down in sheet.   My drill down heirarchy is Region. Destination, Chain, Hotel. How can I get the YOY calculation to function properly in the higher levels?  Now I believe its simply adding the YOY calculations on at the lowest levels instead of calculation its own YOY calc.

       

      Any information would be greatly appreciated

       

      Thanks,

       

      Tim

        • 1. Re: Help with YOY calc
          Jamieson Christian

          Tim,

           

          You should be able to rewrite your formula to act as a simple aggregation calculation, rather than a row-level calculation. Like this:

           

          IF SUM([2016]) = 0 AND SUM([2017]) = 0 then 0
          ELSEIF SUM([2016]) = 0 AND SUM([2017]) > 0 then 100
          ELSEIF SUM([2017]) = 0 AND SUM([2016]) > 0 THEN -100
          ELSE
          (SUM([2017]-[2016]) / SUM([2016])) * 100
          END

           

          That way, it will use whatever is the current level of detail when it performs the aggregations.

           

          By the way, two observations:

          • Your exceptions yield numbers in the range -100 to +100, but your final calculation will yield a number in the range -1.0 to +1.0. I added * 100 to my ELSE line to correct for this.
          • You don't really need the third condition, the one that outputs -100, because the same result will be obtained by the ELSE computation.

           

          Hope this helps!