Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

larry_w_
New 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!

Tags (3)
1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Sales Rank

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

8 Replies
jsn
Honored Contributor

Re: Sales Rank

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

larry_w_
New Contributor III

Re: Sales Rank

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
Valued Contributor II

Re: Sales Rank

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

Re: Sales Rank

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
Valued Contributor II

Re: Sales Rank

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

Re: Sales Rank

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

larry_w_
New Contributor III

Re: Sales Rank

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

Re: Sales Rank

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.

Community Browser