Get Free Trial

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

Ravi asked 5 days ago

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

1 answer

Nadia Khodakivska Nadia Khodakivska Flexmonster 4 days ago

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: 
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:

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

Kind regards,

Please login or Register to Submit Answer