topic Re: Aggr and rank not sorting in Text box in QlikView

Aggr and rank not sorting in Text box

Sun, 17 Mar 2013 00:28:00 GMT

Read an article by Adam Bellerby on use of aggr. Article was good. Link to article is below.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/07/aggr

Used the article in the attached .qvw. It seems that the sort of the data in the rank is alpha and not by the top sales or in this case invoice. This is the section in the document called: AGGR & Rank()

Am I doing something wrong, or does the article just get lucky that the top salesperson is listed first?

I found a way to sort the top three, but took two statement, can the existing concat/aggr/rank be changed to provide a sorted list in the text box by highest producer versus alphabetic which it seems?

Look at the textbox in the document.

Let me know if you have any thoughts..

Thanks,

Re: Aggr and rank not sorting in Text box

swuehl — Sun, 17 Mar 2013 10:43:02 GMT

Try using concat() with a sort weight (standard is sorting alphanumerical):

=CONCAT(DISTINCT IF(AGGR(Rank(SUM(INVOICE.Total)),CUSTOMER.LastName)<=3, CUSTOMER.LastName),',',

aggr(sum(INVOICE.Total),CUSTOMER.LastName)

)

Re: Aggr and rank not sorting in Text box

Sun, 17 Mar 2013 16:17:42 GMT

Stefan,

Thanks for your help.... I do not have a license anymore so do not believe I can read your document. The suggestion you made is correct, sweet. To get it to sort DESC, i added a piece to the sorting aggr as *-1

Q1. Is this the best way or is there another way?:

=CONCAT(DISTINCT IF(AGGR(Rank(SUM(INVOICE.Total)),CUSTOMER.LastName)<=3, CUSTOMER.LastName),',', aggr(SUM(INVOICE.Total),CUSTOMER.LastName)*-1) // WORKS, AND SORTS, BUT ASC, NEEDED TO ADD *-1 TO GET IT TO SORT DESC

Note, I stupidly used a nonaggr function to sort which did not work, should of stayed consistent.

Q2. Why does the following not work? Is it because there is no dimension on the text box or something in my syntax?

=CONCAT(DISTINCT IF(AGGR(Rank(SUM(INVOICE.Total)),CUSTOMER.LastName)<=3, CUSTOMER.LastName),'|', SUM(TOTAL INVOICE.Total <CUSTOMER.LastName>)) // WORKS, but sort still off, probably cause no dimension in text box???

Thanks,

Re: Aggr and rank not sorting in Text box

Sun, 17 Mar 2013 17:17:48 GMT

Found another similar option is to use negative in front of sort aggr, not sure if makes any difference or not, still have the above two questions.

=CONCAT(DISTINCT IF(AGGR(Rank(SUM(INVOICE.Total)),CUSTOMER.LastName)<=3, CUSTOMER.LastName),' '&chr(13), -aggr(SUM(INVOICE.Total),CUSTOMER.LastName)) // WORKS, AND SORTS, BUT ASC, NEEDED TO ADD - TO GET IT TO SORT DESC

Re: Aggr and rank not sorting in Text box

swuehl — Sun, 17 Mar 2013 19:40:48 GMT

Using a multiplication *-1 or a preceding minus sign should equivalent, right?

And you are right, you need to use the negative sums when sorting desc, I missed that in my post.

Q1: I am not sure if there is more simple solution, at least I can't think of one right now.

Q2: I think your sort weight expression is not correct syntax:

SUM(TOTAL INVOICE.Total <CUSTOMER.LastName>)

what should this mean? you can use a field list after your field named INVOICE.Total

Have you intended to use the field list after the first total? I think it's not allowed to use this aggregation inside the outer aggregation (concat() function). That's why you need to use the advanced aggregation.

If you can precalculate the sums, this would make your expression somewhat less complex, of course...