23 Replies Latest reply: Aug 3, 2018 5:11 AM by Zaheer Latib

# 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

• ###### Re: Ranking - need help

can you try below

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

• ###### 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?

• ###### Re: Ranking - need help

try put in measure

• ###### Re: Ranking - need help

did put it in measure, still not working

what should I put in the customer dimension field

• ###### 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.

• ###### 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

• ###### 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

• ###### 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.

• ###### 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

• ###### Re: Ranking - need help

what's the relation between "Group" and "Category" fields ?

• ###### Re: Ranking - need help

its just a hierarchy level

so we select the top 40 customers in group

but the table must show the sales by each category in the food division

so in Food, select 1st the top 40 customers in the Group, juice, but show their basket mix of all Food Sales by Category

I really hope it makes sense

• ###### Re: Ranking - need help

maybe you have to add the field "Category" as dimension to your table object.

• ###### Re: Ranking - need help

Hi

Already have it in dimensions so it can show me these customers sales by the different Category under the Group that was selected

Any other ideas for me please

If you can and do not mind

• ###### Re: Ranking - need help

could you show your current output and also your expected result ?

that's to clear understanding what you are looking for.

• ###### Re: Ranking - need help

so as per below it will show the top customers bought Juice (GROUP) and that category

 Customer Group Sales A Juice R10 B Juice R15

This is the expected output

 Customer Group Category Sales A Juice Juice Can R10 A Juice Juice 1L R15 A Juice Juice 2L R20 B Juice Juice 1L R15 B Juice Juice 1.5L R18 B Juice Juice 2L R30 B Juice Juice 6 x 1L R90 B Juice Juice 6 x 2L R180
• ###### Re: Ranking - need help

where is the dimension field "Category" in first table ?

• ###### Re: Ranking - need help

sorry here it is

 Customer Group Category Sales A Juice Juice Can R10 B Juice Juice 1L R15

Also when the table comes back showing all the different categories in the Juice GROUP

It must show the total sales (if possible) per customer

• ###### Re: Ranking - need help

i think your table is as below:

first dimension : Aggr( If( Rank(Sum(Sales))<=40, Customer ), Customer )

second dimension: Group

third dimension: Category

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

is it right ?

• ###### Re: Ranking - need help

Hi

No still not

Let me just check a few things and get back to you

But thanks so much for really trying to go out of ur way to assist

I really appreciate it

Chat soon

• ###### Re: Ranking - need help

Hi

Still do not have the correct answer

• ###### Re: Ranking - need help

as I asked above, i'd need to see your table dimensions and measures.

• ###### Re: Ranking - need help

Hi

I managed to come right

Thanks so much for trying to help me

Really appreciate it