Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
klikgevoel
Contributor III
Contributor III

Latest text values in straight table

Hi all,

I'm trying to show the latest text values in a straight table. However, using FirstSortedValue() or an Aggr() function does not seem to work, ie FirstSortedValue(Column1, -Enddate) or =Aggr(FirstSortedValue(Column1,-Enddate),Identifier,Name). See image below as an example of my data and the solution. 

qlik_question_latest_values_straight-table.PNG

 

Some data as an example to work with:

Main:
LOAD * Inline [
Name, Name-label, Employees, Startdate, Enddate, Column1, Column2, Column3, Identifier
Company1, Company1 16, 200, 01-01-2016, 01-01-2017, 2016 Text in column1, 2016 Text in column2, 2016 Text in column3, 691,
Company1, Company1 17, 180, 01-01-2017, 01-01-2018, 2017 Text in column1, 2017 Text in column2, 2017 Text in column3, 691,
Company1, Company1 19, 150, 01-01-2018, 01-01-2020, 2018 Text in column1, 2018 Text in column2, 2018 Text in column3, 691,
Company2, Company2 16, 1000, 01-01-2016, 01-01-2017, 2016 Text in column1, 2016 Text in column2, 2016 Text in column3, 492,
Company2, Company2 17, 1020, 01-01-2017, 01-01-2018, 2017 Text in column1, 2017 Text in column2, 2017 Text in column3, 492,
Company2, Company2 18, 998, 01-01-2018, 01-01-2019, 2018 Text in column1, 2018 Text in column2, 2018 Text in column3, 492,
];

Labels (3)
2 Replies
JustinDallas
Specialist III
Specialist III

Are you sure your Startdate and Enddate are both Dates?  You may have to run a LOAD *... Date(Startdate, 'MM-DD-YYYY') AS 'StartDateDate'

 

I was able to arrive at your solution using FirstSortedValue i.e FirstSortedValue(Employees, -Enddate) as a measure.

QlikQuestion.png

 

I'm going to try to attach the QVF I've got, but I know the forum LOVES rejecting the upload of such files.

 

 

klikgevoel
Contributor III
Contributor III
Author

Ofcourse this isn't the actual data model I'm eventually working in. But this shows that the date-format isn't being read as an actual date in the current data model. Thanks for the tip 😉