Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
You have more than 1 dimensions? Try this
=Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))
Have you just tried this
=Rank((Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))
I did try that at first, but it is giving me a "1" for all rows... That is why I added the Aggr function
You have more than 1 dimensions? Try this
=Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))
try
=Rank(total (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY))
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?
=Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY),4)
Try this
=Num(Rank(TOTAL (Sum({<CWEEK={1}>}T_AMNT)/Sum({<CWEEK={1}>}QUANTITY)))