topic Latest text values in straight table in App Development

Latest text values in straight table

klikgevoel — Sat, 16 Nov 2024 20:37:51 GMT

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.

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,
];

Re: Latest text values in straight table

JustinDallas — Wed, 19 Jun 2019 13:58:10 GMT

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.

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

Re: Latest text values in straight table

klikgevoel — Wed, 19 Jun 2019 14:31:03 GMT

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 😉