parse out values based on max date?

[1600eads]

I have data - 

SalesID	Product_Sold	Sold_Date	MAX_SOLD_DATE	DESIRED_OUTPUT
1	iPhone | Galaxy | AirMax Headphones	1/23/2024 | 1/31/2024 | 1/31/2024	1/31/2024	Galaxy | AirMax Headphones
2	LG TV 55in | LG WasherDryerCombo	1/15/2024 | 1/15/2024	1/15/2024	LG TV 55in | LG WasherDryerCombo

 
I am able to parse out the max date but now I need to display the Products_Sold based on the max date (DESIRED_OUTPUT column). The issue I'm running into is that in some cases, a Salesperson can sell more than 1 item on any given transaction. Any guidance on concatenating multiple Products_Sold based on the Sold_Date? SalesID=1, should display only 2 values - Galaxy and AirmaxHeadphones since the max date is 1/31/2024.

I've tried something like

=aggr(CONCAT(DISTINCT{<Sold_Date_Unconcat = {'$(=MAX(Sold_Date_Unconcat ))'}>} Product_Sold_Unconcat ,'|'), SalesID).

I also do have the original columns where it is not concatenated Product_Sold_Unconcat and Sold_Date_Unconcat which I was using for the expression above but not working as I expected.

update: I also tried firstsortedvalue(Product_Sold_Unconcat, -Sold_Date_Unconcat) but based on Qlik's documentation it will not yeild any results if have the same sort_weight, in this case would be the dates.

"If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL."

Thanks everyone.

[1600eads]

I was able to figure this one out by using this expression.

FirstSortedValue( Aggr(Concat(DISTINCT Product_Sold_Unconcat, ' | '), Sold_Date_Unconcat),-Aggr(Sold_Date_Unconcat, Sold_Date_Unconcat))

I wrapped this around an aggr function again so I can allow users to filter values on a table object using this expression.

=aggr(FirstSortedValue( Aggr(Concat(DISTINCT Product, ' | '),DateSold),-Aggr(DateSold,DateSold)),SalesID)

The issue is since I am aggr this by SalesID, if a user were to select any of the values from the table, it will display the aggregation which is SalesID.

I was hoping I can control this in the script/data load editor but it doesn't seem to allow the aggr function to be used. Is there any workaround to make it to display the actual products vs SalesID (or the aggregation). Thanks all.

[Chanty4u]

Try this

=Concat({<Sold_Date = {"$(=Max(Sold_Date))"}>} DISTINCT Product_Sold, ' | ')