I have it thanks
Calculating Margin and margin %
Hello,
I have added number fields in my Task. I want to calculate my margin based on this fields, for that I have this as Post- Function.
Margin: Field Margin will be assigned with the calculation result of the formula: ({Buy Rate 1}{Percentage 1})+({Buy Rate 2})+({Buy Rate 3}{Percentage 3})+({Buy Rate 4})+({Buy Rate 5}{Percentage 5})+({Buy Rate 6})+({Buy Rate 7}{Percentage 7})+({Buy Rate 8})+({Buy Rate 9}{Percentage 9})+({Buy Rate 10}) This feature will be run as user in field Current user.
Margin %
Field Margin (%) will be assigned with the calculation result of the formula: ({Sell Rate}-{Margin})/{Margin} This feature will be run as user in field Current user.
I'll get an error, I think the reason for that is, that some fields sometimes are not set, so they became the default value 0. How can I say, let the fields which are not set.
Official response
Comments (12)
-
reporter -
reporter - changed status to resolved
done
-
repo owner Hi Seher,
I will answer anyway, since it can be useful for other users: you should replace the field codes that you think may be uninitialized with the following expression in your formula:
({nnnnn} = null ? 0 : {nnnnn})
where nnnnn is field code of the field that may be uninitialized.
UPDATE:
With version 2.2.16 you can use a much more compact syntax for summing numeric fields that may be uninitialized.
You can use
sum([{10000}, {10001}, {10003}])
instead of
({10000} = null ? 0 : {10000}) + ({10001} = null ? 0 : {10001}) + ({10003} = null ? 0 : {10003})
being {10000}, {10001} and {10003} field codes for 3 numeric custom fields.
-
reporter - changed status to open
Hello Fidel,
after the Update, it doesn't work. I have now this:
and this error:
Best regards,
Seher
-
repo owner From which version to which version have you updated?
Can you please, copy+paste the formula in a comment? I would like to have it in text format in order to manipulate it.
Is it possible that your formula for Margin returns zero? I.e, I want to know whether a zero is a legal value for margin?
-
reporter Hello Fidel,
the last time I have a problem with Jira (I could not create projects). They said it's because of the updated versions and we restart jira. After the restart process, most of the math expressions were not in the post-functions. So I add the same again and get this error.
Margin (({10709} = null ? 0 : {10709}) * ({11003} = null ? 0 : {11003})+({11026} = null ? 0 : {11026}) * ({11035} = null ? 0 : {11035})+({11027} = null ? 0 : {11027}) * ({11036} = null ? 0 : {11036})+({11028} = null ? 0 : {11028}) * ({11037} = null ? 0 : {11037})+({11029} = null ? 0 : {11029}) * ({11038} = null ? 0 : {11038})+({11030} = null ? 0 : {11030}) * ({11039} = null ? 0 : {11039})+({11031} = null ? 0 : {11031}) * ({11040} = null ? 0 : {11040})+({11032} = null ? 0 : {11032}) * ({11041} = null ? 0 : {11041})+({11033} = null ? 0 : {11033}) * ({11042} = null ? 0 : {11042})+({11034} = null ? 0 : {11034}) * ({11043} = null ? 0 : {11043}))/100
Margin %
(({11000}-{11005})/{11005})*100
I've tested margin with some values, I add values in some of the fields. It calculates right, but if percentage is 0 and only buy rate is set, I'll get the error.
Best regards, Seher
-
repo owner You will not lose your post-function's configurations when you update JIRA or JIRA Workflow Toolbox. I don't know what problem you had, but if all works as expected all the configuration will be preserved.
I think that the problem you are experiencing is due to Margin is returning a zero (in some cases), and in the next post-function (i.e., the one for calculating Margin %) you are dividing by Margin. This causes the classical divide by zero problem. In any language or system divisions by zero are forbidden.
Please, try the following formula in your second post-function:
{11005} > 0 ? (({11000}-{11005})/{11005})*100 : 0
-
reporter Hello Fidel,
I don't know I'll still get it. But for some I don't have the margin. I have three options in my Workflow and in one I have no margin. I add the margin function in my 'done' transition and get perhaps because of that this error. Can I fix it?
Best regards,
Seher
-
repo owner Try keeping your original formula in your second post-function (i.e. (({11000}-{11005})/{11005})*100), and setting Conditional execution parameter in your second post-function with the following boolean expression:
{11005} > 0
this way Margin % will only be set when Margin is greater than zero, and thus avoiding the divide by zero problem.
-
reporter Thanks Fidel. This is the right solution.
Best regards,
Seher
-
repo owner - changed status to resolved
Great
-
repo owner With version 2.2.16 you can use a much more compact syntax for summing numeric fields that may be uninitialized.
You can use
sum([{10000}, {10001}, {10003}])
instead of
({10000} = null ? 0 : {10000}) + ({10001} = null ? 0 : {10001}) + ({10003} = null ? 0 : {10003})
being {10000}, {10001} and {10003} field codes for 3 numeric custom fields.
- Log in to comment
Hi Seher,
I will answer anyway, since it can be useful for other users: you should replace the field codes that you think may be uninitialized with the following expression in your formula:
where nnnnn is field code of the field that may be uninitialized.
UPDATE:
With version 2.2.16 you can use a much more compact syntax for summing numeric fields that may be uninitialized.
You can use
instead of
being {10000}, {10001} and {10003} field codes for 3 numeric custom fields.