Need expression to sort contents of a variable

[Anonymous]

This is going to be a little hard to explain so bear with me.  I have several variables.  Depending on a users selections, I have a formula in one variable that is set to the contents of the other variables.

Here is an example of the expression in my vPartsToSelect variable:

if(Market_Data.Market='US NFDM'and Market <> 'US MPC',vUSNFDMParts,

if(Market_Data.Market='EU SMP' and Market <> 'EU MPC',vEUSMPParts,

So vPartsToSelect is a list of parts that changes depending on what Market_Data.Market is selected.

What I need to do is add an expression to sort the fieldnames in variable vPartsToSelect based on a set analysis.  This expression would sort the result of the example expression above. I just dont know what expression I need or if one exists.

[evan_kurowski]

Hello Mark,

Maybe add some small samples of the contents of these variables, because I think some specific examples might help. (Like if the variable stores many lines, show us what the first 3 or 4 look like).

[Anonymous]

Sure, the variables are very simple.  They include fieldnames and look like this:  'fieldname1','fieldname2','fieldname3'

I think the answer is the combination of concat, aggr and rank functions but not sure how.

I can get it to work on one variable with this:

=concat(DISTINCT{<Parts={$(vUSNFDMParts)}>}Parts,', ',aggr(Sum({<Year={$(vCurrentYear)},Scenario={$(vPrevLBEScenario)},Currency={'USD.'}>}Spend),Parts))

But the variable that sets parts (vUSNFDMParts in the example above) is different depending on user selections I mentioned in the post.

How can I combine the two?

[evan_kurowski]

Hello Mark,

From your description and syntax it appears there are two or more variables which might be fed into vPartsToSelect: 

• vUSNFDMParts
• vEUSMPParts

If you applied sort at the formation of vUSNFDMParts and vEUSMPParts, there would be no need to reapply sorting when moving one of their values into vPartsToSelect.

So earlier in the process, can we identify what kind of sort criteria could be built into the formation of the  precursor variables? 

[Anonymous]

I'm essential doing that now.  However, although the contents of vUSNFDMParts, vEUSMPParts (and others) are static the sort order will change over the course of time.   I'm trying to avoid having to manually update the sort order of all these variables.

I'm thinking of creating another vPartsToSelect2 to pass the selections to but sort them at that point.  Then reference #2 instead of the original one.

[Anonymous · Accepted solution]

Ok, I figured it out.  I simply applied the expression I had already figured out where I use the variable vPartToSelect.

concat(DISTINCT{<Parts={$(vPartToSelect)}>}Parts,', ',aggr(Sum({<Year={$(vCurrentYear)},Scenario={$(vPrevLBEScenario)},Currency={'USD.'}>}Spend),Parts))

Thanks for letting me bounce this off you

[evan_kurowski]

Sure, you're welcome.

Seeing your expression, I think I can flesh out the description of your original issue.

You are creating CONCAT() strings and you'd like to use an expression that sorts the values according to how much has been spent on [Parts] in the most recent year.

Now that your desired sort is revealed we can get started on figuring out a solution.