Calculated value : Count conditionnaly

posted on December 17th, 2019

Answered

Nans Plancher asked on December 17, 2019

Hi,
I'm trying to make a POC in order to use flexmonster in our app.
I'd like to sum the values of a cell depending on a condition based on another row's cell value. The equivalent in SQL would be:
SELECT sum(a) WHERE b = 'OK'
I can't find how to do it with calculated value. Is there another way, even tricky, to do it ?
Regards

3 answers

Public

Vera Didenko ⋅ Flexmonster ⋅ December 17, 2019

Hello,

Thank you for writing to us.

For fields containing numeric values, the desired output can be achieved with the IF(condition, true_scenario, false_scenario) formula, for example:

if( sum("b") != 0, sum("a"), )

Please consider presenting your string data in numeric format, for example:
b = "OK" = 1
b = "ELSE" = 0

If desired, the numeric values can then be transformed back with customizeCell():

customizeCell: function(cell, data) { 
   if(data && data.hierarchy && data.hierarchy.uniqueName === "b"){
        if(data.label === "1"){
            cell.text = "OK"
        }else{
            cell.text = "ELSE"
        }
   } 
}

We have prepared a JSFiddle example for illustration: https://jsfiddle.net/flexmonster/eLzb0ax6/

Please let us know if this works for you.

Also, the following threads may be of help:
1) https://www.flexmonster.com/question/use-calculated-values-with-conditions-on-multiple-colums/
2) https://www.flexmonster.com/question/how-to-have-a-if-calculated-field-but-returning-text/

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

Best regards,
Vera

Public

Nans Plancher ⋅ December 17, 2019

Hi again Vera,
I figured out I could fulfill my needs by adding a column, which splits the 'grand total' into the differents groups I wanted to count.
However, the case you explained to me offers much more flexibity and will be useful to me in the future.
Thanks a million for your quick and valuable response. From my side, I'm convinced by flexmonster and I hope we'll use the great work you all did here.
Regards,
Nans

Public

Vera Didenko ⋅ Flexmonster ⋅ December 17, 2019

Hello, Nans,

Thank you for your kind feedback.

We are happy to hear that you found our advice helpful.

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

Best regards,
Vera

Calculated value : Count conditionnaly

3 answers

Please login or Register to Submit Answer