Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

lematiethibault
New Contributor II

Pareto on 2 dimensions

Hello the community,

I have spend so many hours on my problem that I finally asking for your help. Let me explain

I am using QV12.10 SR8.

For some needs in retail, I have built a pareto using the new great feature in the aggr function to sort my element with an expression.

And that works perfectly to define my Class A,B,C per product (ArticleNo in my case) using this function:

=Aggr(

if(rangesum(Above(sum(#SalesQty))/sum(TOTAL #SalesQty), 0, rowno() ))<=0.8, 'A',

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL #SalesQty), 0, rowno() ))<=0.95, 'B',

'C')),

(_KeyArticleNo, (=sum(#SalesQty), Desc))

)

However, I have now been asked to do the same thing per product and product group and here comes the problem...

it looks like the sorting doesn't work when I had an extra dimesnion in my aggr function. I tried all combination bu nothing works.

Example:

=Aggr(

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.8, 'A',

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.95, 'B',

'C')),

(_KeyArticleNo, (ArticleGroup, (=Sum(#SalesQty), DESC)))

)

with some picture to illustrate my issue.

My test case is ArticleGroup: A10 - Jacket, Class A

Here is what I currently have:

Capture1.PNG

So 17 for A10, Class A

But here is what I should get according to my other table I have created (that gives too many info):

Capture2.PNG

I should have 14

Attached you will also find the QVW.

Could you please help me? I have no solution anymore...

Thanks a lot!

Thibault

1 Solution

Accepted Solutions

Re: Pareto on 2 dimensions

Try using this

=Aggr(

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.8, 'A',

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.95, 'B',

'C')),

ArticleGroup,(_KeyArticleNo, (=Sum(#SalesQty), DESC))

)


Capture.PNG

2 Replies

Re: Pareto on 2 dimensions

Try using this

=Aggr(

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.8, 'A',

if(rangesum(Above(sum(#SalesQty)/sum(TOTAL <ArticleGroup> #SalesQty), 0, rowno() ))<=0.95, 'B',

'C')),

ArticleGroup,(_KeyArticleNo, (=Sum(#SalesQty), DESC))

)


Capture.PNG

lematiethibault
New Contributor II

Re: Pareto on 2 dimensions

OMG!!!

Thank you so much. I have spent 4 hours on this....

The syntax for aggr is to me so tricky.

Thank you again!

Community Browser