Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data for week 5..
Table1:
Company Site Sales
Company 1 Site 1 1000
Company 1 Site 2 1800
Company 1 Site 3 1250
Company 1 Site 4 2500
Company 2 Site 1 1700
Company 2 Site 2 2000
Company 2 Site 3 4250
Company 2 Site 4 1500
Company 3 Site 1 4000
Company 3 Site 2 9800
Company 3 Site 3 1450
Company 3 Site 4 2800
Table 2
Company Avg Sales Rank
Company 1 1637 3
Company 2 2362 2
Company 3 4512 1
Each Company has its own tab within the application. We want to display the rank of 'Customer 1' in a text box (Obviously the rank will be based on a week).
e.g.
Week 5 is selected.
The text box will display Customer 1's rank (3)
We also want to display the Site rank too.
So for example,
In week 5, the best site for customer 1 is site 4 which ranks no.5
Can anyone help please?
Maybe
=only({<Company={'Company 1'}>} aggr(rank(avg(Sales)),Company))
for the rank across Companies and
=only({<CompanySite={"=rank(sum({<Company={'Company 1'}>}Sales))=1"}>} aggr(rank(sum(Sales)),CompanySite))
for the rank of the max sales sites across all sites (I created an additional field Company&Site as CompanySite )
Regards,
Stefan
Any ideas on this?
Maybe
=only({<Company={'Company 1'}>} aggr(rank(avg(Sales)),Company))
for the rank across Companies and
=only({<CompanySite={"=rank(sum({<Company={'Company 1'}>}Sales))=1"}>} aggr(rank(sum(Sales)),CompanySite))
for the rank of the max sales sites across all sites (I created an additional field Company&Site as CompanySite )
Regards,
Stefan
Thanks Stefan, that worked.
Hi Stefan,
I have a problem with the answer you gave me..
I tested it in an example script with made up fields. I have now given it to a client to try and they have come back with a much more complicated query.
They work out average sales by using the following expression
Sum({1<'Channel Label' -= {Web, 'Dir*'},SalesWeek={$(=vSalesWeek)}
>}Valid_AmountSummary)/
Count({1<'Channel Label' -= {Web, 'Dir*'},SalesWeek={$(=vSalesWeek)}
>}store_name)
So now i need to get the result as described in my original question but by using the above expression for Avg Sales.
'Store_Name' is the field name for 'Customer'
This is just for the first part of the question. They havent given me the field name as yet for the 'Site' field.
Can you help?
Thanks
Maybe I am missing something here, but you should be able to replace the avg(Sales) in the above rank() function with your calculation of average. Have you tried that?