8 Replies Latest reply: Dec 19, 2012 6:59 AM by Larry Woodside RSS

    Sales Rank

    Larry Woodside

      Hi,

       

      I have a sales scenario that looks like this:

       

      Sales.jpg

      I would like the "Rank" column in the second table to contain the ranking for customer B.
      The numbers should be the same as the corresponding Rank columns in the pivot table for the line with

      customer = 'B'.  That is, the column should contain 4, 3, and 3.

       

      What formula should I use for the "Rank" column in the second table?

       

      Thanks!

        • Re: Sales Rank
          Johannes Sunden

          =Rank(Aggr(Sum({<Customer={B}>}Sales),Customer,Product))

            • Re: Sales Rank
              Larry Woodside

              Thanks for the response, but that expression seems to give me 3, 1, 2.  I think all sales would need to be considered since it is a ranking relative to customer for the particular product. 

                • Re: Sales Rank
                  Dave Riley

                  There's probably a better way using Rank and Aggr, but I quite like this ...

                   

                  =substringcount(left(concat(Sales,',', -Sales),index(concat(Sales,',', -Sales),ProdSum)),',')+1

                   

                  All it does it create a comma list of sales in reverse order, then matches the ProdSum (your first expression renamed) by counting the commas and adding 1.

                   

                  flipside

                    • Re: Sales Rank
                      Larry Woodside

                      Thanks for the response, but I would like the last column in the second table to be the rank of customer B for sales of the particular product (ranking relative to all customers who purchased the product).  It seems the sales to be concatenated would for each of the customers purchasing the product and I'm not sure how to do that.

                        • Re: Sales Rank
                          Dave Riley

                          In your example, the dimension in the second table is showing for all Sales and it is only your first expression which is limited to a particular Customer, with the 2nd expression calculating for all customers, so the comparison will show the ranking position of the first expression across all customers.  That said, your sales totals are hard-coded single values, so if you need to sum them up my expression would need changing to ..

                           

                          =substringcount(left(

                             concat(aggr(sum(Sales),Customer,Product),',',-aggr(sum(Sales),Customer,Product)),

                             index(concat(aggr(sum(Sales),Customer,Product),',',-aggr(sum(Sales),Customer,Product)),ProdSum)),',')

                             +1

                           

                          ... it may make sense to break up the concatenation into a column of its own so that you can reference it by column name in that chart. (The column can be hidden afterwards).

                           

                          flipside

                            • Re: Sales Rank
                              Larry Woodside

                              That's pretty cool!  It works!  Thanks for your help.

                                • Re: Sales Rank
                                  Larry Woodside

                                  The final expression (indented to show arguments is):

                                   

                                  =substringcount(

                                      left(

                                          ',' &

                                          concat(

                                              aggr(

                                                  sum(Sales)

                                                  ,Customer,Product)

                                              ,','

                                              ,-aggr(

                                                  sum(Sales)

                                                  ,Customer,Product)

                                              ) & ','

                                          ,index(

                                              ',' &

                                              concat(

                                                  aggr(

                                                      sum(Sales)

                                                      ,Customer,Product)

                                                  ,','

                                                  ,-aggr(

                                                      sum(Sales)

                                                      ,Customer,Product)

                                                  ) & ','

                                              ,',' & Sum({<Customer={B}>}Sales) & ','

                                          )

                                       )

                                       ,',')