asked on October 25, 2020
https://www.flexmonster.com/question/calculated-value-with-if-statement-comparing-string-values/
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)
⋅ 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,
Vera
⋅ 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.
⋅ 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,
Vera