Ranking - need help

[Anonymous]

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

[sunilkumarqv]

can you try below

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

[Anonymous]

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]

try put in measure

[Anonymous]

did put it in measure, still not working

what should I put in the customer dimension field

[agigliotti]

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.

[Anonymous]

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

[Anonymous]

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]

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

[Anonymous]

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