Pareto on 2 dimensions

lematiethibault — Tue, 05 Dec 2017 19:01:14 GMT

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:

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

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

Re: Pareto on 2 dimensions

sunny_talwar — Tue, 05 Dec 2017 20:47:27 GMT

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

)

Re: Pareto on 2 dimensions

lematiethibault — Tue, 05 Dec 2017 20:55:52 GMT

OMG!!!

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

The syntax for aggr is to me so tricky.

Thank you again!

topic Pareto on 2 dimensions in QlikView

Pareto on 2 dimensions

Re: Pareto on 2 dimensions

Re: Pareto on 2 dimensions