topic Re: Calculating Top N values but excluding rows based on other columns conditions in QlikView

Calculating Top N values but excluding rows based on other columns conditions

deepakqlikview_123 — Mon, 30 Jan 2017 09:52:22 GMT

Hi All,

PFA.

I need to find out Top 5 names based on Num column value but if value in value column is negative I need to skip that row and find out Top 5 names based on num column

For eg-

based on my data my values top 5 based in num col is 10,40,7,6,5 but

40 and 5 has negative vaues assigned in column value that is -5 so i need to skip whole row so that my top 5 values would be

10,2,3,6,7

Can you please suggest how to proceed.

Thanks

Re: Calculating Top N values but excluding rows based on other columns conditions

vinieme12 — Mon, 30 Jan 2017 10:04:28 GMT

Try

if(Value>0,Rank(Num,1,1))

Or this

AGGR(rank(if(Value>0,Num),1,1),Name)

Re: Calculating Top N values but excluding rows based on other columns conditions

tresB — Mon, 30 Jan 2017 10:14:48 GMT

Try like:

Sum({<Name={"=Rank(Sum({<Value={'>=0'}>}Num))<6"}>}Num)

Re: Calculating Top N values but excluding rows based on other columns conditions

deepakqlikview_123 — Mon, 30 Jan 2017 11:14:03 GMT

Hi All,

thanks for the reply,

For top 20 records.

=aggr(if(rank((sum(ForecastReserve) - Sum(ActualReserve)))<=20,[Dim SellRep]),[Dim CustNo])

I need to show only those records where sum(Openamount)>0

Can you please suggest how should i modify the expression.

Thanks

Re: Calculating Top N values but excluding rows based on other columns conditions

deepakqlikview_123 — Tue, 31 Jan 2017 05:24:34 GMT

Hi All,

thanks for the reply,

For top 20 records.

=aggr(if(rank((sum(ForecastReserve) - Sum(ActualReserve)))<=20,[Dim SellRep]),[Dim CustNo])

I need to show only those records where sum(Openamount)>0

Can you please suggest how should i modify the expression.

Thanks

Re: Calculating Top N values but excluding rows based on other columns conditions

deepakqlikview_123 — Tue, 31 Jan 2017 14:01:21 GMT

Hi All,

Please find attached sample app.

I need to avoid the aggregation in expresion but rowno() gives performance issues for larger data sets.

Can you please suggest how should i avoid aggregation.

Thanks