2 Replies Latest reply: Dec 5, 2017 3:55 PM by Thibault LEMARIE RSS

    Pareto on 2 dimensions

    Thibault LEMARIE

      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