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

[deepakqlikview_123]

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

[vinieme12]

Try

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

Or this

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

[tresesco]

Try like:

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

[deepakqlikview_123]

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

[deepakqlikview_123]

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

[deepakqlikview_123]

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