calculate highest ranked sales excluding the selected company

[arjunmadhusudan]

Hi,

I have data as below

Company    Sales

A                  1000

A                     400

B                    500

B                      20 

C                  2000

D                    400

D                      20

I need to calculate     Sum of sales selected company /   Sales of company with highest sale from the remaining list ( not selected)

Regards,

Arjun

[olivierrobin]

hello

i think you can achieve that by ysing set analysis with e() set modifier

ie :

sum for selected company / sum( e(selected company))

[arjunmadhusudan]

I need only want company with max sales in the denominator from the not selected list.

[olivierrobin]

something like

max(aggr(sum(value),company)) combined with the exclusion of the selected one

[qv_testing]

PFA,

[arjunmadhusudan]

Oliver,

Also I want display the name of this company. How should I modify the expression to display the name of the company in the denominator?

[olivierrobin]

you could use only(<set analysis compuying the max value>company name)

[qv_testing]

Have tried my attached file ??

[arjunmadhusudan]

Oliver,

refer below expression where cursale is my sales and Name is my company name which I want to display.

Only({<Max({< Name = e(Name) >} Aggr(Sum({<Name = e(Name)>}CurSale),Name))>}Name)

I m not getting any result using this.

[Anonymous]

Hi Arjun,

For Value You can use the aggr function. For company name i am not sure where you want to show the company name i.e expression ot title of chart. However, if You want to show the name of company you can use first sorted value as below .

=FirstSortedValue(distinct {<Company=e(Company)>}Company, aggr(-sum({<Company=e(Company)>}Sales),Company),1)

I hope this helps.