topic Re: Top n as a % of total in App Development

Top n as a % of total

prasadmundewadi — Wed, 31 Jan 2018 21:54:50 GMT

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

maxgro — Wed, 31 Jan 2018 22:32:23 GMT

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

prasadmundewadi — Thu, 01 Feb 2018 15:14:27 GMT

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

prasadmundewadi — Thu, 01 Feb 2018 15:43:36 GMT

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

Re: Top n as a % of total

maxgro — Thu, 01 Feb 2018 15:48:16 GMT

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