Problem with aggr(rank

Thu, 13 Feb 2014 17:08:17 GMT

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

Re: Problem with aggr(rank

MK_QSL — Thu, 13 Feb 2014 17:16:13 GMT

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.

topic Problem with aggr(rank in QlikView

Problem with aggr(rank

Re: Problem with aggr(rank