Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
devarasu07
Honored Contributor II

sortable Aggr function issue?

Hi,

I'm trying to achieve Sorting aggr function in my expression and it's not working. can you advise me how to achieve the below expected output. thanks.

=aggr(rangesum(Above(TOTAL (sum(TotalNetPrice)/sum(TOTAL TotalNetPrice)),0,RowNo(Total))),(Article,(= sum(TotalNetPrice)/sum(TOTAL <Article> TotalNetPrice)), Desc))

Note: need to apply sort only in the expression, because report display based on other columns.

Cumulative % sort by Sales% Calculated measure.

Capture.JPG

tried with below article,

The sortable Aggr function is finally here!

Thanks

Deva

1 Solution

Accepted Solutions
MVP
MVP

Re: sortable Aggr function issue?

It just needed a bracket correction:

aggr(rangesum(Above(TOTAL (sum(TotalNetPrice)/sum(TOTAL TotalNetPrice)),0,RowNo(Total))),(Article,(= sum(TotalNetPrice)/sum(TOTAL TotalNetPrice), Desc)))

5 Replies
qliksus
Valued Contributor II

Re: sortable Aggr function issue?

There is no field called Desc and why are you using  a expression ,(= sum(TotalNetPrice)/sum(TOTAL <Article> TotalNetPrice)), as dimension in aggr function 

devarasu07
Honored Contributor II

Re: sortable Aggr function issue?

Hi,

It's sort criteria (ASC or DESC order), so in my output it should be Desc order

MVP
MVP

Re: sortable Aggr function issue?

It just needed a bracket correction:

aggr(rangesum(Above(TOTAL (sum(TotalNetPrice)/sum(TOTAL TotalNetPrice)),0,RowNo(Total))),(Article,(= sum(TotalNetPrice)/sum(TOTAL TotalNetPrice), Desc)))

MVP
MVP

Re: sortable Aggr function issue?

The Aggr() dimensions must be fields, not expressions. As far as I know, this has not changed with the 'structured parameters' of the sortable Aggr(). From the help:

Syntax:

Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{,StructuredParameter})

...

A structured parameter is composed of a dimension and optionally of some sort criteria.

The dimension is a single field and cannot be an expression. The dimension is used to determine the array of values the expression should apply to.

Aggr - chart function ‒ Qlik Sense

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qliksus
Valued Contributor II

Re: sortable Aggr function issue?

sorry my bad I am using Qlikview 11 version which prompted the error . Considering the expression  you have used

below how do you get the  78% ,82% for the article 30270006, 20780203

aggr(rangesum(Above(TOTAL (sum(TotalNetPrice)/sum(TOTAL TotalNetPrice)),0,RowNo(Total))),Article)