Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rubenacampos
Contributor III
Contributor III

Display most recent record in a table

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!

6 Replies
vvira1316
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

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 IDContract StatusContract Due DateContract Completion DatePeriod of PerformanceEvaluation StatusEvaluation TypeEvaluation Due Date
W9126G09D01110002OVERDUE01/28/201710/02/201604/06/2016 - 09/30/2016DraftedFinal01/28/2017
W9126G09D01110002OVERDUE01/28/201710/02/201609/30/2014 - 04/05/2016CompletedInterim-
W9126G09D01110002OVERDUE01/28/201710/02/201609/30/2013 - 09/29/2014CompletedInterim-
W9126G09D01110002OVERDUE01/28/201710/02/201609/30/2012 - 09/29/2013CompletedInterim-
morenoju
Partner - Specialist
Partner - Specialist

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
Specialist II
Specialist II

latestperiod.PNG

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
Specialist II
Specialist II

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 ....

latestperiod.PNG

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
Contributor III
Contributor III

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