Get Free Trial

count conditional

Jose Antonio Veintimilla asked on May 17, 2018

How could I count the values that meet a condition? I speak of alphanumeric conditions. I have tried with an example of yours but I can not mount it correctly:
var jsondata = [{
“Color”: “green”,
“M”: “September”,
“W”: “Wed”,
“country”: “Canada”,
“state”: “Ontario”,
“city”: “Toronto”,
“Price”: 174,
“Quantity”: 22,
“Svc Cd”: 125
}, {
“Color”: “red”,
“M”: “March”,
“W”: “Mon”,
“Time”: “1000”,
“country”: “USA”,
“state”: “California”,
“city”: “Los Angeles”,
“Price”: 30,
“Quantity”: 19,
“Svc Cd”: 124
var pivot = new Flexmonster({
container: “pivot-container”,
componentFolder: “”,
width: “100%”,
height: 430,
toolbar: true,
report: {
dataSource: {
data: jsondata
slice: {
pages: [{uniqueName: “city”}],
rows: [{uniqueName: “Color”}],
columns: [{uniqueName: “[Measures]”}],
measures: [{uniqueName: “city”}, {uniqueName: “Formula #1”, caption: “Formula #1”, individual: true, formula: “if(sum(‘city’) == ‘Los Angeles’, 1, 2)”, active: true}]
formats: [{name: “”, nullValue: “blank”}]

5 answers

Iryna Kulchytska Iryna Kulchytska Flexmonster May 17, 2018

Hi Jose Antonio,
Thank you for posting your question.
The comparison with string value like “Los Angeles” is not available in calculated values. When using string hierarchies in calculated values, you can use only count aggregation and compare it with numbers.
Also, the following thread on our forum can be useful for your case:
Please let me know if you have any questions.
Kind regards,

Jose Antonio Veintimilla May 17, 2018

If it seems good, I send you a screen dump of the final result that I am trying to obtain. In case you could give me some help for this case:
var nominal = [{“idReg”:”393465″,”codigoCamp”:”0001″,”descCamp”:”description1″,”finca”:”one”,”estado”:”Pendiente”,”CUPS”:”ES0217010029647071HX”},
pivot = new Flexmonster({
container: “pivotContainer”,
componentFolder: “assets/js/plugins/flexmonster/”,
//global: {
// localization: “es.json”
width: “100%”,
height: 640,
toolbar: true,
report: {
localization: es,
dataSource: {
dataSourceType: “json”,
data: nominal
options: {
datePattern: “DD/MM/yyyy”
slice: {
pages: [
{ uniqueName: “codigoCamp”, caption: “Código Campaña” },
{ uniqueName: “estado”, caption: “Estado” },
{ uniqueName: “finca”, caption: “Finca” }
rows: [
{ uniqueName: “descCamp”, caption: “Campaña” }
columns: [
{ uniqueName: “estado”, caption: “Estado” }
measures: [
{ uniqueName: “CUPS”, caption: “Cuenta”, formula:”count(‘CUPS’)”, active: true }
expandAll: true,
drillAll: true,
licenseKey: “hidden”,
beforetoolbarcreated: customizeToolbar


Tanya Gryshko Tanya Gryshko Flexmonster May 18, 2018

Hello, Jose Antonio,
Thanks for the quick reply and providing your data source.
Could you please share some additional details about the final result you’re trying to achieve?

Jose Antonio Veintimilla May 21, 2018

the idea is the following:
Insert in columns the field “ESTADO”: show different ‘ESTADOS’ and count them. When double-clicking show detail (drillDown).
Insert in columns the field “FINCA”: count number of ‘FINCA’ (regardless of the state).
Insert column “ALERTAS”: count number of “ALERTAS”
Insert column of aggregate with the operation of ((Estado.Realizada + Estado.”Imposible Realizar”) / Alertas)*100

Captura (1).jpg

Dmytro Zvazhii Dmytro Zvazhii Flexmonster May 22, 2018

Hello Jose,
Thank you for the detailed explanation. Please find our answers below:
1. It seems that you have successfully added “ESTADOS” field to the column area and it works as expected.
2. Speaking about adding a calculated value based on “FINCA” field on the same level with “ESTADOS” field, we have to inform you that it is not possible to achieve that due to the architecture of our grid. The architecture of our grid does not support such type of behavior. The only possible workaround here is to show a separate grid with calculated values only and without “ESTADOS” field in the column area. Here is the example:
3. Here is the same recommendation as for the previous one. It is not possible to add some different independent field on the same level with “ESTADOS” field.
4. Our component does not support such type of formula. You cannot refer to field’s member when writing a formula.
Please let us know if there is anything else we can help you with.

This question is now closed