7 Replies Latest reply: Nov 7, 2011 6:43 AM by Some Nath Roy RSS

    Calculate maximum contributors

    Some Nath Roy

      Hi All,

       

      I have a table which has fields : party, item, sales% and dropqty. Two input variables named vSales and vDrop are to be used to filter the records.

      Now I have to extracts those records from the table which will satisfy the following conditions :

       

      condition 1 : sales%  must be greater than vSales value.

      condition 2 : maximum contributors of the vDrop percentage of the total dropQty party wise.

       

      If I consider a table like:

      party, item, sales%, dropqty
      A, m1, 10, 40
      A, m2, 12, 32
      A, m3, 15, 10
      A, m4, 20, 23
      A, m5, 15, 17
      A, m6, 40, 18
      A, m7, 17, 45
      A, m8, 22, 19
      A, m9, 16, 25
      A, m10,30, 30
      B, m1, 22, 10
      B, m2, 32, 56
      B, m3, 16, 32
      B, m4, 40, 18
      B, m5, 32, 22
      B, m6, 10, 23
      B, m7, 8, 8
      B, m8, 54, 34
      B, m9, 18, 25
      B, m10,28, 17

       

      The final output shold be like considering vSales = 10 and vDrop = 50:

      PartyItemSales%dropqty
      Am71745
      Am21232
      Am103030
      Bm23256
      Bm85434
      Bm31632

       

      A qvw file is attached herewith for ready reference. Looking for your help.

       

      Thanks & Regards,

      Somnath

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Calculate maximum contributors
          Stefan Wühl

          Hi Somnath,

           

          could you explain

          condition 2 : maximum contributors of the vDrop percentage of the total dropQty party wise.

           

          a little more?

           

          Regars,

          Stefan

            • Re: Calculate maximum contributors
              Some Nath Roy

              Dear Stefan,

               

              Thanks for your reply. I am trying to describe the condition step by step.

               

              Total dropqty party wise is as follows:

              partysum(dropqty)(vDrop * sum(dropqty)) / 100
              A259129.5
              B245122.5

               

              Now, after cosidering the condition 1 if create a table with dropqty in descending order and create a calculated column named Cumulative as follows:

               

              partyitem            sales% dropqtyCumulative
              Am7174545
              Am2123277
              Am103030107
              Am91625132
              Am42023155
              Am82219174
              Am64018192
              Am51517209
              Am31510219
              Bm2325656
              Bm8543490
              Bm31632122
              Bm91825147
              Bm53222169
              Bm44018187
              Bm102817204
              Bm12210214

               

              Now I would have to take those lines in the final result having 'Cumulative' value less than the [ (vDrop * sum(dropqty)) / 100] value of a party0. Thus the final result should be as follows:

               

              PartyItemSales%dropqty
              Am71745
              Am21232
              Am103030
              Bm23256
              Bm85434
              Bm31632

               

               

               

              Thanks & Regards,

              Somnath

                • Re: Calculate maximum contributors
                  David Geiermann

                  Dear Somnath,

                   

                  I am fairly new the QV, but i tried to solve your problem as good as possible.

                   

                  First I added a cumulative field in the scripting area, which makes it more convenient to not beeing forced to use a sorted table:

                   

                  Data:

                  NoConcatenate

                  LOAD party,

                       item,

                       sales%,

                       If(party=Previous(party), RangeSum(dropqty, peek('Cumulative')),dropqty) AS Cumulative,

                       dropqty

                  Resident Source order by party ASC,dropqty DESC;

                   

                  Then you can just simply add a table and use the formula:

                   

                  =if(sales%>$(vSales),if(below(Cumulative<=(total_dropqty_party * $(vDrop)) / 100,0),1,null()))

                   

                  All items achieving your conditions will get marked by "1".

                   

                  I guess it's not the best solution but it should solve the problem.

                   

                  Regards

                  David

                    • Re: Calculate maximum contributors
                      Some Nath Roy

                      Dear David,

                       

                      Thanks for your reply.

                      The calculation of the Cumulative field can not be done at the script level. If this is be done, some of the item's of a party not having sales% greater than vDrop value may contribute in the Cumulative dropqty. This is incorrect.

                      In our example case the following records should not contribute in the cumulative dropqty after considering

                      sales% > vSales (= 10).

                       

                      PartyItemSales%dropqty
                      Am11040
                      Bm61023
                      Bm788

                       

                       

                      First we need to filter the table depending on the condition sales% greater than vSales. After that we need to find out maximum contributor items of a party in the vDrop percentage of the total dropqty.

                       

                      Thanks & Regards,

                      Somnath

                        • Re: Calculate maximum contributors
                          David Geiermann

                          Dear Somnath,

                           

                          Sorry for this mistake. You are right, on the script level should the conditions be considered. I added another if statement:

                           

                           

                          if(sales%>$(vSales),If(party=Previous(party), RangeSum(dropqty, peek('Cumulative')),dropqty),peek('Cumulative')) AS Cumulative,

                           

                           

                          I hope this will fix the problem. Altough if you change the vSales% you will always have to re-run the script.

                           

                          Regards

                          David

                          • Re: Calculate maximum contributors
                            Stefan Wühl

                            Hi Somnath,

                             

                            if you don't want a script based solution, I could only offer you something like attached.

                            It's not really nice looking (besides, it could probably be simplified, but I can't spend more time on this).

                             

                            As you will see, I filterers successfully the rows you are interested in. But since we use chart inter record functions, the supress when zero will not work, so there are still a lot of unnecessary empty rows shown.

                             

                            I tried with aggr function and I think I could get it to work if aggr() would allow to sort the dimensions (I need dropyqty sorted desc, which is I think is not possible). I tried for a short time with manipulating load order (I thought that aggr() would care for that), but with no success).

                             

                            If you are interested, it would maybe look like this:

                            =aggr( if(rangesum(top( dropqty,1,RowNo()))<=$(vDrop)*sum(total<party> dropqty)/100, dropqty) ,party,dropqty)

                             

                            (not the full solution incl. condition1, but even only condition2 is not correct in this case)

                             

                            All in all, not very satisfying, I also searched the forum and it seems that others had problems here too.

                             

                            Regards,

                            Stefan

                              • Re: Calculate maximum contributors
                                Some Nath Roy

                                Dear Stefan,

                                 

                                Thanks a lot for your help.

                                I experienced the same thing that aggr() function is not working with the sort order of the dropqty.

                                From the calculation point of view your solution is absolutely fine.

                                Still I am expecting a better approach if we can supress null even we use inter record functions.

                                 

                                 

                                Thanks & Regards,

                                Somnath