Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
Try
ONLY( {$< [Field 5] ={"$(=MAX({$<[Field 4] = {'AAA'}>} DATE( [Field 5], 'dd-MMM-yyyy')))"}>} [Field 6])
Hi, thank you for the reply, have tried that and it's the same Null value in the field I'm afraid.
Like this?
=Only(If([Field 5]= Max({<[Field 4]={"AAA"}>}TOTAL [Field 5]), [Field 6]))