Rank - Exclude Column while ranking

[#1]

Hi everyone,

I have following data

LOAD * INLINE [
    Region, Name, Sales
    South, A, 2000
    South, B, 4000
    South, A, 3000
    North, A, 3000
    North, B, 2000
    West, A, 1000
    West, B, 3000
];

Created a straight chart like below:

Region	Name	Total Sales	Rank
West	A	1000	2
West	B	3000	1
North	A	3000	1
North	B	2000	2
South	A	5000	1
South	B	4000	2
		18000	-

Ranking is done region wise. I need to rank on the basis of Name only.

The chart should show the result like:

Region	Name	Total Sales	Rank
South	A	5000	1
South	B	4000	2
West	B	3000	3
North	A	3000	3
North	B	2000	4
West	A	1000	5
		18000	-

Thanks in advance.

[Sokkorn]

Hi,

Have you try this yet: Rank(Total Sum(Sales))

Rgds,

Sokkorn

[#3]

just tried giving answer like

Region	Name	Total Sales	Rank
West	A	1000	6
West	B	3000	3
North	A	3000	3
North	B	2000	5
South	A	5000	1
South	B	4000	2
		18000	-

[Sokkorn]

How do you think? Correct or not?

[#5]

To show top 3 salesman

I used below domension (2nd) 1st is region

=if(aggr(Rank(Total Sum(Sales),0,0),Sales)<=3 ,Name ,null())

result is

Region	Name	Total Sales	Rank
North	A	3000	3
South	A	5000	1
South	B	4000	2
		12000	-

does not show

West B 3000

if i used

=if(aggr(Rank(Total Sum(Sales),0,0),Sales)<=4 ,Name ,null())

result is not correct

Region	Name	Total Sales	Rank
West	A	1000	4
North	A	3000	3
South	A	5000	1
South	B	4000	2
		13000	-

[Sokkorn · Accepted solution]

Hi,

Maybe script level can do this job

[Data]:
LOAD * INLINE [
Region, Name, Sales
    South,A, 2000
    South,B, 4000
    South,A, 3000
    North,A, 3000
    North,B, 2000
    West,A, 1000
    West,B, 3000
];
[tmpRank]:
LOAD
    Region        AS [Region2],
    Name        AS [Name2],
    Sum(Sales)    AS [Sales2]
Resident [Data] Group By Region,Name;
[Rank]:
LOAD 
    RecNo()    AS [No],
    *,
    IF([Sales2] <> Previous([Sales2]), RangeSum(1, Peek('Rank')),Peek([Rank])) AS [Rank]
Resident [tmpRank] Order By [Sales2] Desc;
DROP Table [tmpRank]

Regards,

Sokkorn

[#7]

Thanks a Lot Sokkorn

Ranking was done correctly

but not showing Salesman according to descending order of sales

Region2	Name2	Rank	Sales2
			-
South	A	1	5000
South	B	2	4000
North	A	3	3000
North	B	4	2000
West	A	5	1000
West	B	3	3000

[#8]

Thanks alot ...

I user No for sorting

It worked

[brijesh1991]

Hey since my expression is too long and is combination of 3 other expressions, Can I do it on front end??