topic Re: Sort Order in Aggregated Text Box in QlikView

Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 11:00:43 GMT

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?

Re: Sort Order in Aggregated Text Box

swuehl — Thu, 22 Feb 2018 11:04:36 GMT

Maybe something like

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

Re: Sort Order in Aggregated Text Box

sunny_talwar — Thu, 22 Feb 2018 11:22:01 GMT

Another option

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

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 12:14:22 GMT

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

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 12:21:08 GMT

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(

*0.30)))

If(

Num(

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

Re: Sort Order in Aggregated Text Box

swuehl — Thu, 22 Feb 2018 12:24:56 GMT

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

Re: Sort Order in Aggregated Text Box

sunny_talwar — Thu, 22 Feb 2018 12:25:34 GMT

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,

Num(

*0.30)))

If(

Num(

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

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 13:08:07 GMT

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?

Re: Sort Order in Aggregated Text Box

sunny_talwar — Thu, 22 Feb 2018 13:12:50 GMT

May be try this

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

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

*0.70)=0,

Num(

*0.30)))

If(

Num(

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

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 13:19:36 GMT

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

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

Re: Sort Order in Aggregated Text Box

sunny_talwar — Thu, 22 Feb 2018 13:21:10 GMT

What exactly do you mean 10 top products? Do you mean that same top rank is shared by 10 products?

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 13:25:17 GMT

Yes , So 10 products have a rating of 100% so they will all have a rank of 1-10,

in other scenarios where there is 2,5,4 and 6 with 100% it works fine... im only assuming it because its 10 that its giving an issue...

Re: Sort Order in Aggregated Text Box

sunny_talwar — Thu, 22 Feb 2018 13:42:54 GMT

Try this

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

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

*0.70)=0,

Num(

*0.30)))

If(

Num(

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

Re: Sort Order in Aggregated Text Box

rido1421 — Thu, 22 Feb 2018 13:50:40 GMT

Thank You Sunny, Brilliant as always!