5 Replies Latest reply: Sep 24, 2014 10:35 AM by Jon Vance

# agg(rank(sum

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?

region4 week growth4 week growth rankexcel rank formula
120113133
1202-5087
120318522
1204-2976
1205-9198
1206-153109
120721411
99802255
99905644
• ###### Re: agg(rank(sum

Can you provide sample qvw

• ###### Re: agg(rank(sum

Hi,

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

• ###### Re: agg(rank(sum

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.

• ###### Re: agg(rank(sum

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