Formula Calculations - Commissions

[khopper]

I am having trouble with a Formula to calculate Commissions under Entity Accounts, am I doing something wrong? Can i limit the calculations to 2 decimal places? USD
I know its a lot but, just trying to be as detailed as possible.

Problem # 1 - the Calculation is off by decimal places for the total on field commissions.
Problem # 2 - the Calculation below if I change filed voice to anything other than 'New" it does not finish the calculation for the other 2 fields 'voice' and 'internet' and just enters a 0 or null value.

I was able to duplicate all of this in the Demo CRM at http://demo.espocrm.com/advanced/?lang=en_US#

field mrc should total $241.35 (correct)

ifThen(
   client == 'COMCAST',
   mrc = (( ccblmrc  +ccfflmrc + ccmlmrc + ccimrc + ccvmrc) - (discountinternet + discountvideo + discountvoice))
);

ifThenElse(
    client == 'COMCAST' && term = '24 Mo',
    commission =
       ifThenElse(
           voice == 'New',
           (ccblmrc + ccfflmrc + ccmlmrc) - discountvoice * 0.5,
           0
       )
       +
       ifThenElse(
           internet == 'New',
           (ccimrc - discountinternet) * 0.5,
           0
       )
       +
       ifThenElse(
           video == 'New',
           (ccvmrc - discountvideo) * 0.5,
           0
       ),
    0
);

example test info for fields:
client = 'COMCAST',
term = '24 Mo',
voice = 'New',
internet = 'New',
video = 'New',
ccblmrc = '24.95',
ccfflmrc = '0',
ccmlmrc = '134.85',
ccimrc = '149.95',
ccvmrc = '81.55',
discountinternet = '70.00',
discountvoice = '79.95',
discountvideo = '0'


commission field calculated from CRM = $119.85 , should equal $120.675 rounded to $120.68

https://www.calculatorsoup.com/calcu...9&action=solve

[yuri]

Hi,

There might be a bug in parser. I will take a look.

[yuri]

Hi,

Try replacing the file application/Espo/Core/Formula/Parser.php with:

https://raw.githubusercontent.com/espocrm/espocrm/a4e23b8adbfd830ad38abeaa0956e69df6e50d17/application/Espo/Core/Formula/Parser.php

[khopper]

Using what you provided its now giving me a result of 319.33 - it went up instead of down.

[yuri]

Check your formula. I calculated manually and got the same result as formula gave.

[khopper]

Total 241.35 / 0.5 = 120.675

Formula: ifThenElse(client == 'COMCAST' && term = '24 Mo', commission = ifThenElse(voice == 'New', ccblmrc+ccfflmrc+ccmlmrc-discountvoice * 0.5,0) + ifThenElse(internet == 'New', ccimrc-discountinternet * 0.5,0) + ifThenElse(video == 'New', ccvmrc-discountvideo * 0.5,0),0);

manually checking:
(ccblmrc = '24.95' + ccfflmrc = '0' + ccmlmrc = '134.85' - discountvoice = '79.95') * 0.5 + (ccimrc = '149.95' - discountinternet = '70.00') * 0.5 + (ccvmrc = '81.55' - discountvideo = '0') * 0.5 =

(24.95+0.00+134.85-79.95) = 79.85 * 0.5 = 39.925
(149.95 - 70.00) = 79.95 * 0.5 = 39.975
(81.55 - 0.00) = 81.55 * 0.5 = 40.75

39.925 + 39.975 + 40.75 = 120.65

Should not = 319.33

[yuri]

I got 120.65.

[khopper]

is there an attachment size limit? I can send an SWF video file so you can witness....

[yuri]

I formatted your code. Please use formatting to make it readable,

ifThenElse(
    client == 'COMCAST' && term = '24 Mo',
    commission = 
           ifThenElse(
            voice == 'New',
            ccblmrc + ccfflmrc + ccmlmrc - discountvoice * 0.5,
            0
        )
          +
          ifThenElse(
            internet == 'New',
            ccimrc - discountinternet * 0.5, 
            0
        )
          +
          ifThenElse(
            video == 'New',
            ccvmrc - discountvideo * 0.5,
            0
           )
      ,
      0
);

[yuri]

There is a discrepancy between your formula and calculation you provided above.

[khopper]

What is the discrepancy you are mentioning? I am following order of operations PEDMAS.

[khopper]

Here is a video: https://ufile.io/xzfx1
using the new Parser.php file and both formulas in the clean readable formats both giving inaccurate calculations.
Please advise, this form doesn't appear to allow uploads larger than 97kb.

[yuri]

(149.95 - 70.00) = 79.95 * 0.5 = 39.975
and
ccimrc - discountinternet * 0.5

(81.55 - 0.00) = 81.55 * 0.5 = 40.75
and
ccvmrc - discountvideo * 0.5

[yuri]

There is exe file, no video.

[khopper]

the EXE is a self executable video. Created using SCREEN2SWF and exported to EXE.
If you would prefer I can send as SWF file.

[yuri]

I fixed your code to correspond to calculations you provided:

ifThenElse(
    client == 'COMCAST' && term == '24 Mo',
    commission =
        ifThenElse(
            voice == 'New',
            (ccblmrc + ccfflmrc + ccmlmrc - discountvoice) * 0.5,
            0
        )
        +
        ifThenElse(
            internet == 'New',
            (ccimrc - discountinternet) * 0.5,
            0
        )
        +
        ifThenElse(
            video == 'New',
            (ccvmrc - discountvideo) * 0.5,
            0
        )
    ,
    commission = 0
);

It gives 120.675 !!!

[khopper]

My apologies - I had several variants of the formula and must have grabbed the wrong one.
So the Formula code will only work in the clean readable format? I can't have it on one line?

[yuri]

It will work in one line. The problem is not in formatting. The problem is in missed parentheses in your code.

[khopper]

Thank you for your patience.
I should keep the modified Parser.php file correct?

[yuri]

Keep Parser.php. It will be added in the next release.