Display most recent record in a table

[rubenacampos]

I have a data dimension "Period of Performance" for all my accounts that is expressed as "04/06/2016 - 09/30/2016".  How do I write an expression to only display the most recent period of performance in a table? Each account has multiple Periods of Performances (POP) all with varying dates.  I want to display the latest POP for each account.   For example, if I have :

04/06/2016 - 09/30/2016

09/30/2014 - 04/05/2016

08/16/2017 - 08/15/2018

I want to only show the latest one of the group, "08/16/2017 - 08/15/2018", in my display table.

Thank you in advance!

[vvira1316]

May be you can create a flag that will allow you to identify the latest period. You can do that using date fields/period information itself.

If you will give a data sample with other fields then we may be able to provide you the script code/expression that will allow you to do what you are trying to achieve.

[rubenacampos]

Below is a sample of my data.  I want to show the the record with the most recent Period of Performance which is 04/06/2016 - 09/30/2016.

Contract ID	Contract Status	Contract Due Date	Contract Completion Date	Period of Performance	Evaluation Status	Evaluation Type	Evaluation Due Date
W9126G09D01110002	OVERDUE	01/28/2017	10/02/2016	04/06/2016 - 09/30/2016	Drafted	Final	01/28/2017
W9126G09D01110002	OVERDUE	01/28/2017	10/02/2016	09/30/2014 - 04/05/2016	Completed	Interim	-
W9126G09D01110002	OVERDUE	01/28/2017	10/02/2016	09/30/2013 - 09/29/2014	Completed	Interim	-
W9126G09D01110002	OVERDUE	01/28/2017	10/02/2016	09/30/2012 - 09/29/2013	Completed	Interim	-

[morenoju]

Hi,
I had the same thing to do with regard to a record with the most recent timestamp. I was able to get it using:

avg({1}{<link_timestamp={'$(=Max({1}link_timestamp))'}>}record)

I'm not sure if it will work for you since you don't have timestamps but periods. But check if the syntax is helpful.
Regards.

[vvira1316]

Hi,

I may be reposting this as I'm not seeing my latest update.

See attached file that is in latest QS version.

I'm including script and expression here as well.

Script

[MostRecentPeriodDataTemp]:
LOAD
    "Contract ID",
    "Contract Status",
    "Contract Due Date",
    "Contract Completion Date",
    "Period of Performance",
    "Evaluation Status",
    "Evaluation Type",
    "Evaluation Due Date",
    date(Trim(SubField("Period of Performance",'-', 1)), 'MM/DD/YYYY') as "Period Start",
    date(Trim(SubField("Period of Performance",'-', 2)), 'MM/DD/YYYY') as "Period End"  
FROM [lib://MostRecentPeriod/MostRecentPeriodData.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
LOAD
    "Contract ID",
    date(Max("Period Start"), 'MM/DD/YYYY') as "Latest Record Identifier Date"
Resident [MostRecentPeriodDataTemp]
Group By "Contract ID"
;

NoConcatenate
[MostRecentPeriodData]:
LOAD
    "Contract ID",
    "Contract Status",
    "Contract Due Date",
    "Contract Completion Date",
    "Period of Performance",
    "Evaluation Status",
    "Evaluation Type",
    "Evaluation Due Date",
    "Period Start",
    "Period End",
    "Latest Record Identifier Date",
 If([Period Start]=[Latest Record Identifier Date], 1, 2) as "Use  For Record Display"
Resident [MostRecentPeriodDataTemp];

Drop Table [MostRecentPeriodDataTemp];

Expression in table chart

MaxString({<[Use  For Record Display]={1}>} [Period of Performance])

[vvira1316]

Hi,

I may be reposting this as I'm not seeing my latest update. There are some challenges while attaching files with the revamped community. My apology if you are getting too many emails ....

See attached file that is in latest QS version.

I'm including script and expression here as well.

Script

[MostRecentPeriodDataTemp]:
LOAD
    "Contract ID",
    "Contract Status",
    "Contract Due Date",
    "Contract Completion Date",
    "Period of Performance",
    "Evaluation Status",
    "Evaluation Type",
    "Evaluation Due Date",
    date(Trim(SubField("Period of Performance",'-', 1)), 'MM/DD/YYYY') as "Period Start",
    date(Trim(SubField("Period of Performance",'-', 2)), 'MM/DD/YYYY') as "Period End"  
FROM [lib://MostRecentPeriod/MostRecentPeriodData.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
LOAD
    "Contract ID",
    date(Max("Period Start"), 'MM/DD/YYYY') as "Latest Record Identifier Date"
Resident [MostRecentPeriodDataTemp]
Group By "Contract ID"
;

NoConcatenate
[MostRecentPeriodData]:
LOAD
    "Contract ID",
    "Contract Status",
    "Contract Due Date",
    "Contract Completion Date",
    "Period of Performance",
    "Evaluation Status",
    "Evaluation Type",
    "Evaluation Due Date",
    "Period Start",
    "Period End",
    "Latest Record Identifier Date",
 If([Period Start]=[Latest Record Identifier Date], 1, 2) as "Use  For Record Display"
Resident [MostRecentPeriodDataTemp];

Drop Table [MostRecentPeriodDataTemp];

Expression in table chart

MaxString({<[Use  For Record Display]={1}>} [Period of Performance])

[rmadursk]

avg({1}{<link_timestamp={'$(=Max({1}link_timestamp))'}>}record)

This worked for me for the most part. It gave me an error message about nested aggregation.  I didn't need the average so I just removed that from the expression and achieved the desired result for my App.

Thanks