Sorting the value in set analysis

[famoka]

Aggr(
    Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', '),
    GTIN
)

Hello everyone,

I have an expression that yields results in the format of "FOKO A , FOKO S". I need to sort these results so that any value ending with 'S' appears first, while the others are sorted alphabetically "FOKO S, FOKO A". Is This possible to do within the set analysis?

I appreciate any assistance you can provide. Thank you for your time and help.

[Anil_Babu_Samineni]

@famoka Perhaps this in your sort expression. This will look based on String order (A to Z)

=concat( mid(Aggr(
Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', '),
GTIN
), ValueLoop(1, len(Aggr(
Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', '),
GTIN
))),1),'',mid(Aggr(
Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', '),
GTIN
), ValueLoop(1, len(Aggr(
Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', '),
GTIN
))),1))

[famoka]

Hi thanks for thus, but this dosen't work for me. We neet to somehow try to sortit within the the set analysis i provided. This expression is within vizlib pivot table and is a measure not a dimension so we cant sortit in sort expression.

[henrikalmen]

Set analysis {the part of the expression that is enclosed with curly brackets} is only for filtering, you can't do sorting through set expressions. The Aggr() function has the possibility to add a sorting parameter, but I don't believe it can sort based on the last character in the text value.

I'm not sure, but maybe the Dual() function can be used inside your expression. Try this as start, but I'm not sure it will work:

Aggr(
    Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} dual(M_FOKO, if(right(M_FOKO,1)='S', 0, 1))), ', '),
    GTIN
)

[henrikalmen]

Or perhaps this, calling the sorting option of the concat() function with an expression. Not sure about this one either.

Aggr(
    Concat(DISTINCT {<start_date={"<=$(vMaxpvm)"}, end_date={">=$(vMinpvm)"}>} M_FOKO, ', ', if(right(M_FOKO,1)='S', 0, M_FOKO)),
    GTIN
)

[marcus_sommer]

You may try it by defining your foko-field as a dual() value and in the numeric part you applies your wanted sorting. Then you add this field as the third parameter to the concat() - sorting the aggr() won't help because at this point the aggregated string exists already.