Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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:

RankProductAmountCumulative Amount
1A2626
2B2551
3C2475
4D2398
5E22120
6F21141
7G20161
8H19180
9I18198
10J17215
11K16231
12L15246
13M14260
14N13273
15O12285
16P11296
17Q10306
18R9315
19S8323
20T7330
21U6336
22V5341
23W4345
24X3348
25Y2350
26Z1351

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.

6 Replies

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

Aaron,

QVW?

Life is so rich, and we need to respect to the life !!!

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

May be this:

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

antoniotiman
Honored Contributor III

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

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

Very good point

Not applicable

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

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

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)

Community Browser