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

Same question as calculated-value-with-if-statement-comparing-string-values/

Eric asked on October 25, 2020

my example:
Data has columns:
Resource Name, TaskType, Hours
TaskType has values such as Work, Monitor, Meeting, TIme-Off, etc.
Work Hours + Monitor Hours is calculated value ‘On-Project Hours’
i.e. I’d like to write this formula for my calculated value:

On-Project Hours = If(Or(“TaskType”==”Work”,”TaskType”==”Monitor”),WorkHours,0)

3 answers

Vera Didenko Vera Didenko Flexmonster October 26, 2020

Hello, Eric,
Thank you for reaching out to us and for providing details.

For such cases, we kindly suggest presenting your string data in numeric format, for example:
TaskType = “Work” = 0
TaskType = “Monitor” = 1
TaskType = “Meeting” = 2
TaskType = “Time-Off” = 3
Then the desired output can be achieved with the following calculated value

uniqueName: "On-Project Hours",
formula: "If(Or( max(\"TaskType\") = 0, max(\"TaskType\") = 1), sum(\"WorkHours\"),0)",
caption: "On-Project Hours",
individual: true

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

customizeCell: function(cell, data) {
if (data && data.hierarchy && data.hierarchy.uniqueName === "TaskType") {
if (data.label === "0") {
cell.text = "Work"
} else if (data.label === "1") {
cell.text = "Monitor"
} else if (data.label === "2") {
cell.text = "Meeting"
} else if (data.label === "3") {
cell.text = "Time-Off"

Please see the following JSFiddle example we have prepared for you: https://jsfiddle.net/flexmonster/czn1uva8/

In addition, 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/
3) https://www.flexmonster.com/question/calculated-value-count-conditionnaly/

Please let us know if this works for you.
Kind regards,

Eric October 27, 2020

I’ve figured out another way to accomplish my goal, but I will certainly use the technique provided in some future case. It sure would be nice to have string-comparison options in the Calculated measures. (both ‘equals’ and ‘Contains’)
In the meantime, we can consider this one resolved.

Vera Didenko Vera Didenko Flexmonster October 28, 2020

Hello, Eric,
We are glad to hear that you’ve found a solution for your case. 
Regarding string comparison options for calculated measures, we have added your request to our customers’ wish list. We will make sure to inform you as soon as anything changes in this regard.
As always, feel free to reach out in case of questions.
Kind regards, 

Please login or Register to Submit Answer