Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having a problem with the aggr(rank feature. I get different results when I change the ranking number. By different results, I get companies that were not shown before in ranks above the initial limit. The totals are different also.
DIMENSION
=if(
aggr(
rank(
Sum ({$<[Region] = {'A','B','C','D','E','F'}>} [Counter]))
, [SNL Company Name])<=25
, [SNL Company Name],'All Other')
EXPRESSIONS
Rank
=rank(
Sum ( {$<[Region] = {'A','B','C','D','E','F'},
[As of Year] = {$(=Year(Max(AddMonths([As of Date],0))))},
[As of Day] = {$(=Day(MonthEnd(Max(AddMonths([As of Date],0)))))},
[As of Month] = {$(=Month(Max(AddMonths([As of Date],0))))}>} [Counter]),2)
Count Dec 2013
Sum ( {$<[Region] = {'A','B','C','D','E','F'},
[As of Year] = {$(=Year(Max(AddMonths([As of Date],0))))},
[As of Day] = {$(=Day(MonthEnd(Max(AddMonths([As of Date],0)))))},
[As of Month] = {$(=Month(Max(AddMonths([As of Date],0))))}>} [Counter])
Count Sep 2013
Sum ( {$<[Region] = {'A','B','C','D','E','F'},
[As of Year] = {$(=Year(Max(AddMonths([As of Date],-3))))},
[As of Day] = {$(=Day(MonthEnd(Max(AddMonths([As of Date],-3)))))},
[As of Month] = {$(=Month(Max(AddMonths([As of Date],-3))))}>} [Counter])
Results
[SNL Company Name])<=25 [SNL Company Name])<=50
Rank | Count for Dec 2013 | Count for Sep 2013 | Rank | Count for Dec 2013 | Count for Sep 2013 |
1 | 79466 | 78302 | 1 | 53983 | 52879 |
2 | 19178 | 19281 | 2 | 19178 | 19281 |
3 | 15761 | 15619 | 3 | 15761 | 15619 |
4 | 7284 | 7149 | 4 | 7284 | 7149 |
5 | 5997 | 5922 | 5 | 5997 | 5922 |
6 | 5301 | 5066 | 6 | 5301 | 5066 |
7 | 4522 | 4546 | 7 | 4522 | 4546 |
8 | 3433 | 3537 | 8 | 3433 | 3537 |
9 | 3012 | 3141 | 9 | 3012 | 3141 |
10 | 2916 | 2855 | 10 | 2916 | 2855 |
11 | 2759 | 2520 | 11 | 2759 | 2520 |
12 | 2755 | 2715 | 12 | 2755 | 2715 |
13 | 2547 | 2598 | 13 | 2547 | 2598 |
14 | 2541 | 2644 | 14 | 2541 | 2644 |
15 | 2488 | 2432 | 15 | 2488 | 2432 |
16 | 2438 | 2350 | 16 | 2438 | 2350 |
17 | 2229 | 2189 | 17 | 2229 | 2189 |
18 | 1929 | 1603 | |||
18 | 1840 | 1881 | 19 | 1840 | 1881 |
19 | 1836 | 1764 | 20 | 1836 | 1764 |
20 | 1645 | 1600 | 21 | 1645 | 1600 |
21 | 1601 | 1649 | 22 | 1601 | 1649 |
23 | 1510 | 1364 | |||
24 | 1501 | 1414 | |||
25 | 1478 | 1394 | |||
22 | 1436 | 1414 | 26 | 1436 | 1414 |
23 | 1415 | 1500 | 27 | 1415 | 1500 |
24 | 1295 | 1510 | 28 | 1295 | 1510 |
29 | 1292 | 1296 | |||
30 | 1282 | 1315 | |||
31 | 1252 | 1347 | |||
25 | 1215 | 1300 | 32 | 1215 | 1300 |
33 | 1145 | 1128 | |||
34 | 1096 | 1022 | |||
35 | 1061 | 1172 | |||
36 | 995 | 936 | |||
37 | 990 | 1015 | |||
38 | 987 | 990 | |||
39 | 912 | 952 | |||
40 | 896 | 871 | |||
26 | 889 | 1109 | 41 | 889 | 1109 |
42 | 876 | 850 | |||
43 | 812 | 798 | |||
44 | 783 | 868 | |||
45 | 668 | 674 | |||
46 | 661 | 685 | |||
47 | 613 | 655 | |||
48 | 612 | 795 | |||
49 | 600 | 570 | |||
50 | 468 | 673 | |||
- | 177799 | 176593 | - | 176735 | 175557 |
Are you considering Calculation from Max Date?
If yes, use as below..
Instead of Max(AddMonths([As of Date]))
use AddMonths(Max([As of Date]))
Do the same for all your expressions.