top 3 departments in a sentence

[thierrytt1102]

Dear, 

I have a table like this

Department    |   sum(Sales)
A                          |    100
B                         |    200
C                         |    300
D                         |    800
E                         |    100
F                         |    1000

And I 'd like to group the 3 best departments in a sentence like: 

"Departments  F, D and C have the best score."

Could you help me in designing this formula? 

Thank you!!

[tresesco]

Try like:

='Departments  '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department)
              & ' , ' & FirstSortedValue(Department, -Aggr(Sum(Sales), Department, 2)  
& ' and '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department, 3)& 'have the best score.' 

[thierrytt1102]

Hi, 

Thx for your response, we're almost done...

when I'm creating an app with:

load* Inline [Department,Sales
A,100
B,200
C,300
D,800
E,100
F,1000];

I'm copy pasting your formula, correcting a little parenthesis error, I have the first value, but not the other..

Can you help me?

Formula: 

'Departments '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department))
& ' , ' & FirstSortedValue(Department, -Aggr(Sum(Sales), Department, 2) )
& ' and '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department, 3))& 'have the best score.'

[tresesco · Accepted solution]

One more correction is required. Try:

='Departments '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department))
& ' , ' & FirstSortedValue(Department, -Aggr(Sum(Sales), Department),2 )
& ' and '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department),3)& 'have the best score.'

 

[JordyWegman · Accepted solution]

Hi,

The commas with the numbers were wrong placed. Use this:

'Departments '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department))
& ' , ' & FirstSortedValue(Department, -Aggr(Sum(Sales), Department),2 )
& ' and '& FirstSortedValue(Department, -Aggr(Sum(Sales), Department),3)& 'have the best score.'

Jordy

Climber