# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Ranking - need help

Good Day

Please assist or give me ideas on how to do this

Have been using some statements, but just not coming right

I need to first select the Top 40 customers ranked on their Sales, that shopped in a particular Group, let’s say I want to select for Juice

Than from these top 40 customers ranked on their sales, I then need to show their sales for every Category, but only in 1 Division, lets call the Division Food

So I am using this below, but am just not coming right, not sure if I am putting it in the dimension and not measure or the other way around

Have tried the below expressions

sum({\$<Customer= P({<Group={'Juice'}>}Customer), FiscalYear = {\$(vMaxYear)}, FiscYMD = {"<=\$(=(vMaxFYMD))"}>} Sales)

sum({\$<Customer= {"=rank(sum(Sales), 4)<= 40"}>} Sales) > 0,  Customer), Customer, null()))

Thank you so much

23 Replies
Valued Contributor II

## Re: Ranking - need help

can you try below

=Sum( Aggr( If(Rank(Sum( Sales)) <=40, Sum({<Group={'Juice'},Division={'Division Food'}>}Sales)),  Category))

New Contributor II

## Re: Ranking - need help

Hi Sunil

Do I need to maybe insert the Customer field also into this expression

And do I put this in the Sales measure

or the Customer dimension?

Valued Contributor II

## Re: Ranking - need help

try put in measure

New Contributor II

## Re: Ranking - need help

did put it in measure, still not working

what should I put in the customer dimension field

Honored Contributor II

## Re: Ranking - need help

for a KPI you could use the following expression:

Sum( Aggr( If( Rank(Sum(Sales))<=40, Sum( {< Group = {'Juice'}, Division = {'Division Food'} >} Sales ) ),  Customer ) )

while for a table object you can use a calculated dimension as below:

Aggr( If( Rank(Sum(Sales))<=40, Customer ), Customer ) (plus untick "show null values")

and

Sum( {< Group = {'Juice'}, Division = {'Division Food'} >} Sales ) as measure

I hope it helps.

New Contributor II

## Re: Ranking - need help

hi Andrea

Thanks so much

I am getting somewhere with your help

However, its bringing back the Top 40 customers with Sales but also returns other customers from 41 onwards, but shows their Sales as 0

How or what do I do to remove them from the table, I did untick the 'Include null values'

Also, its bringing back the top 40, but not showing me their other Sales in the other categories for the Food Division

Thnx again

New Contributor II

## Re: Ranking - need help

Sorry I just noticved now it brings back the customers other category sales, but shows the sales as 0

And I only need it for Sales in Division = Food

But it is bring back other Divisions

Thnx

Honored Contributor II

## Re: Ranking - need help

to remove all table rows with all measures value as 0 untick "Include zero values" under Add-ons->Data handling section.

New Contributor II

## Re: Ranking - need help

Thanks Andrea

Done that, but still not 100% correct

So the report is giving me back the top 40 customers by sales

But it is not showing me their Sales in the Other categories for the Food Division

So how do I let it give me the same customers grouped together, showing all the sales byt he different categories they shopped in

And than gives me the next customer and their sales...and so on

Hope you can still help