Sales Rank

[larry_w_]

Hi,

I have a sales scenario that looks like this:

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!

[Anonymous]

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

[larry_w_]

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. 

[flipside]

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

[larry_w_]

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.

[flipside · Accepted solution]

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

[larry_w_]

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

[larry_w_]

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) & ','

        )

     )

     ,',')

[larry_w_]

Also, may wish to get the index using a string which is delimited by commas: ,<prodSales>,

Just in case the number string happens to match a sequence of digits earlier on in the string.