Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

prasadmundewadi
New Contributor III

Top n as a % of total

I have data like shown in below table:
ProductOpptyValueSalesRep
AOp1100Adam
AOp2130Bill
AOp3400Paul
BOp4500Adam
BOp5200Bill
BOp6100Paul
BOp7120Paul
COp8150Adam
COp9700Bill
COp10220Paul
Here Sum(value) = 2620

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

ProductAs % of top 2 of total
A530/2620--> derived as (400 + 130) since 400 and 130 are the top 2 values
B700/2620--> derived as (500+200)
C920/2620--> derived as (700+220)

How can i do this?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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

4 Replies
MVP
MVP

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)

1.png

prasadmundewadi
New Contributor III

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.

prasadmundewadi
New Contributor III

Re: Top n as a % of total

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

MVP
MVP

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

Community Browser