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

posted on October 25th, 2020

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

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

3 answers

Please login or Register to Submit Answer