Nested Set Analysis No Aggregation

[krisslax]

Hi, this has been driving me nuts and wondered if anyone would be able to help.

So I have 2 tables, the first is in a table chart on a sheet, and I need to retrieve 2 fields from the 2nd one using set analysis:

Field 1	Field 2	Field 3	Analysis 1	Analysis 2
1	A	V
2	B	W
3	C	X
4	D	Y
5	E	Z

Field 1	Field 4	Field 5	Field 6
1	AAA	01/01/2024	Op1
1	BBB	01/02/2024	Op2
1	AAA	01/03/2024	Op2
2	BBB	01/04/2024	Op1
2	AAA	01/05/2024	Op3
2	BBB	01/06/2024	Op4

In the first table for Analysis 1, I have the following at it works great to retrieve the max date value:

=MAX( {$<[Field 4] = {'AAA'}>} DATE( [Field 5], 'dd-MMM-yyyy'))

In Analysis 2, I need the corresponding Field 6 that matches the Max Date.

My latest attempt was to use this as a nested set analysis without aggregation, but it returns blanks for all records:

ONLY( {$< [Field 5] = {"MAX( {$<[Field 4] = {'AAA'}>} DATE( [Field 5], 'dd-MMM-yyyy'))"}>} [Field 6])

[BrunPierre]

Try

ONLY( {$< [Field 5] ={"$(=MAX({$<[Field 4] = {'AAA'}>} DATE( [Field 5], 'dd-MMM-yyyy')))"}>} [Field 6])

[krisslax]

Hi, thank you for the reply, have tried that and it's the same Null value in the field I'm afraid.

[BrunPierre]

Like this?

=Only(If([Field 5]= Max({<[Field 4]={"AAA"}>}TOTAL [Field 5]), [Field 6]))