Rank () - Top 10 for only Current Fiscal Week

[crystles]

I have a table of parts. The parts have numerous dimensions including a Quantity and a Total Cost.

I then have two expressions. One is the Total Cost divided by the Quantity for ONLY the current week

EXPRESSION 1 = Total Cost / Quantity

Sum({<CWEEK={1}>}T_AMNT) / Sum({<CWEEK={1}>}QUANTITY)

I then want to show the Rank of each Item because I only need to show the Top 10 Items by Expression 1

So I have this formula, (See Below) It is supposed to rank the first expression by the Part number (VALUE_ELEMENT)

aggr( Rank(
(Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))
,3),VALUE_ELEMENT)

BUT, it is not aggr correctly, since it is giving me Ranks for a blank value in the Expression 1 field... I am assuming it is Ranking for the entire unfiltered data set, instead of using the Set Analysis to give me the Rank for only the current week....

I am not sure how to get the Rank for only the current week.... Any help would be greatly appreciated. Thanks!!!

[sunny_talwar]

Have you just tried this

=Rank((Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))

[crystles]

I did try that at first, but it is giving me a "1" for all rows... That is why I added the Aggr function

[sunny_talwar · Accepted solution]

You have more than 1 dimensions? Try this

=Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))

[Anonymous]

try

=Rank(total (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))

[crystles]

That fixed it! Thank you!

... now I am seeing though that some of my rows are the same number, so it is giving me a rank of "1-2" or '5-6" ... is there any way to just see it by a single number?

[Anonymous]

=Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY),4)

[sunny_talwar]

Try this

=Num(Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY)))