Calculated column expression - Choose the highest number

[Nicholas Witham]

Hi All, I've just started using Spotfire (v10. something) and am a bit stuck on a problem.

I have 2 columns where I want my graph to only read the highest number from those 2 columns on a single row. All figures in the cells are numbers (1 to 5), but some cells are blank.

I have gone down the path of creating a calculated column, and am trying to use an expression to have the new column return the highest value from the 2 major columns.

The problem is that I can't get my expression quite right......I believe there's something wrong with my logic, or the order in which I do my if statements.

I'm hoping someone in here might be able to help with my expression, or even better, guide me to a way to have my graph use only the highest number from my 2 major columns on a single row.

Expression:

If(([safety Actual Severity]="") And ([Health Actual Severity]=""),"",

If(([safety Actual Severity]!="") And ([Health Actual Severity]=""),[safety Actual Severity],

If(([safety Actual Severity]="") And ([Health Actual Severity]!=""),[Health Actual Severity],

If([safety Actual Severity]>[Health Actual Severity],[safety Actual Severity],[Health Actual Severity]))))I've also attached a snippet of my major and calculated columns, with the expression in use.

 

Thanks in advance.

Nick.

[Fabian Duerr]

Probably the cells are not blank but NA, so check with is.na function.

Moreover you could simply use the max function: Max([Health Actual Severity],[safety Actual Severity])

[Nicholas Witham]

Thankyou very much.

Your suggestion is much more elegant.

 

Nick

[Fabian Duerr]

Sure. You are welcome.