Need a special offer?Find out if your project fits.
+

Calculated fields are not working as expected with conditional formatting and formatting cell

Answered
Ravi asked on June 23, 2022

Hi,
The link attached has a column of Calculated values. In that,

  1. conditional formatting includes empty cells that are highlighted with the conditions. 
  2. On Formating cell (formatting a negative value) is not getting applied

https://jsfiddle.net/ynd0fmp6/37/
 
Thanks
Karthick
 

3 answers

Public
Nadia Khodakivska Nadia Khodakivska Flexmonster June 24, 2022

Hello, Karthick,

Thank you for reaching out to us.

Please find our answers below:
1. The reason why conditional formatting includes empty cells is that they are not really empty. If you remove the property of the format divideByZeroValue: '', there will be a value showing that it was gotten from diving by zero. Currently, this behavior could not be fixed due to the component's architecture. However, we suggest the following workaround:
Firstly, you should set the validateFormulas property of the Options Object to false, so that the following formula is valid. Then, add to your formula a condition: 

{
uniqueName: 'calc_field_growth',
caption: 'Calc Field Growth (%)',
format: 'percentage',
formula: 'if(isNaN(sum("calc_field")), "", (sum("sales") - sum("calc_field"))/ sum("calc_field"))',
}

This way, conditional formatting will not include empty cells. Please check the following JSFiddle for reference: https://jsfiddle.net/flexmonster/wjvobdng/ 
2. Please note that some properties of Format Object are not intended to work with the percent values. At this point, we suggest using the workaround with the calculated measure that allows setting the format. If you create a calculated measure that multiplies the original measure by 100, this calculated measure is an equivalent of the original measure with "isPercent": true. Feel free to check the following example on the JSFiddle: https://jsfiddle.net/flexmonster/kb2s3xuw/

You are welcome to write to us in case further questions arise.

Kind regards,
Nadia

Public
Ravi August 8, 2022

Hi Nadia,
Is there any way we can have two comparisons in the formula with the 'if' condition?
something like the following example formula.

formula: 'if(isNaN(sum("calc_field"))or(sum("calc_field")==0), "", (sum("sales") - sum("calc_field"))/ sum("calc_field"))'

Kind regards,
Karthick 

Public
Nadia Khodakivska Nadia Khodakivska Flexmonster August 9, 2022

Hello, Karthick,

Thank you for contacting us.

Yes, it is possible to have two and more comparisons in the formula with the if condition. Kindly check the following JSFiddle for reference: https://jsfiddle.net/flexmonster/fmsx14rb/ 

Please let us know if you have more questions.

Kind regards,
Nadia

Please login or Register to Submit Answer