This content has been marked as final.
Show 1 reply

1. Re: Help with YOY calc
Jamieson Christian Jan 24, 2017 10:58 AM (in response to Tim Mullady)Tim,
You should be able to rewrite your formula to act as a simple aggregation calculation, rather than a rowlevel 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
ENDThat 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!