Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
klikgevoel
New 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,
];

2 Replies
JustinDallas
Valued Contributor II

Re: Latest text values in straight table

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

Re: Latest text values in straight table

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 😉