6 Replies Latest reply: Sep 24, 2013 5:45 PM by Oleg Troyansky RSS

    Summing top X ranked items in chart

      I have searched the community for an answer for this, but other posted solutions do not seem to work for my case.  I have the following input.  Note:  My actual data are spread out across multiple tables using a hash as a key to link customers to projects and bids.  They are too big to JOIN in the script, but QV handles the tables nicely without join.

      LOAD * INLINE [
      Customer, Project, Exclusive, Bid
      A, foo, 1, 40
      B, foo, 0, 1
      C, foo, 0, 2
      D, foo, 0, 4
      E, foo, 0, 7
      F, foo, 0, 10
      A, bar, 0, 10
      B, bar, 0, 4
      A, ded, 1, 30
      B, ded, 1, 15
      C, ded, 0, 5
      D, ded, 0, 5
      ]
      ;

       

      I want my output to have the following columns:

      Project = project dimension

      SharedSum = This is the sum of the top 4 customer bids with Exclusive = 0 associated with the project on that row.  For example, with project "foo", the top 4 would be customer F (bid = 10), E (bid = 7), D (4), C(2).  The sum would therefore be 10+7+4+2 = 23.  The resulting table will have these values.

      ProjectShareSum
      bar14
      ded10
      foo23

       

      However, I cannot get my ShareSum calculation to work properly.  Here is what I have, which doesn't work:

      =sum( aggr(
      if(rank(sum( { < Exclusive={0} > } Bid)) <= 4,
      sum( { < Exclusive={0} > } Bid)
      ),
      Customer))

       

      Thanks!

       

      Correct answer with help from troyansky's comment/blog post:

      =sum( aggr(
      if(rank(sum( { < Exclusive={0} > } Bid),4) <= 4,
      sum( { < Exclusive={0} > } Bid)
      ),
      Project, Customer))

        • Re: Summing top X ranked items in chart
          Manas BN

          Hello,

           

          If you can change the script you can try the below code. It will create a Rank column for each Project and each Exclusive flag. So all you have to do is use set analysis on the front end. No need of Aggr/If.

           

          Capture.PNG.png

           

          Fact:

          LOAD *

          ,RecNo() as ID                                     // Create ID Field

          INLINE [

          Customer, Project, Exclusive, Bid

          A, foo, 1, 40

          B, foo, 0, 1

          C, foo, 0, 2

          D, foo, 0, 4

          E, foo, 0, 7

          F, foo, 0, 10

          A, bar, 0, 10

          B, bar, 0, 4

          A, ded, 1, 30

          B, ded, 1, 15

          C, ded, 0, 5

          D, ded, 0, 5

          ];

           

          // Join Rank to Fact table

          LEFT JOIN (Fact)

          LOAD

          ID

          ,if(Project=Previous(Project),peek(Rank)+1,1) as Rank

          RESIDENT Fact

          ORDER BY Exclusive,Project, Bid DESC

          ;

            • Re: Summing top X ranked items in chart

              Manas, that is an elegant solution.  But I may have oversimplified my question. Your solution would work if my data were in a single table.  My data are in separate tables, one with Customer and Project, and another with Customer and Price, linked by a hash key (it's actually even more complicated than that, but I'm trying to keep it simple for this example).  These tables are extremely large, and my attempts at joining them in the script crash QlikView.  However, QV handles the data nicely as separate tables in the reports.  This forces me to keep them as separate tables joined by a hash key, and perform the Aggr/rank in the report itself.

                  • Re: Summing top X ranked items in chart

                    troyansky By golly! After reading your blog post and trying various permutations, I think I got it to work!  I applied to to my *real* data set and so far everything looks correct.  I still have a little more auditing to do, though.

                     

                    Here is what I ended up with for my expression (not dimension):

                    =sum( aggr(
                    if(rank(sum( { < Exclusive={0} > } Bid),4) <= 4,
                    sum( { < Exclusive={0} > } Bid)
                    ),
                    Project, Customer))

                     

                    Basically, I was very close with my original expression; I just needed to have all my dimensions identified in the Aggr function.  So just Aggr by Customer was not enough, I had to also include Project to get the correct values.

                     

                    Reading up on the "4" in the Rank function, I'm still not clear what it is actually doing, but the calculation does not work without it.

                    4 - Lowest rank on first row, then incremented by one for each row

                     

                    I might also look into optimizing my data to have precalculated ranked values in a table somewhere, but that might force me to do it on the database side.

                      • Re: Summing top X ranked items in chart
                        Oleg Troyansky

                        Reading up on the "4" in the Rank function, I'm still not clear what it is actually doing, but the calculation does not work without it.

                        4 - Lowest rank on first row, then incremented by one for each row

                         

                         

                        Chadd - the parameter 4 makes sure that each element gets a unique rank, even if the value is the same. For example, if the first 3 projects have an identical value = 1,000 you could give them all Rank = 1, or Rank = "1-3", or rank each one of them with a unique number 1, 2, 3. The parameter "4" means the later, - each entry is ranked with a unique number. Otherwise, you are running into a risk of getting more than N top entries (by accident, also 4 in your example)

                         

                        cheers,

                         

                        Oleg

                • Re: Re: Summing top X ranked items in chart
                  kushal chawda

                  see the attached one