Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zlatib24
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

 

Please help and must the expressions be in the dimension or the measure

 

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
sunilkumarqv
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))

zlatib24
New Contributor II

Re: Ranking - need help

Hi Sunil

Thanks for your reply, but still not working

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?

sunilkumarqv
Valued Contributor II

Re: Ranking - need help

try put in measure

zlatib24
New Contributor II

Re: Ranking - need help

did put it in measure, still not working

what should I put in the customer dimension field

agigliotti
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.

zlatib24
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

Sorry, please assist

Thnx again

zlatib24
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

agigliotti
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.

zlatib24
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

Community Browser