2 Replies Latest reply on Mar 2, 2016 9:09 PM by Steve Gesuale

# calculating % difference between calculated fields

Hi,

Looking for help with a pesky calculated field.

Data is at daily level with one measure sales and another sales from the same date for the previous year (Sales YAGO).

These are used in conjunction with a date parameter to calculate the year-to-date sales (YTCD - Sales) and year-to-date for the previous year (YTCD YAGO - Sales).

I’m trying to then calculate the % difference between year-to-date sales and the previous year's year-to-date sales.

Using this calc which seems correct but can’t figure out why it’s returning “incorrect” values.

sum([YTCD - Sales]-[YTCD YAGO - Sales]) / sum([YTCD YAGO - Sales])

For example, Boston has lower sales this year than last but the calc is showing it’s 15% up.

I suspect it's something do with using a calc on top of other calc's?

Would love some help in sorting this out from any experts!

Thanks.

Steve

• ###### 1. Re: calculating % difference between calculated fields

Looks like there are NULL values for TYCD - Sales or YTCD YAGO - Sales, and that is causing some of the sales to be omitted using your current formula.

Both of these alternatives yield the correct result:

This sums YTCD separately from YTCD YAGO, so NULL values don't change the result.

(sum([YTCD - Sales])-sum([YTCD YAGO - Sales])) /

sum([YTCD YAGO - Sales])

This just handles NULL values in your current formula.

sum(ZN([YTCD - Sales])-ZN([YTCD YAGO - Sales]))/

sum([YTCD YAGO - Sales])

-Steve

1 of 1 people found this helpful
• ###### 2. Re: calculating % difference between calculated fields

Hi Steve,