4 Replies Latest reply: Feb 1, 2018 10:48 AM by Massimo Grossi

# Top n as a % of total

 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?

• ###### Re: Top n as a % of total

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)

• ###### Re: Top n as a % of total

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.

• ###### Re: Top n as a % of total

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

• ###### Re: Top n as a % of total

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