Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasadmundewadi
Contributor III
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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

4 Replies
maxgro
MVP
MVP

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

maxgro
MVP
MVP

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