Only Display the highest record in a text object

[#1]

Hi Community,

I have a question regarding text objects. I have a list of airports flown to, please see below

Airport

Airport	No. of times flown to
BEG	131
BHX	83
OTP	66
SRZ	61
LOS	55

I created this as a straight table so i'll need some sort of count in the text object. =Count({$<[Product] = {'Freight'} >}[To Airport]) so the raw data is BEG 131 rows BHX 83 rows and so on

As you know if I put this in a text object i would only get the total amount as there are no Dimensions 396 in this case but what I want is the Name of the airport so something like

='Most flown to Airport ' & 'BEG'

I hope this makes sense!

Thank you in advance

[MK_QSL]

='Most flown to Airport '  &FirstSortedValue(Airport,-[No. of times flown to])&CHR(10)&SUM([No. of times flown to])

[arjunkrishnan]

Use This Expression It Will Useful For U

='Most flown to Airport '  &FirstSortedValue(Airport,-aggr(sum(No. of times flown to),Airport))

[MayilVahanan]

Try like this

='Most flown to Airport ' &  FirstSortedValue(Airport, -[No. of times flown to])

[sudeepkm]

you can also keep it in a script level. please take a look at the attachment.

[#6]

Sorry I think the data I've given you is miss-leading.

my data set is actually

airport

BEG

BEG

BEG

BEG

BEG...

BHX

BHX
BHX

BHX....

and so on.

so i have to Count the number of BEG's and show BEG in the text object instead of the number of BEG's

[MK_QSL]

='Most flown to Airport '  &FirstSortedValue(Airport,-Aggr(SUM([No. of times flown to]),Airport))&CHR(10)&SUM([No. of times flown to])

[jonathandienst]

Hi

=FirstSortedValue(airport, -Aggr(Count(airport), airport))

HTH

Jonathan

[pgrenier]

Hello Mark,

Presuming you might have an index column in your flight table (Flight_ID), I believe you could achieve your result using the following expression:

=FirstSortedValue({$<[Product] = {'Freight'} >}[To Airport], -Aggr({$<[Product] = {'Freight'} >} Count(DISTINCT Flight_ID), [To Airport]))

Regards,

Philippe

[#10]

Hi,

Use this script

Tab:

Load * Inline [
Airport, No. of times flown to
BEG, 131
BEG, 100
BHX, 83
BHX, 88
OTP, 66
OTP, 60
SRZ, 61
LOS, 55
BEG, 80
];

Tab2:
Load
Max([No. of times flown to]) as MaxVal
Resident Tab;

Let vMax = Peek('MaxVal',-1,'Tab2');

Drop Table Tab2;

Tab2:

Load

Airport as Airport_Max,

[No. of times flown to] as MaxTimesFlown

Resident Tab

Where [No. of times flown to]='$(vMax)';

In Text Object ---> =Airport_Max&' : ' &MaxTimesFlown