topic Re: agg(rank(sum in QlikView

agg(rank(sum

Wed, 24 Sep 2014 02:42:52 GMT

Hi,

Using the agg rank sum formula, I was able to rank nine rows of data based on their 4 week growth. But how is it possible that rank # 6 got skipped? QV ended up with 10 ranks, skipping 6. Just to check my sanity, I put the data in excel and using their rank function, it cam out correctly at 1 through 9.

Any idea what is happening?

Header 1	Header 2	Header 3	Header 4
region	4 week growth	4 week growth rank	excel rank formula
1201	131	3	3
1202	-50	8	7
1203	185	2	2
1204	-29	7	6
1205	-91	9	8
1206	-153	10	9
1207	214	1	1
9980	22	5	5
9990	56	4	4

Re: agg(rank(sum

anbu1984 — Wed, 24 Sep 2014 05:49:37 GMT

Can you provide sample qvw

Re: agg(rank(sum

sushil353 — Wed, 24 Sep 2014 05:57:11 GMT

Hi,

Do you want to show rank at UI level or want to calculate at script level?

Re: agg(rank(sum

Anonymous — Wed, 24 Sep 2014 06:06:56 GMT

please provide your Rank function,

Re: agg(rank(sum

jyothish8807 — Wed, 24 Sep 2014 06:16:21 GMT

Hi Jon,

Try this in dimension. (instead of region take this and rest as expression)

If(aggr(rank(sum([4 week growth])),region)<=10,region)

Regards

Re: agg(rank(sum

Wed, 24 Sep 2014 14:35:43 GMT

Thank-you.

The function is:

=aggr(rank(Sum({<[4_WEEK_ROLLING_RETAIL]={"C4WTY"}, BRAND_NAME={"NUEDEXTA"}>} TRX_QTY_NORM) - Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}, BRAND_NAME={"NUEDEXTA"}>} TRX_QTY_NORM)), REGION_NAME_TM1)

You see there are two sub-fuctions that return a value and are subtracted from one another - repeated 9 times for the region_name.