Concatenated List sorting

[jtylerrand]

I have a table with a Location, Country, Year, Month, and Total(Integer).

I need to create a concatenated list of all of the countries per location, with the total.

The intended final product would look like this:

Year Location Month Countries

2023 SPW October Mexico(14), Honduras(9), Canada(1), Colombia(1)

I can concatenate the list already, to include the totals, but the order is in descending Alphabetical Order:

I am using the following Variables:

CITIZENSHIPS - Dimension

=If(IsNull(Previous([MAX_CONCAT_CITIZENSHIP]));Substr([MAX_CONCAT_CITIZENSHIP];1;Length([MAX_CONCAT_CITIZENSHIP])-2);Substr([MAX_CONCAT_CITIZENSHIP];1;Pos([MAX_CONCAT_CITIZENSHIP];Previous([MAX_CONCAT_CITIZENSHIP]))-3))

MAX_CONCAT_CITIZENSHIP - Dimension

=[CONCAT_CITIZENSHIP] Where([Subject Citizenship Code]=[MAX_CITIZENSHIP])

CONCAT_CITIZENSHIP- Dimension

=[Subject Citizenship Code] + " (" + [TOTAL] + ")" + ", " + Previous(Self)

MAX_CITIZENSHIP - Measure

=Max([Subject Citizenship Code]) In ([Fiscal Year];[Sector];[Month])

I realize the CONCAT_CITIZENSHIP variable is what is sorting the list of citizenships in ascending order.

Also that the MAX_CONCAT_CITIZENSHIP variable is what "stops" the concatenation list at the correct point.

What I can't seem to figure out is if there is a way to sort the final list by the Totals in descending order, and ideally if there is a tie, to then sort alphabetically.

I realize that this may not be possible, but I thought I would ask. Thanks.

[anne-petteroe]

Hello Tyler,

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers, as it provides tips for preparing questions that draw responses from our members.

Feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will also help you when preparing questions for the community.

By adding a picture to your profile you encourage your readers to respond.

Kind regards,
Anne