Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 | - |
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.
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])
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])
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