topic Re: How to total Top N row (by sort date) in QlikView

How to total Top N row (by sort date)

rsark — Thu, 24 Jan 2013 21:13:24 GMT

Hello,

I am attempting to create a expression to sum total quantity for top N in list. I have attempted to use the Rank, Aggr, Presentation Max Number setting and I am not having any luck. Any suggestions and/or assistance would greatly be appreciated. (see attached .QVW with sample data detail and expected results)

Regards,

Ron

Re: How to total Top N row (by sort date)

whiteline — Sun, 27 Jan 2013 13:12:20 GMT

Hi.

You can use set analysis with rank, but you'll need some key field to perform this (I've used PDate as it's different for eachrow).

For top 5 sum:

=sum({$<PDATE={"=Rank(total sum(PQTY))<=5"}>}PQTY)

Re: How to total Top N row (by sort date)

Sun, 27 Jan 2013 14:00:59 GMT

Hi.

I used the whiteline expression and I made a little change.

The expression

=sum( {<PCODE= p({$<PCODE={"=Rank(sum(PQTY))<=5"}>}) >} PQTY)

show the sum for the 5 tops PCODE.

Alessandro Furtado

Re: How to total Top N row (by sort date)

Anonymous — Sun, 27 Jan 2013 17:50:59 GMT

Dear Rasark,

Please find the attachment of application let me know.

Regards

Re: How to total Top N row (by sort date)

rsark — Mon, 28 Jan 2013 15:15:44 GMT

Hello All,

The Whiteline approach would work great (Thanks) if I was looking to rank QTY, but the requirement is to rank/sort the PDATE and sum the QTY by Top N

Sort/Rank the date in descending order for the current selection and sum the QTY for the Top N. The repsonse Shaik submitted was close, but the RowNos were loaded via script and the production load will be over 150,000k rows...

Thanks,

Ron

Re: How to total Top N row (by sort date)

whiteline — Sat, 02 Feb 2013 19:08:43 GMT

Sort/Rank the date in descending order for the current selection and sum the QTY for the Top N.

I think it's even easier:

=sum({$<PDATE={"=Rank(total ONLY(PDATE))<=5"}>}PQTY)