Hi all,
I am trying to use the rank function to figure out how to rank sales with three different dimensions(Type, Office Name and Name). Please see tables below. My issue with the my rank function is that it returns rank of 4 for both De in office D and E, table 1 below.
Here's the function I used, aggr(rank(sales,4,1),type,name), I tried adding the third dimension office name hoping that would work, aggr(rank(sales,4,1),type,name, Office Name) would return rank of 1s to all the rows. Please let me know if there's way to accomplish this. The correct ranking should be in the 2nd table, you can see that De in office E should rank 6th for both Type A and B. Thanks again for your time!
| Rank | Type | Office Name | Name | Sales |
| 1 | A | A | Bob | 800 |
| 2 | A | B | sally | 700 |
| 3 | A | C | Sussy | 600 |
| 4 | A | D | De | 300 |
| 4 | A | E | De | 200 |
| 5 | A | F | Tom | 400 |
| 1 | B | A | Bob | 800 |
| 2 | B | B | sally | 700 |
| 3 | B | C | Sussy | 600 |
| 4 | B | D | De | 300 |
| 4 | B | E | De | 200 |
| 5 | B | F | Tom | 400 |
| Rank | Type | Office Name | Name | Sales |
| 1 | A | A | Bob | 800 |
| 2 | A | B | sally | 700 |
| 3 | A | C | Sussy | 600 |
| 4 | A | D | De | 300 |
| 5 | A | F | Tom | 400 |
| 6 | A | E | De | 200 |
| 1 | B | A | Bob | 800 |
| 2 | B | B | sally | 700 |
| 3 | B | C | Sussy | 600 |
| 4 | B | D | De | 300 |
| 5 | B | F | Tom | 400 |
| 6 | B | E | De | 200 |
