🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

count number rows of an AND formula

Michael T asked on September 28, 2020

Wanting to know if it’s possible to calculate boolean function within row for calculated value
For example:
With CSV columns: A, B, C, D, E
Value of each column is 0 or 1 indicating false or true
how can i count number of rows where  value of A and D are both 1?
Thank you

5 answers

Mykhailo Halaida Mykhailo Halaida Flexmonster September 30, 2020

Hi Michael,
Thank you for posting your question.
The described functionality can be achieved using built-in Flexmonster calculated values.
More specifically, you can define a separate measure similar to the following:

"uniqueName": "Formula #1",
  "formula": "if(\"A\" == \"D\" and \"A\" == 1, 1, 0)",
"individual": true,
"caption": "Formula #1"

The measure’s formula counts the row towards the final result if A & D field values are equal between each other and are equal to 1.
Furthermore, the individual property is set to true to ensure that raw values are used to calculate the measure value, instead of aggregated values – this allows the measure total to be calculated in a proper way, instead of comparing the totals of A & D columns.
To demonstrate the approach described above, we’ve prepared a quick JSFiddle sample – feel free to check it out: https://jsfiddle.net/flexmonster/8y52ax3r/ 
Please let us know if this helps.
Best regards,

Mykhailo Halaida Mykhailo Halaida Flexmonster October 7, 2020

Hi Michael,
Hope you’re doing well.
We were wondering if you’ve received our response with suggestions regarding calculated values. Was it helpful, or is there still anything we can help you with?
Please let us know what you think.

Mykhailo Halaida Mykhailo Halaida Flexmonster October 14, 2020

Hi Michael,
How are you?
Just checking in to ask whether you’ve found our previous suggestions helpful.
We would be happy to hear your feedback!
Kind regards,

Michael T October 14, 2020

thank you for your answer. for more complicated boolean logic, are we able to use brackets to help group operators?

"formula": "if((\"A\" == \"D\" and \"A\" == 1) or \"B\", 1, 0)"
Mykhailo Halaida Mykhailo Halaida Flexmonster October 15, 2020

Hi Michael,
Thank you for your response.
Yes, brackets can be used in calculated measures to create formulas of higher complexity – the example you’ve mentioned in your response is valid as well.
We hope this helps.

Please login or Register to Submit Answer