Rank SUMS Comparison

[#1]

I’m trying to create a chart where if a user selects a client, he can compare it’s sales to the #1 Ranked Client, #5 Ranked Client, and #10 Ranked Client Overall .

Here’s an example of the source data. Now keep in mind the Rank columns do not exist, I've added them in there as reference.

Client Name	Sales (Sum YTD) of Product A	Rank of Sales of Product A (not within the database)	Sales (Sum YTD) Product B	Rank of Sales of Product B (not within the database)
Client A	100	4	25	6
Client B	80	7	50	2
Client C	110	3	1	10
Client D	120	2	30	5
Client E	75	8	38	4
Client F	5	9	22	7
Client G	90	6	100	1
Client H	91	5	5	9
Client I	255	1	60	3
Client J	3	10	15	8

Now if a user selects Client A, the chart I’m aiming to create is the following:

Products	Client A (Parameter Driven)	Client Ranked Sales #1	Client Ranked Sales #5	Client Ranked Sales #10
Product A	100	255	91	3
Product B	25	100	30	1

Now the columns I’ve highlighted in yellow are the ones I’m having a hard time creating expressions for.  Does anyone know of a way to do this using AGGR/Rank/Sum or another expression?

[Jason_Michaelides]

Hi,

I've taken a guess at your data structure but the attached should do it.

Hope this helps,

Jason

[#3]

Thanks Jason for the quick reply.  I looked it over, and I think the returned ranked values in your application are based on the total of the two. 

Based on your data, I believe the ranked 1 and 5 values should show:

Product	A		B
Client Name	Sales	Rank	Sales	Rank
Client A	664	7	236	7
Client B	559	8	1434	1
Client C	1396	1	0	10
Client D	305	9	790	5
Client E	846	6	1181	2
Client F	970	5	995	3
Client G	1139	3	854	4
Client H	32	10	177	8
Client I	1120	4	441	6
Client J	1388	2	83	9
Total	8419		6191

[swuehl]

Yes, the advanced search will return the customer which is ranked over his total sales.

You can add a restriction to the advanced search set expression, like

SUM({<[Client Name]={"=Rank(SUM({1<Product={A}>}Sales))=1"} >}Sales)

But this you would need to state the correct Product depending on your chart dimension (if you want to create a dynamic table with a lot of products.

Unfortunately, the set expression won't take the current dimension value into account, though there are methods to create a pick(match() ) combination, picking different set analysis expressions depending on the dimension value.

But you could also try this:

=sum({1} aggr(if(rank(sum({1} Sales))=1,sum({1} Sales)),Product,[Client Name]))

See also attached.

Hope this helps,

Stefan