Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

Calculated value : Count conditionnaly

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

Please login or Register to Submit Answer