Converting the result of a calculated field to a negative value

(0) Share

Report

Posted on by Community Member

Hi All

I need to create a calculated field that will convert the result into a negative value.

For example, in excel is would use the formula =(a1-b1)*-1 this will convert the result to a negative value.

However, this formula format does not seem to work in CRM on calculated fields or possibly i am not typing the command properly.

Could someone please assist in letting me know how to apply this type of formula in Dynamics 365 for Calculated fields.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

ashlega 34,477 on at

Like (0)

Report

Hi,

did you try

Field2 = 0-Field1

?

Was this reply helpful? Yes No
Verified answer

Moh Helper 250 on at

Like (0)

Report

Hi Byron,

Here a solution who could help you :

In the calculated form :

IF...Else

Condition

a1 <= b1

Action

field = 0 - (a1-b1)

Else

Action

field = a1-b1

Kind regards,

Mohamed

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Very Clever, let me try that

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Worked like a charm, thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi, I saw your response to his query on creating a negative value from a calculated field.

Is there a way to make sure that in a calculated field that we do not create a negative value. That the lowest value is 0? We are using a currency field, and at times we receive more awards than targeted, but we don't want to have the value to dip below the 0 mark.

Currently our equation is simple. It's Target$ - Actual$ = Not Achieved yet (Money). We do not want the Not Achieved Yet (Money) field to ever go below 0. It affects our charting and reporting.

Any help would be appreciated. Thank you

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

You could try a couple of things.

1) Try to make the field's min value 0. I am not sure if this will throw an error on calculation, or if it will just set to 0. You do this in the solution you built the field in.

2) Check the values in your fields prior to doing the calculation. Use the conditions to check the values. You can do something like:

If ((Target$) > (Actual$)) - then you will ONLY get non zero values.

of course, this isn't a great way to do things. You would get incomplete/incorrect data. But, you asked me for a solution - this SHOULD work.

Was this reply helpful? Yes No