Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
Partner - Creator III

How to find the nth value from the table?

Hi,

I having a problem while I wanted to find the nth value from the table. I can get it when I use the dimension as it is in the load table, but when I use the dimension with the certain condition I needed, it doesn't work.

My dimension with the condition is,

if(aggr(Count(distinct BILLDATE),CUSTOMER)>1 and Aggr((Sum(NETAMT) - Sum(QTY*COSTRATE) >1000), CUSTOMER), CUSTOMER) I stored the above expression in the variable vCustomer

I tried to find the nth value by using the following expression,

aggr(if(aggr(rank(Avg(NETAMT)),($(vCustomer)))=5,Avg(NETAMT)),($(vCustomer))) in this case i wanted to find 5th row value from the table, but couldn't find.

Any help is greatly appreciated.

Thanks,

Mohan

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Do these expression really need alll the Aggr()'s? I cant make head or tail of what these expessions are trying to do. I think you need to revist them and see if they can be simplified first, before you try and add further complexity to them.

Aggr() can be a performance killer, and should only be used when truely necessary.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mohan_1105
Partner - Creator III
Partner - Creator III
Author

Hello Jonathan,

Yeah!! No. of records for each customer is more than one. That the reason I used Aggr() function, to give more clarity on my expression.

aggr(    if(aggr(rank(Avg(NETAMT)), $(vCustomer))= 5, Avg(NETAMT)),  $(vCustomer)).

I tried my best to make it clear above, I hope you understood now. This expression trying to get the 5th record value i.e.Avg(NETAMT) from the table. I am using rank function and matching it with my interested value i.e. 5 as the expression using if condition.

The expression works perfectly when I use CUSTOMER instead of vCustomer.

vinieme12
Champion III
Champion III

please post a sample app

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
mohan_1105
Partner - Creator III
Partner - Creator III
Author

Hi Vineeth,

Sorry I am not permitted to post the data in public, also I don't have much time to create the sample data with such levels.

vinieme12
Champion III
Champion III

See this thread

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.