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