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

[agigliotti]

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

[Anonymous]

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

[agigliotti]

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

[Anonymous]

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

[agigliotti]

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

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

[Anonymous]

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

[agigliotti]

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

[Anonymous]

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

[agigliotti]

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 ?

[Anonymous]

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