Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Contributor III

## Caclulate sum of top 20

I have to prepare a table with dimension Account Managers.

I need to caclulate for each Account Manager the sum of his top 20 customers (calculated column 1)

Then I need to calculate the result of this exercise with total sales of each account manager (calculated column 2)

Anyone who can give me the clue for this ?

5 Replies
Specialist

See Rank()

rank( sum( Sales )) gives you rank in top

You will need to combine with sum(aggr( 'expr', [Account Manager] )) to get Top 20 sum for each Manager

Specialist II

Hi

The below expression gives you the original rank

=avg(aggr(rank( sum( {\$<AccountManager=>} Sales) ), AccountManager))

The below gives the top 20

=if(avg(aggr(rank( sum( {\$<AccountManager=>} Sales) ), AccountManager)) <= 20, AccountManager)

Thanks

Satish

MVP

Maybe like this (in a table with dimension [Account Manager]):

Expression1:

=Sum( Aggr( If( Rank( Sum(Sales))<= 20, Sum(Sales) ), [Account Manager], [Customer]))

Expression2:

=Sum(Sales)

[haven't fully understood what you mean with 'calculate the result of this exercise with total sales of each account manager']

Contributor III
Author

Perfect,

However, some customers are visited by multiple accountmanagers.

I think that this formula takes in deed the top 20 clients, but takes in consideration total sales of these customers, so including the sales by other account managers.

Any idea how to solve ?

MVP

It should only take the sales of the customer into account that relates to the  account manager.

If you get not the requested result, could you upload some sample data or even better a small sample QVW?

Community Browser