topic Re: sortable Aggr function issue? in QlikView

sortable Aggr function issue?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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.

tried with below article,

The sortable Aggr function is finally here!

Thanks

Deva

Re: sortable Aggr function issue?

qliksus — Wed, 11 Oct 2017 04:19:01 GMT

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

Re: sortable Aggr function issue?

devarasu07 — Wed, 11 Oct 2017 04:23:42 GMT

Hi,

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

Re: sortable Aggr function issue?

tresB — Wed, 11 Oct 2017 05:14:33 GMT

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

Re: sortable Aggr function issue?

jonathandienst — Wed, 11 Oct 2017 05:21:51 GMT

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

Re: sortable Aggr function issue?

qliksus — Wed, 11 Oct 2017 05:24:56 GMT

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)