Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

slribeiro
New Contributor III

Calculate sum of top 10 (concatenated dimensions)

Greetings

I have the following rank table

Dimension: Name & ' | ' & Company

Expression: sum(Value)

And i select the top 10 and i want to present in a text box in qliksense the sum of that sub total.

Regards,

Simão Ribeiro

1 Solution

Accepted Solutions

Re: Calculate sum of top 10 (concatenated dimensions)

You can try adding the expression to the rank function:

=Sum(Aggr(If(Rank(Sum(TOTAL <Company> ({$<[field1]={"hello"}, field2= {$(=max(field3))>} Value)/1000

))<=10, sum({$<[field1]={"hello"}, field2= {$(=max(field3))}>} Value)/1000), Company))

or:

=Sum({<Company={"=Rank(sum({$<[field1]={'hello'}, field2={$(=max(field3))}>} Value)/1000)<=10"}>} sum({$<[field1]={'hello'}, field2={$(=max(field3))}>} Value)/1000))

3 Replies

Re: Calculate sum of top 10 (concatenated dimensions)

Hi Simão, if Name and Company have a 1-1 relation this expression should work:

=Sum(Aggr(If(Rank(Sum(TOTAL <Company> Value))<=10, Sum(Value)), Company))

This expression also can work:

=Sum({<Company={"=Rank(Sum(Value))<=10"}>} Value)

slribeiro
New Contributor III

Re: Calculate sum of top 10 (concatenated dimensions)

I think i should work but in the truth i have a table a little more complicated and i can't integrate all in one expression.

So, my expression is not just Sum(Value)... Instead i use some set analysis like so...

sum({$<[field1]={"hello"},

  field2= {$(=max(field3))}

     >} Value)/1000

Re: Calculate sum of top 10 (concatenated dimensions)

You can try adding the expression to the rank function:

=Sum(Aggr(If(Rank(Sum(TOTAL <Company> ({$<[field1]={"hello"}, field2= {$(=max(field3))>} Value)/1000

))<=10, sum({$<[field1]={"hello"}, field2= {$(=max(field3))}>} Value)/1000), Company))

or:

=Sum({<Company={"=Rank(sum({$<[field1]={'hello'}, field2={$(=max(field3))}>} Value)/1000)<=10"}>} sum({$<[field1]={'hello'}, field2={$(=max(field3))}>} Value)/1000))