I need to set up a dashboard with records with a date less than or equal to the filter's effective date, returning only 1 record per key (company,sector).
I've already searched on several forums, but I can't find a solution. Thanks.
Hi, You can achieve this by using the FirstSortedValue function. This function returns the first sorted value of an expression, sorted by another expression.
try this
Load Company, Sector, FirstSortedValue(Effective_date, -Effective_date) as Effective_date, FirstSortedValue(Cost_center, -Effective_date) as Cost_center Resident YourTable Where Effective_date <= YourFilterDate Group By Company, Sector;
In this script, replace YourTable with the name of your table and YourFilterDate with the date you have as a filter.
Please replace the field names with your actual field names if they are different. This script should give you a new table with filtered records as per your requirement.
