agg(rank(sum

[#1]

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

[anbu1984]

Can you provide sample qvw

[sushil353]

Hi,

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

[Anonymous]

please provide your Rank function,

[jyothish8807]

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

KC

[#6]

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.