display rank in text object

[hopkinsc]

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?

[hopkinsc]

Any ideas on this?

[swuehl · Accepted solution]

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

[hopkinsc]

Thanks Stefan, that worked.

[hopkinsc]

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

[swuehl]

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?