Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
lematiethibault
Contributor II
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
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Contributor II
Contributor II
Author

OMG!!!

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

The syntax for aggr is to me so tricky.

Thank you again!