topic Re: Sales Rank in QlikView

Sales Rank

larry_w_ — Wed, 19 Dec 2012 10:16:20 GMT

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!

Re: Sales Rank

Anonymous — Wed, 19 Dec 2012 10:23:38 GMT

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

Re: Sales Rank

larry_w_ — Wed, 19 Dec 2012 10:39:05 GMT

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

flipside — Wed, 19 Dec 2012 10:51:37 GMT

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_w_ — Wed, 19 Dec 2012 11:00:49 GMT

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

flipside — Wed, 19 Dec 2012 11:27:25 GMT

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

... 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_w_ — Wed, 19 Dec 2012 11:38:52 GMT

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

Re: Sales Rank

larry_w_ — Wed, 19 Dec 2012 11:49:18 GMT

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

)

,',')

Re: Sales Rank

larry_w_ — Wed, 19 Dec 2012 11:59:12 GMT

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.