Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

top 3 departments in a sentence

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!!

 

Labels (3)
2 Solutions

Accepted Solutions
MVP
MVP

Re: top 3 departments in a sentence

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.'

 

 

Partner
Partner

Re: top 3 departments in a sentence

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 

Work smarter, not harder
4 Replies
MVP
MVP

Re: top 3 departments in a sentence

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.' 

 

Partner
Partner

Re: top 3 departments in a sentence

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.'

 

MVP
MVP

Re: top 3 departments in a sentence

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.'

 

 

Partner
Partner

Re: top 3 departments in a sentence

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 

Work smarter, not harder