Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

Highlighted
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

 

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 .