topic Re: How do I get the number of products that is 80% to of total in a Text box in QlikView

How do I get the number of products that is 80% to of total in a Text box

Tue, 28 Jun 2016 17:59:06 GMT

All:

I am trying to get the number of items where 80% of the cumulative total displayed in a text box.

An example of this is the following in a table format:

Rank	Product	Amount	Cumulative Amount
1	A	26	26
2	B	25	51
3	C	24	75
4	D	23	98
5	E	22	120
6	F	21	141
7	G	20	161
8	H	19	180
9	I	18	198
10	J	17	215
11	K	16	231
12	L	15	246
13	M	14	260
14	N	13	273
15	O	12	285
16	P	11	296
17	Q	10	306
18	R	9	315
19	S	8	323
20	T	7	330
21	U	6	336
22	V	5	341
23	W	4	345
24	X	3	348
25	Y	2	350
26	Z	1	351

There are 26 products in this instance.

80% of 351 is 280.8

I want to display the number of products not over the cumulative amount inside a text box. In this case it would be 14.

Any help on this would greatly be appreciated.

Re: How do I get the number of products that is 80% to of total in a Text box

Anil_Babu_Samineni — Tue, 28 Jun 2016 18:13:35 GMT

Aaron,

QVW?

Re: How do I get the number of products that is 80% to of total in a Text box

sunny_talwar — Tue, 28 Jun 2016 18:29:16 GMT

May be this:

Count(DISTINCT {<Product = {"=RangeSum(Above(Sum(Amount), 0, RowNo()))/Sum(TOTAL Amount) <= 0.80"}>}Product)

Re: How do I get the number of products that is 80% to of total in a Text box

antoniotiman — Tue, 28 Jun 2016 18:53:32 GMT

Sunny,

this works if Products are sorted by Amount.

I think You need Macro.

Re: How do I get the number of products that is 80% to of total in a Text box

sunny_talwar — Tue, 28 Jun 2016 19:07:52 GMT

Very good point

Re: How do I get the number of products that is 80% to of total in a Text box

Tue, 28 Jun 2016 19:10:54 GMT

Yes my data is not necessarily in sorted order.

Re: How do I get the number of products that is 80% to of total in a Text box

sunny_talwar — Tue, 28 Jun 2016 19:32:28 GMT

For anyone using QV12 or above, this should work:

=Count(DISTINCT {<Product = {"=Aggr(RangeSum(Above(TOTAL Sum(Amount), 0, RowNo(TOTAL))), (Amount, (NUMERIC, DESCENDING)), Product)/Sum(TOTAL Amount) <= 0.80"}>}Product)