I generally use the SIGN function as there are three possibilities -- increase, decrease, or no change. So SIGN( SUM([Latest Year Sales]) - SUM([Prior Year Sales]) ) would return 1 for an increase in sales, -1 for a decrease, and 0 for no change.

@vizwiz

2 жыл бұрын

Ah nice! I always forget about that option.

Love this tutorial. Thanks, Andy!

A big thank you Andy

Excellent!

Nice

Hi Andy - How can I handle instances whereby the % change calculation returns NULL so it dissappears from my view all together which isn't what I want. I'd rathr it show "--"

@vizwiz

Жыл бұрын

I'd wrap the calc in the ZN function. Then when it comes to formatting, use this custom format 0.0%;-0.0%;- The third part of this number format is for formatting zero. So ZN will turn the null to zero, then the number format will display it as -

@JD_018

Жыл бұрын

@@vizwiz Hi Andy, that is what I have done. Calculation is: ZN(([Selected Period])-ZN([Previous Period ])) / ZN([Previous Period ]) In addition, I've used your formatting instructions but the calc dissappears all together when null :(

@vizwiz

Жыл бұрын

@@JD_018 That makes sense. If your previous period (denominator) return 0, you should get null since you can't divide by zero.

@JD_018

Жыл бұрын

@@vizwiz can’t I not handle this instance in a way that returns “- -“ rather than it disappearing from the view all together?

@vizwiz

Жыл бұрын

@@JD_018 I don't think so because you are getting an invalid result.