How do I pull the value of a field where there is a Max Date for that row?

[#1]

Hi everyone,

I am trying to pull in Flash Reports which all link together by the Project Number. So one project can have multiple Flash Reports. I want to display the value of the a particular field according to the max date.

The problem is, I currently have flash reports for march 2013 for all of the projects as an example, but if one project has a flash report for october 2013, then it is the max date and therefore it pulls in that field but only for that project and not the others. Each project should have it's own max date and diffrentiate from one another so that it pull in literally the field according to that max date of that particular project (or row)..

Does someone know how to do this?

Many thanks,

Bruno Pereira

[MK_QSL]

Can you share your sample data or apps please?

[#3]

I have something similar to this. There would be an individual excel sheet for each row of the second table. I just done it like this to simplify.

So for project number 1, I would expect the RAG to be 2. For Project 2, I would expect 3. And for project 3, I would expect 2.

Project Number	Name	Date
1	Test 1	01/02/2014
2	Test 2	01/02/2014
3	Test 3	01/02/2014

Project Number	Date	RAG Status
1	03/03/2014	1
1	05/04/2014	2
2	01/03/2014	3
2	09/04/2014	1
2	02/05/2014	3
3	14/03/2014	1
3	20/04/2014	2

[MK_QSL · Accepted solution]

Create a straight table

Dimension =

Project Number

Expressions

FirstSortedValue(Date,-Date)

and

FirstSortedValue([RAG Status],-Date)

[#5]

Thank you Manish! You're a genius!