Calculation will not recognise numbers greater >10

[GarthT]

Help please

I am trying to create a database for my melanoma cancer patients. We "stage' the patients based on the histological diagnosis; measured tumour thickness (Breslow), whether the skin is ulcerated or not-ulcerated and on the microscopic depth of invasion (clark level). I have written a calculation field for the stage - "Tstage" based on the above variables. The calculation is based on a nested series of "If" calculations

The calculation works perfectly until the tumour thickness exceeds 10. The calculation then fails to recognise the number is >10 and calculates a result as if the thickness was 1. Similarly if the thickness is 23 - the result appears to have calculated as if the thickness was 2.3

The T stage for any tumour >10 should always return a result of "T4b".

Any help would be much appreciated.

I have attached a truncated version of the database to illustrate the problem

Best wishes

Dr Garth Titley

[GarthT]

Help please

I am trying to create a database for my melanoma cancer patients. We "stage' the patients based on the histological diagnosis; measured tumour thickness (Breslow), whether the skin is ulcerated or not-ulcerated and on the microscopic depth of invasion (clark level). I have written a calculation field for the stage - "Tstage" based on the above variables. The calculation is based on a nested series of "If" calculations

The calculation works perfectly until the tumour thickness exceeds 10. The calculation then fails to recognise the number is >10 and calculates a result as if the thickness was 1. Similarly if the thickness is 23 - the result appears to have calculated as if the thickness was 2.3

The T stage for any tumour >10 should always return a result of "T4b".

Any help would be much appreciated.

I have attached a truncated version of the database to illustrate the problem

Best wishes

Dr Garth Titley

[stanley]

Dr Titley:

The quotations around the Breslow numbers were causing the problem (plus a lost set of parenthesis.)

The corrected calculation is:

If(Histological diagnosis = "MM" and Breslow ? 1 and (Ulceration = "no" and (Clark level = "II" or Clark level = "III"));"T1a"; If(Histological diagnosis = "MM" and Breslow ? 1 and (Ulceration = "yes" or (Clark level = "IV" or Clark level = "IV"));"T1b"; If(Histological diagnosis = "MM" and (Breslow > 1 and Breslow ? 2) and Ulceration = "no";"T2a"; If(Histological diagnosis = "MM" and (Breslow > 1 and Breslow ? 2) and Ulceration = "yes";"T2b"; If(Histological diagnosis = "MM" and (Breslow > 2 and Breslow ? 4) and Ulceration = "no";"T3a"; If(Histological diagnosis = "MM" and (Breslow > 2 and Breslow ? 4) and Ulceration = "yes";"T3b"; If(Histological diagnosis = "MM" and (Breslow > 4 and Ulceration = "no");"T4a"; If(Histological diagnosis = "MM" and Breslow > 4 and Ulceration = "yes";"T4b"; "n/a"))))))))

You would be better served to use a Case() statement rather than the nested If() statements, but this works, and it isn't so complicated that it slows things down, so why change it...

-Stanley

[stanley]

Dr Titley:

The quotations around the Breslow numbers were causing the problem (plus a lost set of parenthesis.)

The corrected calculation is:

If(Histological diagnosis = "MM" and Breslow ? 1 and (Ulceration = "no" and (Clark level = "II" or Clark level = "III"));"T1a"; If(Histological diagnosis = "MM" and Breslow ? 1 and (Ulceration = "yes" or (Clark level = "IV" or Clark level = "IV"));"T1b"; If(Histological diagnosis = "MM" and (Breslow > 1 and Breslow ? 2) and Ulceration = "no";"T2a"; If(Histological diagnosis = "MM" and (Breslow > 1 and Breslow ? 2) and Ulceration = "yes";"T2b"; If(Histological diagnosis = "MM" and (Breslow > 2 and Breslow ? 4) and Ulceration = "no";"T3a"; If(Histological diagnosis = "MM" and (Breslow > 2 and Breslow ? 4) and Ulceration = "yes";"T3b"; If(Histological diagnosis = "MM" and (Breslow > 4 and Ulceration = "no");"T4a"; If(Histological diagnosis = "MM" and Breslow > 4 and Ulceration = "yes";"T4b"; "n/a"))))))))

You would be better served to use a Case() statement rather than the nested If() statements, but this works, and it isn't so complicated that it slows things down, so why change it...

-Stanley

[-Queue-]

Your result should be of a text type, not a number. And your tests involving Breslow do not need quotes around them, since they are numbers and not text.

I've taken the liberty of simplifying your nested Ifs into a few nested Cases and removed some redundancies. This should make it cleaner and more obvious and make it easier to expand, if necessary.

Case( Histological diagnosis = "MM";

Case( Breslow <= 1;

Case( Ulceration = "no" and (Clark level = "II" or Clark level = "III"); "T1a"; Ulceration = "yes" or Clark level = "IV" or Clark level = "IV"; "T1b"; "n/a" );

Breslow <= 2;

"T2" & Case( Ulceration = "no"; "a"; "b" );

Breslow <= 4;

"T3" & Case( Ulceration = "no"; "a"; "b" );

"T4" & Case( Ulceration = "no"; "a"; "b" ) );

"n/a"

)

[comment]

I would change it so that mistakes are easily recognized:

----------------------------------------

Case ( Histological diagnosis = "MM" ;

Case (

Breslow 1 and Ulceration = "no" and ( Clark level = "II" or Clark level = "III" ) ;

"T1a";

Breslow 1 and ( Ulceration = "yes" or Clark level = "IV" or Clark level = "IV" ) ;

"T1b";

Breslow 2 and Ulceration = "no" ;

"T2a";

Breslow 2 and Ulceration = "yes" ;

"T2b";

Breslow 4 and Ulceration = "no" ;

"T3a";

Breslow 4 and Ulceration = "yes";

"T3b";

Breslow > 4 and Ulceration = "no";

"T4a";

Breslow > 4 and Ulceration = "yes";

"T4b" ;

"missing data"

) ;

"n/a" )

----------------------------------------

[stanley]

I love it when everyone pitches in. Comment's got a great point with his "missing data" line, which will be returned if one of the evaluated fields is empty.

-Stanley

[comment]

Wait a minute - there seems to be a contradiction:

"The T stage for any tumour >10 should always return a result of "T4b"."

That is NOT how the calc in the file goes. Shouldn't the last case be:

...

Breslow <= 10 and Ulceration = "no";

"T4a";

( Breslow <= 4 and Ulceration = "yes" ) or Breslow > 4 ;

"T4b" ;

...

?

[GarthT]

Thanks everyone - I appreciate the rapid response - I have tried all of your suggestions and they all work!!

In response to Comments last comment (!) - I said "The T stage for any tumour >10 should always return a result of "T4b" because this was where I was having problems - in fact any tumour thickness >=4 should return a result of T4 - so the earlier calculations were correct.

I found the calculation was returning a result when the Breslow field was empty so I have modified the calculation as below and everything seem to work fine.

I the "missing data" text never seems to appear so I am not sure of the relevance of this

Thanks again

Garth T

Case (IsEmpty(Breslow);"n/a";

Case ( Histological diagnosis = "MM" ;

Case (Breslow ? 1 and Ulceration = "no" and ( Clark level = "II" or Clark level = "III" ) ; "T1a";

Breslow ? 1 and ( Ulceration = "yes" or Clark level = "IV" or Clark level = "V" ) ; "T1b";

Breslow ? 2 and Ulceration = "no" ; "T2a";

Breslow ? 2 and Ulceration = "yes" ; "T2b";

Breslow ? 4 and Ulceration = "no" ; "T3a";

Breslow ? 4 and Ulceration = "yes"; "T3b";

Breslow > 4 and Ulceration = "no"; "T4a";

Breslow > 4 and Ulceration = "yes"; "T4b" ;

"missing data") ;"n/a" ))

[comment]

As you can see, we tend to take MM seriously here.

The confusion stems from your saying that "The T stage for any tumour >10 should always return a result of "T4b"." while your calc will return T4a when there's no ulceration.

Your calc seems to agree with what I found at

http://www.cancer.org/docroot/cri/content/cri_2_4_3x_how_is_melanoma_staged_50.asp

Based on that I would suggest the following simplification:

 

Let ( [



T = 

Case (

Breslow > 4 ; 4 ;

Breslow > 2 ; 3 ;

Breslow > 1 ; 2 ;

Breslow > 0 ; 1 ;

"?" ) ;



b = 

Ulceration = "yes" or 

T = 1 and ( Clark level = "IV" or Clark level = "V" ) 

] ;



Case ( 

Histological diagnosis = "MM" ;

"T" & T & Case ( b ; "b" ; "a" ) ;

"n/a"

)

)  

[GarthT]

It's an every increasing problem in UK too.

The staging system you have found is a simplified version - I have attached the most recent for your interest. I am trying to write something that will stage each patient based on this; i.e a TNM stage and then a pathological stage based on this - I am almost there................ but if you are keen!!

I am off skiing tomorrow - might try and tackle this some more on the plane!!

Thanks for your help and advice - maybe I will crack it

Garth

[comment]

I started, but I need more information on the input side of the process - the table is not entirely clear on that.

If you can correct/amend the attached file, i.e. what are the input fields, if their input is restricted to value lists, etc., and return it to me, I believe I can do the output calc part.

[GarthT]

The input fields needed are:Breslow, Ulceration (yes or no); Clark level; Nodes, lymphatic mass; Metastasis, LDH.

The value lists are:

Clark

I

II

III

IV

V

n/a

LDH

Not measured

Normal

Elevated

Nodes

None

1 node

2 to 3 nodes

4 or more nodes

Matted nodes

Lymphatic mass

Positive Nodes: SLNB or ELND

Positive Nodes: TLND or e/c spread

Satellites

In-transit mets

Metastases

None

Distant skin or s/c or nodal

Lung

All other visceral mets

The TNM staging is derived from this and the pStage from the TNM

Thanks - for your help

See you when I get back

Garth T

[GarthT]

I forgot ----- there needs to be a field callled "histological diagnosis" - for the purpose of this staging the content will always be "MM" - but in the main DB - there is a value list with for other cancer types

Garth

[comment]

Here is a draft. Please read the notes.