topic Re: Sorting the value in set analysis in App Development

Sorting the value in set analysis

famoka — Fri, 09 Feb 2024 08:19:00 GMT

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.

Re: Sorting the value in set analysis

Anil_Babu_Samineni — Fri, 09 Feb 2024 08:53:11 GMT

@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))

Re: Sorting the value in set analysis

famoka — Fri, 09 Feb 2024 11:57:57 GMT

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.

Re: Sorting the value in set analysis

henrikalmen — Fri, 09 Feb 2024 13:11:08 GMT

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 )

Re: Sorting the value in set analysis

henrikalmen — Fri, 09 Feb 2024 13:17:09 GMT

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 )

Re: Sorting the value in set analysis

marcus_sommer — Fri, 09 Feb 2024 14:50:22 GMT

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.