You need to sign in to do that

Don't have an account?

Roger Wicki

# Accuracy in APEX

Hi Community

I've got this business problem that we need to have a field on OpportunityLineItem (OLI for short) which represents it's revenue value. But first I need to give you insights on how we set up our model.

We usually have several OLIs on our Opportunities (Opp for short). We grant discounts on 3 levels:

- Difference between List- & Salesprice (= Product Discount, irrelevant here)

- Flat discount amount (= Special Discount Flat)

- Percentage based amount (applied after subtraction of Special Discount Flat, resulting in Special Discount Amount)

Because we often sell our clients packages including multiple product families and we need to get insights on revenue by product family and sales rep, we need this additional information on OLI.

So here's what I do:

On every after trigger action on OLI I recalculate the revenue values of each OLI. On after Opp update with changes in discount fields I invoke the same piece of code including only relevant records (unfinished).

I have a field on OLI that calculates it's share percentage off the Opp amount. So if my OLI would be 2'500 and my Opp amount 10'000 the Share would be 25%. Let's say, this Opportunity also has a Total Discount of 2'000 resulting in 8'000 Opp Amount. Those 2000 need to be distributed over the OLIs according to their share. So the aforementioned OLI would receive 2000 * 25% = 500 of the total discounts as a subtraction to it's value. The resulting revenue value of the OLI would then be 2'500 - 500 = 2'000.

Of course, we have a special case. One Product family may not get Opp discounts applied to unless all OLIs of the Opp are of that product family. So what I do here is gather the OLI share amounts of that product family, add it on top of the total Opp discount and redistribute that total Opp discount on all other OLIs.

I know this is really confusing to imagine so I made a google spreadsheet with a calculation example you can find here (https://docs.google.com/spreadsheets/d/1KZgGShICwAEEqreEt5CNg5ZMinAt165C1qUjOCHNjfI/edit?usp=sharing).

Now my problem is, the code works, but it generates too much inaccuracy. I know that calculating with Double values and percentages will inevitably generate some inaccuracy, but I'd never have imagined that it would generate an inaccuracy of about 3 to 10 on an amount of 7200. Funnily, the inaccuracy seems to be in junction with the Data types I use. I use double as a main container for values and sub results. However, the Share percentage is in decimal format. I have to transform the percentage (3,15) number into a double.

A strange thing I found out is that if I add floating point zeroes to variables I declare, the revenue value changes into another inaccurate result. Being a double, the amount of zeroes added to the variable during declaration should not matter at all...

Anyway, what I find even more strange is that the google spreadsheet seems to be either more precise or is just intelligent enough to overcome the inaccuracies.

Here is my code:

I've got this business problem that we need to have a field on OpportunityLineItem (OLI for short) which represents it's revenue value. But first I need to give you insights on how we set up our model.

We usually have several OLIs on our Opportunities (Opp for short). We grant discounts on 3 levels:

- Difference between List- & Salesprice (= Product Discount, irrelevant here)

- Flat discount amount (= Special Discount Flat)

- Percentage based amount (applied after subtraction of Special Discount Flat, resulting in Special Discount Amount)

Because we often sell our clients packages including multiple product families and we need to get insights on revenue by product family and sales rep, we need this additional information on OLI.

So here's what I do:

On every after trigger action on OLI I recalculate the revenue values of each OLI. On after Opp update with changes in discount fields I invoke the same piece of code including only relevant records (unfinished).

I have a field on OLI that calculates it's share percentage off the Opp amount. So if my OLI would be 2'500 and my Opp amount 10'000 the Share would be 25%. Let's say, this Opportunity also has a Total Discount of 2'000 resulting in 8'000 Opp Amount. Those 2000 need to be distributed over the OLIs according to their share. So the aforementioned OLI would receive 2000 * 25% = 500 of the total discounts as a subtraction to it's value. The resulting revenue value of the OLI would then be 2'500 - 500 = 2'000.

Of course, we have a special case. One Product family may not get Opp discounts applied to unless all OLIs of the Opp are of that product family. So what I do here is gather the OLI share amounts of that product family, add it on top of the total Opp discount and redistribute that total Opp discount on all other OLIs.

I know this is really confusing to imagine so I made a google spreadsheet with a calculation example you can find here (https://docs.google.com/spreadsheets/d/1KZgGShICwAEEqreEt5CNg5ZMinAt165C1qUjOCHNjfI/edit?usp=sharing).

Now my problem is, the code works, but it generates too much inaccuracy. I know that calculating with Double values and percentages will inevitably generate some inaccuracy, but I'd never have imagined that it would generate an inaccuracy of about 3 to 10 on an amount of 7200. Funnily, the inaccuracy seems to be in junction with the Data types I use. I use double as a main container for values and sub results. However, the Share percentage is in decimal format. I have to transform the percentage (3,15) number into a double.

A strange thing I found out is that if I add floating point zeroes to variables I declare, the revenue value changes into another inaccurate result. Being a double, the amount of zeroes added to the variable during declaration should not matter at all...

Anyway, what I find even more strange is that the google spreadsheet seems to be either more precise or is just intelligent enough to overcome the inaccuracies.

Here is my code:

private void startProcess() { // Based on oppMap Ids, collect all relevant OLIs, exit if nothing to process newOLIs = getAllOlis(oppMap.keySet()); // newOLIs is map<Id,OpportunityLineItem> // Create the oppOliMap for splitting oppOliMap = getOppOliMap(newOlis.values()); // oppOliMap is map<OppId,list<OpportunityLineItem>> list<OpportunityLineItem> oliUpdate = new list<OpportunityLineItem>(); for ( Id oppId : oppOliMap.keySet() ) { Integer numMDBs = 0; Double discountStackMDB = 0.0; Double nonMDBShare = 0.0; Double nonMDBMultiplier = 0.0; // Get the number of MDBs and their total discount share for ( OpportunityLineItem oli : oppOliMap.get(oppId) ) { if ( oli.ProductFamily__c.contains('MDB') || (oli.ProductFamily__c.contains('Membership') && oli.ProductFamily__c.contains('Designboom')) ) { numMDBs++; discountStackMDB += getShareAmount(oppMap.get(oppId), oli.Share__c); } else { nonMDBShare += (oli.Share__c.doubleValue() / 100.0); } } nonMDBMultiplier = 1.0 / nonMDBShare; if ( numMDBs == 0 || numMDBs == oppOliMap.get(oppId).size() ) { for ( OpportunityLineItem oli : oppOliMap.get(oppId) ) { oli.Revenue__c = oli.Salesprice_Total__c - getShareAmount(oppMap.get(oppId), oli.Share__c); if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) ) { oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' + 'tell him immediately'); } else { oliUpdate.add(oli); } } } else { for ( OpportunityLineItem oli : oppOliMap.get(oppId) ) { if ( oli.ProductFamily__c.contains('MDB') || (oli.ProductFamily__c.contains('Membership') && oli.ProductFamily__c.contains('Designboom')) ) { oli.Revenue__c = oli.Salesprice_Total__c; if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) ) { oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' + 'tell him immediately'); } else { oliUpdate.add(oli); } } else { oli.Revenue__c = oli.Salesprice_Total__c - getShareAmount(oppMap.get(oppId), oli.Share__c) - (discountStackMDB * (oli.Share__c.doubleValue() / 100.0) * nonMDBMultiplier); if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) ) { oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' + 'tell him immediately'); } else { oliUpdate.add(oli); } } } } } try { update oliUpdate; if ( Test.isRunningTest() ) { System.debug('Throwing Error intentionally'); insert new Case(); } } catch(System.DMLException dex) { System.debug('There was an error during the OLI Update'); } }Auxiliary method used:

private static Double getShareAmount(Opportunity opp, Decimal share) { Double result = 0.0; if ( opp.SpecialDiscountFlat__c != NULL ) { result += opp.SpecialDiscountFlat__c; } if ( opp.Special_Discount_Amount__c != NULL ) { result += opp.Special_Discount_Amount__c; } // Salesforce treats percentage fields as simple decimals: 7.68% == 7.68 and not 0.0768 result *= (share.doubleValue() / 100.0); return result; }

Harish NarayananHi Roger,

I couldn't get my head around the concept of non-MDB multiplier and why the values in Column F. But from a logical perspective, I could see 2 things though I may be wrong.

1. Code on Line #004 --> You are returning the result of keySet() to another Map whereas it returns a Set with all of the keys in the map. 2

2. Method definition of

getShareAmount--> You check for bothandspecialdiscountflatusing 2 sets of "if" rather if-else ladder. in the event when both parameters are set as shown in your excel sheet, the calculation ofspecialdiscountamountwill always be overridden. So I reckon, this might be causing wrong results while calculating shares.specialdiscountflatSee if these 2 suggestions makes sense to you. I am yet going thru the rest of the code, so if i find something of mismatch, will post it here.

Cheers

Harish

## All Answers

Harish NarayananHi Roger,

I couldn't get my head around the concept of non-MDB multiplier and why the values in Column F. But from a logical perspective, I could see 2 things though I may be wrong.

1. Code on Line #004 --> You are returning the result of keySet() to another Map whereas it returns a Set with all of the keys in the map. 2

2. Method definition of

getShareAmount--> You check for bothandspecialdiscountflatusing 2 sets of "if" rather if-else ladder. in the event when both parameters are set as shown in your excel sheet, the calculation ofspecialdiscountamountwill always be overridden. So I reckon, this might be causing wrong results while calculating shares.specialdiscountflatSee if these 2 suggestions makes sense to you. I am yet going thru the rest of the code, so if i find something of mismatch, will post it here.

Cheers

Harish

Roger WickiHi Harish

Thanks for your replies and thoughts so far. I should probably have put the getAllOlis() function in there too.

The oppMap has been used before "startProcess()" to filter out opportunities which do neither have a

Special Discount Flatnor aSpecial Discount Amount. Then, because I may only have change one OLI's Salesprice, all OLIs' Revenue can be affected. Because in trigger.new I do not have all OLI's I need to get them with getAllOlis().Perhaps it was also unclear that

SpecialDiscountFlat__candSpecial_Discount_Amount__care pretty independant in functionality. See following quote:We usually have several OLIs on our Opportunities (Opp for short). We grant discounts on 3 levels:- Difference between List- & Salesprice (= Product Discount, irrelevant here)

- Flat discount amount (= Special Discount Flat)

- Percentage based amount (applied after subtraction of Special Discount Flat, resulting in Special Discount Amount)

So with an Opp Amount of 8300, a SpecialDiscountFlat of 300 and a Discount Percentage of 10% the SpecialDiscountAmount is

I didn't want to copy in all my code because it's quite a beast in total :D800 (= (8300 - 300) * 10%).

For completion here is the code for the method getOppOliMap():

To your second point:

If I would use an if-else ladder, I would not be able to cover the case where I have both kinds discounts. This is actually a rarity but I don't want business exceptions to break my code ;) I only use an if-statement at all because Salesforce treats empty fields as NULL values instead of zeroes which could easily cause NullPointerExceptions if not handled that way. Otherwise I would simply use

But that's not possible.

Also note that I use a "+=" assignment. So it takes the former value and adds the new value on top and therefore I don't override anything ;)

Thanks for having a look at it ;)

Roger WickiHey Harish

Let's make an easy example:

I forgot to answer on this part of your comment:

I couldn't get my head around the concept of non-MDB multiplier and why the values in Column F. But from a logical perspective, I could see 2 things though I may be wrong.- 5 OLIs, all with Salesprice of 2'000

- Total Opportunity Amount is 10'000

- So every OLI has a share of 20%

- Total Opportunity Discounts sum up to 4000

- So every OLI has a share amount of 20% * 4000 = 800

- One of the OLIs is MDB, which must not receive any discounts and its 800 reserved discount needs to be distributed among the other OLIs

- So the sum of non-MDB-Shares is 80%

--> Here I see that I may have made a step too much with the MDB Multiplier. The idea was that every OLI has a certain weight in those 80%, according to its share. In this example, all OLIs share the same weight of 20% off 80%. The non-MDB-multiplier has the idea of "with how much do I need to multiply the individual share values, excluding MDB, to get 100%?". But I might as well just calculate 0.2 / 0.8 without the need of an additional variable.

Your thoughts might even narrow down the inaccuracy as 1 / 0.8(± 1.0 * 10^-6) has a much bigger error (1.6 * 10^-9) than 0.2 / 0.8 (± 1.0 * 10^-6) resulting in an error of 3.0 * 10^-10.

The values of column F were the OLI's weight in the non-MDB-Share multiplied by the MDB share amount. ( (0.2 / 0.8) * 800 = 200 ; logically, because if I need to distribute 800 over 4 "buckets of the same size" i receive 200 ).

I'll give this a try and let you know ;)

Roger WickiYou are a genious! One hint to rule them all ;) It proves my addError() text is not for nothing because it ended up being a math problem... Oh well. I really thank you for thinking through the concept with another perspective! It was worth it ;) And thank you for your time. Because I know that our business logic is pretty complicated sometimes :P