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
can you try below
=Sum( Aggr( If(Rank(Sum( Sales)) <=40, Sum({<Group={'Juice'},Division={'Division Food'}>}Sales)), Category))
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?
try put in measure
did put it in measure, still not working
what should I put in the customer dimension field
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.
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
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
to remove all table rows with all measures value as 0 untick "Include zero values" under Add-ons->Data handling section.
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