Sort Order in Aggregated Text Box

[rido1421]

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?

[swuehl]

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]

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]

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]

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]

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]

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

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