topic Rank () - Top 10 for only Current Fiscal Week in QlikView

Rank () - Top 10 for only Current Fiscal Week

crystles — Wed, 10 May 2017 19:33:53 GMT

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!!!

Re: Rank () - Top 10 for only Current Fiscal Week

sunny_talwar — Wed, 10 May 2017 19:37:29 GMT

Have you just tried this

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

Re: Rank () - Top 10 for only Current Fiscal Week

crystles — Wed, 10 May 2017 19:40:45 GMT

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

Re: Rank () - Top 10 for only Current Fiscal Week

sunny_talwar — Wed, 10 May 2017 19:41:35 GMT

You have more than 1 dimensions? Try this

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

Re: Rank () - Top 10 for only Current Fiscal Week

Anonymous — Wed, 10 May 2017 19:42:31 GMT

try

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

Re: Rank () - Top 10 for only Current Fiscal Week

crystles — Wed, 10 May 2017 19:46:52 GMT

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?

Re: Rank () - Top 10 for only Current Fiscal Week

Anonymous — Wed, 10 May 2017 19:49:06 GMT

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

Re: Rank () - Top 10 for only Current Fiscal Week

sunny_talwar — Wed, 10 May 2017 19:49:36 GMT

Try this

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