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.
Calculations: 1) YTD 2014 = sum(d1, 0, m1) 2) YTD 2013 = sum(d4, 0, m1) 3) FY 2013 = sum(d3, 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!!




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 323

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(d4, 0, m1) < 0.00001 and sum(d4, 0, m1) > 0.00001) then 0 else sum(d4, 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 
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S. Ole Dyring 



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!
Judy




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.