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

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

Answered
Eric 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)
 

3 answers

Public
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,
Vera

Public
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.

Public
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, 
Vera

Please login or Register to Submit Answer