Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
rido1421
Creator III
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
sunny_talwar

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)

View solution in original post

13 Replies
swuehl
MVP
MVP

Maybe something like

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

sunny_talwar

Another option

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

rido1421
Creator III
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

rido1421
Creator III
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)  )

swuehl
MVP
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))

sunny_talwar

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)

rido1421
Creator III
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?

sunny_talwar

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)

rido1421
Creator III
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"