ranking a dimension and in that dimension ranking another dimension

[louwriet]

Hi,

Would appreciate help with the following scenario:

Reps visiting shops

I have a rep calling on 50 shops, each shops sells 80 branded products. When I select a rep in a listbox the following must be displayed ranked according to sum of total products sold in a pivot table.

Top 5 shops ranked on total sales of 80 branded products and the top 5 branded products in these 5 shops with their quantities sold. So I will have 2 dimensions in my pivot – shops and products and the pivottable will display only top 5 shops and next to each shop this shop’s top 5 branded products with their quantities sold. So this is actually ranking a dimension and in that dimension ranking another dimension.

Shop	Product	Total Quant
Shop 1	Paintablets	50
	Cough syrep	40
	Migrane tabs	30
	bandaides	20
	shampoo	10
Shop2	Vitamin B tabs	40
	Multivite tabs	30
	Mosquito spray	20
	Airfreshner spray	10
	Paintablets	5
Shop3
Ect….

My expression that I have tried is

= IF ( Aggr ( Rank ( Sum ( Qty )) , ShopName,ProductName, ) <= 5 , Sum ( Qty ) )

But this does not give me the top 5 shops – it list all the shop with 5 products next to each shop and not ranked.

Thank you

Louw

[kaushiknsolanki]

Hi,

   I hope this is what you want.

   Have a look at the attachment.

Regards,

Kaushik Solanki

[louwriet]

Hi Kaushik,

Thank you for your example. I am closer but not there yet. Your expression ranked the products correctly but I need to rank the top 5 shops also.

Let’s say I have 3 reps in n listbox calling on 20 shops each. If none of the reps are selected the pivot will display the top 5 shops ranked of the 60 shops with each shops top 5 products ranked.

If I select a rep in the listbox the pivot table will display again the top 5 shops ranked with the top 5 products ranked of each shop – this top 5 shops is the top 5 of 20 shops – there is a link between rep and shop – each rep is linked to 20 shops.

I have added to your expression and it shows now the top 5 shops with 5 products ranked but the products is not ranked correctly . I did put in the sort in to sort by expression of sum(Quantity) descending of the shop and product dimension.

sum( {$<ShopName = {"=rank(aggr(sum(Quantity), ShopName))<= $(vTop)"},ProductName = {"=rank(aggr(sum(Quantity),ProductName))<= $(vTop)"}>} Quantity)

Thanks for your help.

Regards

Louw

[jonathandienst]

Louw

Your product ranking does not consider only the top 5 shops - it is across all shops. You need to add a set constraint to limit the product ranking to match the shops in the shop set constraint. The punctuation gets quite complicated, so without an example I can't give you the exact expression.

You can create a variable that holds a concatenation of the top 5 shops. That would simplify the expression.

You could also nest the set selections (I am not sure id it will work, but its worth a try)

sum({$<ShopName = {"=rank(aggr(sum(Quantity), ShopName))<= $(vTop)"}

aggr(sum({<ProductName = {"=rank(aggr(sum(Quantity),ProductName, ShopName))<= $(vTop)"}> Quantity), ProductName, ShopName))

(check brackets)

Hope that helps

Jonathan

[louwriet]

Hi Jonathan,

Thank you for your reply. i updated the demo model that Kaushik posted to demostrate my situation that im stuck with. I have tried your suggestion above. Im missing something somewhere maybe the brackets ? For the demo if noting is selected no rep - then it must display the top two shops ranked of all shops and that two shops top two products only ranked. If a rep is selected it must display the top two shops that he calls on and the top two products of that shops.

I hope my explanation is clear enough to understand.

Thank you for your help.

Regards

Louwrie

[jonathandienst · Accepted solution]

Louw

I think I got it - see the attached. I created a variable (vRankShop) that contains an expression to concatenate the top n ranked shops (according to vTop) as a quoted, comma separated list (see the text boxes). Then I used your existing expression and enclosed it in an If(Match()) statement to only include the shops in the list.

That seems to be working.

Regards

Jonathan

[louwriet]

Hi Jonathan,

Brilliant, it works 100% for me.

Thank you so much for your help.

Regards

Louw