Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Have you try this yet: Rank(Total Sum(Sales))
Rgds,
Sokkorn
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 | - |
How do you think? Correct or not?
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 | - |
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
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 |
Thanks alot ...
I user No for sorting
It worked
Hey since my expression is too long and is combination of 3 other expressions, Can I do it on front end??