Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Top N % rows

Hi,

Need to display the Top N % rows in the Straight Table based on the Number entered in the Input Box (variable). 

Can someone help with an example, if this is the dummy data - 

 

AccountProductSales
AABC10000
ADEF25000
AGHI15000
AJKL18000
Bab12000
Bcd8000
Bef22000
Cabc14000
Cdef28000
Cghi42000
Cjkl38000
Cmno5000
TOTAL 237000
Labels (1)
10 Replies
trdandamudi
Honored Contributor II

Re: Top N % rows

Please see the attached file for the solution. Hope this helps..

Top N Rows.PNG

Partner
Partner

Re: Top N % rows

Hi @trdandamudi,

Thanks for the reply and a solution.
This gives me the Top N result though. I was trying for 'Top N %'.

Expected output -

Top 5% - Technically/mathematically will not give me anything.
Top 6% - Account C, Product ghi , Dollars 42000 (1 row in chart)
Top 10% - Account C, Product ghi , Dollars 42000 (1 row in chart)
Top 12% - Account C, Product ghi, jkl, Dollars 42000, 38000 (2 rows in chart)
Partner
Partner

Re: Top N % rows

Hi,

Any probable help on this please?
trdandamudi
Honored Contributor II

Re: Top N % rows

I am sorry, haven't got a chance to look at it as I was busy with few production issues. If I get a chance I will take  a look this weekend. Also in the output expected as you mentioned below:

Expected output -

Top 5% - Technically/mathematically will not give me anything.
Top 6% - Account C, Product ghi , Dollars 42000 (1 row in chart)
Top 10% - Account C, Product ghi , Dollars 42000 (1 row in chart)
Top 12% - Account C, Product ghi, jkl, Dollars 42000, 38000 (2 rows in chart)

What is the calculation to show Account C, Product ghi, Dollars 42000 under "Top 6%" ? Can you please elaborate in detail with the formula if you have one.

Thanks

 

Highlighted
Partner
Partner

Re: Top N % rows

Its Alright.
I was calculating in a reverse way for this example.

1. If I pick the largest Sales value i.e 42000, and do 237000/42000, it comes as 5.64. So the next values will be treated as 6.
trdandamudi
Honored Contributor II

Re: Top N % rows

Ok got it. Then if you say show top 10%, is it not it should show the below records in the chart ?

Account       Product    Sales           %

A                     DEF              25000        9%

C                     def               28000         8%

C                      jkl                38000         6%

C                     ghi                42000        6%

 

Also please see the calculation table below:

Top N Calc Details.PNG

 

 

Partner
Partner

Re: Top N % rows

Hi,

Thank you for quick turnaround.

I guess we are bit close. What I am understanding the requirement now, we need a 80-20 approach on this.
Whatever the N% we enter in the Input Box, that should accumulate the percentage of the records and display. Hope the below output helps.

So, Top 5% - Zero records

Top 6% -
C ghi 42000

Top 9% -
C ghi 42000

Top 10% -
C ghi 42000

Top 12% (6% + 6%) -
C ghi 42000
C jkl 38000

Top 15% -
C ghi 42000
C jkl 38000

Top 20% (6% +6% +8%)) -
C ghi 42000
C jkl 38000
C def 28000

trdandamudi
Honored Contributor II

Re: Top N % rows

I did put something together based on the %, I think now you will be in position to take it forward based on your 80 -20 approach. Hope this helps...

Top N Calc Details.PNG

 

Partner
Partner

Re: Top N % rows

Hi @trdandamudi,
Thank you much!
This example, somehow helped a bit giving some idea. Still trying to have a cumulative thing working .