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

# Help with YOY calc

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

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 0ELSEIF SUM([2016]) = 0 AND SUM([2017]) > 0 then 100ELSEIF SUM([2017]) = 0 AND SUM([2016]) > 0 THEN -100ELSE(SUM([2017]-[2016]) / SUM([2016])) * 100END`

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!