Top n as a % of total

[prasadmundewadi]

I have data like shown in below table:
Product	Oppty	Value	SalesRep
A	Op1	100	Adam
A	Op2	130	Bill
A	Op3	400	Paul
B	Op4	500	Adam
B	Op5	200	Bill
B	Op6	100	Paul
B	Op7	120	Paul
C	Op8	150	Adam
C	Op9	700	Bill
C	Op10	220	Paul
Here Sum(value) = 2620

    I want to get a Pivot table like below in Qlik.

Product	As % of top 2 of total
A	530/2620	--> derived as (400 + 130) since 400 and 130 are the top 2 values
B	700/2620	--> derived as (500+200)
C	920/2620	--> derived as (700+220)

How can i do this?

[maxgro]

Try these expressions

Text(

subfield(concat(total <Product>  Value, '-', -Value), '-', 1)+

subfield(concat(total <Product>  Value, '-', -Value), '-', 2)

) & '/' & sum(TOTAL Value)

(

subfield(concat(total <Product>  Value, '-', -Value), '-', 1)+

subfield(concat(total <Product>  Value, '-', -Value), '-', 2)

)

/ sum(TOTAL Value)

[prasadmundewadi]

Thanks this worked for this example.

(

subfield(concat(total <Product>  Value, '-', -Value), '-', 1)+

subfield(concat(total <Product>  Value, '-', -Value), '-', 2)

)

/ sum(TOTAL Value)

But what I have is: a data set which has about 20000 records and i want to be able to do it  for the top 10% of the data. Not top 2 records. So in that case the formula might get very lengthy. Is there a way to do this dynamically instead of adding multiple subfield expressions.

[prasadmundewadi]

I really want the calculated %. e.g. not '530/2620' but 20.23%

[maxgro · Accepted solution]

this is a percentage

(

subfield(concat(total <Product>  Value, '-', -Value), '-', 1)+

subfield(concat(total <Product>  Value, '-', -Value), '-', 2)

)

/ sum(TOTAL Value)

if you want to know which products contribute to the first % of the total maybe this can help you

Recipe for a Pareto Analysis – Revisited