Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
larry_w_
Contributor III
Contributor III

Sales Rank

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!

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

8 Replies
Anonymous
Not applicable

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

larry_w_
Contributor III
Contributor III
Author

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
Partner - Specialist II
Partner - Specialist II

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_
Contributor III
Contributor III
Author

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
Partner - Specialist II
Partner - Specialist II

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_
Contributor III
Contributor III
Author

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

larry_w_
Contributor III
Contributor III
Author

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_
Contributor III
Contributor III
Author

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.