# Trouble on complicated Case calculation with multiple conditions and data types (number and date)

Question asked by mgteval on Jan 23, 2019
Latest reply on Jan 24, 2019 by user19752

I am having the hardest time getting a calculation to work and I am thinking I must just be way off base. I have a series of related fields (listed below) that correspond to each other in pairs:

TEST1 and TEST1 Date

TEST2 and TEST2 Date

TEST3 and TEST3 Date

TEST4 and TEST4 Date

etc... through 9

The "TEST" fields are a series of test scores. The "TEST Date" fields are a series of dates when the corresponding test score was earned (e.g. record 1 earned an 80 on 1/1/1999, an 85 on 1/5/1999, etc.).  I am trying to calculate a field that will select the highest earned test score and, in the event of a "tie" (e.g. 80 on 1/1/1999 and 80 on 2/1/2001), will choose the later date. I have the following formula, sorry if its no good:

TESTPrimary=

Let ([

S=Max ( TEST1;TEST2;TEST3;TEST4;TEST5;TEST6;TEST7;TEST8;TEST9);

D=Max (TEST1 Date;TEST2 Date;TEST3 Date;TEST4 Date;TEST5 Date;TEST6 Date;TEST7 Date;TEST8 Date;TEST9 Date; )

];

Case (

S=TEST1 and D=TEST1 Date; TEST1;

S=TEST2 and D=TEST2 Date; TEST2;

S=TEST3 and D=TEST3 Date; TEST3;

S=TEST4 and D=TEST4 Date; TEST4;

S=TEST5 and D=TEST5 Date; TEST5;

S=TEST6 and D=TEST6 Date; TEST6;

S=TEST7 and D=TEST7 Date; TEST7;

S=TEST8 and D=TEST8 Date; TEST8;

S=TEST9 and D=TEST9 Date; TEST9;"NA" )

)

I need this because another field is populating a date based on what is yielded in the above formula.

TESTPrimary Date=

Case ( TESTPrimary=TEST1; TEST1 Date;

TESTPrimary=TEST2;TEST2 Date;

TESTPrimary=TEST3;TEST3 Date;

TESTPrimary=TEST4;TEST4 Date;

TESTPrimary=TEST5;TEST5 Date;

TESTPrimary=TEST6;TEST6 Date;

TESTPrimary=TEST7;TEST7 Date;

TESTPrimary=TEST8;TEST8 Date;

TESTPrimary=TEST9;TEST9 Date;

"N/A")

I am up for any suggestions here. Thanks for the help!