cancel
Showing results for
Did you mean:
Creator III

## Sort Order in Aggregated Text Box

Hi There

I have a chart  sorted by Rank, I also have a text box aligned to this chart to show the top product.

In my example Product H is my top product, based on the Rank Product H and Product I are in rank 1-2, I need to sort my text object

"Z-A" so that Product I is actually displayed, how do I do this?

1 Solution

Accepted Solutions
MVP

Try this

=SubField(Concat(Aggr(If(Num(Rank(

If((If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)=0,

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))),

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)))

+

If(

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)) =0,

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))),

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)

)

, 1)) < 2, Role), Role), '|'), '|', -1)

13 Replies
MVP

Maybe something like

=firstsortedvalue(Distinct Product_Name,- Aggr(Sum(Value)+1/Rank(Product_Name),Product_Name))

MVP

Another option

=SubField(Concat(Aggr(If(Rank(Value) < 2, Product_Name), Product_Name), '|'), '|', -1)

Creator III
Author

Hi Stefan ,

this works for my example but not my actual expression, my actual expression is not a straight sum but a percentage which has been calculated from various if statements. Its returning the last alphabet of all the "Product Names" Im assuming it is doing this because the rank function would sort on the "Value"  ?

Do you have any other Ideas?

Regards

Creator III
Author

Hi Sunny

I wouldnt even know how to incorporate that into my expression... below is my expression.

my expression returns one of the highest 1-2  but I need it to correspond with the 1st "product" in the table  thats why I would sort the top 1-2 alphabetically... and do the same in the table

FirstSortedValue(Distinct Role,-

Aggr(Num(

If((If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)=0,

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))),

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)))

+

If(

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)) =0,

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))),

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)

),'#,##0.00%'),Role)  )

MVP

The basic idea is to add a value much smaller than your primary aggregate to break ties.

If your aggregate is a percentage, a 'much smaller' value would be maybe < 0.001:

=firstsortedvalue(Distinct Product_Name,- Aggr(Sum(Value)+1/(1000+Rank(Product_Name)),Product_Name))

MVP

May be this (Just replace Value with your expression)

=SubField(Concat(Aggr(If(Rank(

If((If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)=0,

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))),

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)))

+

If(

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)) =0,

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))),

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)

)

) < 2, Role), Role), '|'), '|', -1)

Creator III
Author

Hi Sunny

Thank you this seems to be working, only in one scenario where there is 10 top products , it returns a blank, any idea what could be causing that?

MVP

May be try this

=SubField(Concat({<Role = {"*"}>}Aggr(If(Rank(

If((If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)=0,

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))),

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)))

+

If(

Num(

(If(IsNull((Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'Support Survey'}>}Excellent)/(Sum({<SurveySource={'Support Survey'}>}Excellent)+Sum({<SurveySource={'Support Survey'}>}Average)+Sum({<SurveySource={'Support Survey'}>}Poor)),'#,##0.00%'))

*0.30)) =0,

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))),

(If(Isnull((Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%')) ),0,

Num(Sum({<SurveySource={'mySay'}>}Excellent)/(Sum({<SurveySource={'mySay'}>}Excellent)+Sum({<SurveySource={'mySay'}>}Average)+Sum({<SurveySource={'mySay'}>}Poor)),'#,##0.00%'))

*0.70)

)

) < 2, Role), Role), '|'), '|', -1)

Creator III
Author

Its still returning the blank, for the scenario where there is 10 top products.

is it not his causing the issue " ) < 2, Role), Role"

Community Browser