topic Re: Top N % rows in QlikView

Top N % rows

dmohanty — Sat, 16 Nov 2024 21:14:33 GMT

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

Re: Top N % rows

trdandamudi — Thu, 14 Mar 2019 15:34:17 GMT

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

Re: Top N % rows

dmohanty — Thu, 14 Mar 2019 15:41:50 GMT

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)

Re: Top N % rows

dmohanty — Fri, 15 Mar 2019 11:35:01 GMT

Hi,

Any probable help on this please?

Re: Top N % rows

trdandamudi — Fri, 15 Mar 2019 12:48:59 GMT

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

Re: Top N % rows

dmohanty — Fri, 15 Mar 2019 13:08:33 GMT

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.

Re: Top N % rows

trdandamudi — Fri, 15 Mar 2019 13:51:30 GMT

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:

Re: Top N % rows

dmohanty — Fri, 15 Mar 2019 14:23:45 GMT

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

Re: Top N % rows

trdandamudi — Fri, 15 Mar 2019 14:51:52 GMT

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

Re: Top N % rows

dmohanty — Mon, 18 Mar 2019 14:18:33 GMT

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

Re: Top N % rows

Brett_Bleess — Mon, 25 Mar 2019 21:07:08 GMT

Check the following Help link, and scroll down to the Pareto Select under Action Types and Actions and subsection Selection, that might be something that could work better for you here.

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Ajax/WebView_Button.htm

Regards,
Brett