Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
what's the relation between "Group" and "Category" fields ?
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
maybe you have to add the field "Category" as dimension to your table object.
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
could you show your current output and also your expected result ?
that's to clear understanding what you are looking for.
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 |
where is the dimension field "Category" in first table ?
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
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 ?
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