Top N % rows

[dmohanty]

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 - 

Account	Product	Sales
A	ABC	10000
A	DEF	25000
A	GHI	15000
A	JKL	18000
B	ab	12000
B	cd	8000
B	ef	22000
C	abc	14000
C	def	28000
C	ghi	42000
C	jkl	38000
C	mno	5000
TOTAL		237000

[trdandamudi]

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

[dmohanty]

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)

[dmohanty]

Hi,

Any probable help on this please?

[trdandamudi]

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

[dmohanty]

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]

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:

[dmohanty]

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]

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...

[dmohanty]

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