Rank with Set Analysis

[#1]

Hi Guys,

I am trying to keep the ranking no matter what is selected. For example, I have three stores and their sales by product and year. I want to create a report for Store A that shows their ranking in comparison with the other stores by year and product. Below I have an example of my data and what I want. And so far what I can achieve. Note this is a simple example for my actual project I need to do a cross table.

Desired Result (Example):

Store	Year	Product	Ranking
A	2011	D	2
A	2012	D	2
A	2012	E	1

Here is my data:

Store	Year	D	E	F
A	2011	238.1349	314.8646	191.1932
B	2011	642.1047	776.4963	335.7628
C	2011	49.81719	371.4189	627.7992
A	2012	977.422	823.2292	213.7887
B	2012	738.0967	946.8795	40.85955
C	2012	295.2093	584.3845	376.8542
A	2013	136.5952	970.51	701.4002
B	2013	635.2067	744.0895	227.2809
C	2013	850.517	336.8081	709.6592

Load Statement:

CrossTable(Product, Sales, 2)

LOAD Store,

     Year,

     D,

     E,

     F

FROM

QVTestData.xlsx

(ooxml, embedded labels, table is Sheet2);

What I have achieved so far, but if I select A the ranking changes to 1 for all.

Year	2011	2011	2011	2011	2011	2011	2012	2012	2012	2012	2012	2012	2013	2013	2013	2013	2013	2013
Product	D	D	E	E	F	F	D	D	E	E	F	F	D	D	E	E	F	F
Store	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank
A	887	1	2	3	918	1	429	2	605	1	664	1	911	1	542	3	297	2
B	525	2	433	1	423	2	69	3	290	2	650	2	48	2	916	1	106	3
C	115	3	193	2	65	3	550	1	28	3	396	3	9	3	795	2	477	1

Rank Expression:

Rank( Sales)

[Nicole-Smith]

Maybe:

Rank(total Sales)

or

Rank(sum({1}Sales))

[rustyfishbones · Accepted solution]

Try this

[#4]

The latter works, but doesn't let me choose one Store. The rank always shows up, no matter my selections.

[#5]

This is perfect - I just needed to change D to Product, and add Product to the Aggr list: Aggr(Rank(Sum( {1} Sales)),Product,Year,Store).

Thank you!