Welcome Guest! To enable all features please Login or Register.
Go to last post Go to first unread
Judy Jacques  
#1 Posted : Friday, September 26, 2014 4:31:27 PM(UTC)
Judy Jacques
Rank: Member

Groups: extranet\Forum
Joined: 7/10/2013(UTC)
Posts: 5

I need help with a calculation. Here’s what I’m trying to do.
I created comparisons to calculate YTD for current year and previous year. I use those to calculate the amount of variance in dollars and cents. Then, I want to calculate the percentage of variance. I have to do some extra work here to avoid dividing by zero.

1) YTD 2014 = sum(d-1, 0, m1)
2) YTD 2013 = sum(d-4, 0, m1)
3) FY 2013 = sum(d-3, 0, m1)
4) YTD Var = sum(c1, 0, m1) – sum(c2, 0, m1)
5) YTD Var % = if ((sum(c2, 0, m1) <= 0) and (sum(c4, 0, m1) > 0)) then 1
else (sum(c4, 0, m1) / sum(c2, 0, m1))

We want to show 100% increase if last year is less than or equal to zero and this year is greater than zero. It’s this part that is not always working. I have 1 account that is getting a huge number for the percentage. Here it is:
YTD 2014 = 228.46
YTD 2013 = 0.00
YTD Variance = 228.46
YTD Variance % = 1.60764432948291E+18

I’ve done some other testing that indicates that the YTD 2013 is not being seen as equal to zero even though it displays as zero.
There was another account that had this issue, but that one now shows 100% with no changes to the calculation even though the sales totals are the same as last week.

Any suggestions?

Thank you for any assistance!!
#2 Posted : Monday, September 29, 2014 9:54:11 AM(UTC)
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 514

Hi Judy,

It seems like your problem is due to a common issue when working with numeric values from a Data Warehouse. What should be zero might in fact just be rounded to zero with a slight positive or negative deviation e.g. on the 16th decimal.

I suggest you change your calculation number 2 to something like this:

2) YTD 2013 = if (sum(d-4, 0, m1) < 0.00001 and sum(d-4, 0, m1) > -0.00001) then 0 else sum(d-4, 0, m1)

Maybe likewise with calculation number 1. How many decimals you want to include, is up to you.

BR / Ole

Edited by user Monday, October 6, 2014 1:57:40 PM(UTC)  | Reason: Not specified

Ole Dyring
Judy Jacques  
#3 Posted : Monday, September 29, 2014 7:45:59 PM(UTC)
Judy Jacques
Rank: Member

Groups: extranet\Forum
Joined: 7/10/2013(UTC)
Posts: 5

Thank you very much for your reply. I don't understand it, but it worked. The account that had the problem had purchased 3 items and returned them all for credit during the YTD 2013 time range, so their sales total really should be zero.

Thank you!

Users browsing this topic
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.



We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.