How to display a text related to a min value in a text object ?

[#1]

Hi,

I've got two fields : 'CityName' and 'Population'. In a Text Object, I would like to display the City name related to the min(agregated) population in a text box. Do you know how to do this ?

Best regards

[#2]

I did something like that with:

='The smallest City is: '&Fieldvalue('CityName',Fieldindex('Population',min('Population')))&' with '&min(Population)&' inhabitants'

[#3]

sorry for the typo in the first min (no ' ' needed), must be:

='The smallest City is: '&Fieldvalue('CityName',Fieldindex('Population', min(Population)))&' with '&min(Population)&' inhabitants'

[#4]

Hi Christian,

that's a good suggestion, but in my case it's a bit more complex. I forgot to precise something : In fact I do not have directly a field called 'Population',
I've have to use set analysis in a global field in order to select only the population. So the field index do not work because the looked value doesn't exist directly, I need to make an aggregation before. Here is the difficulty 🙂

Any other idea ?

Best regards

[#5]

Hi Bertrnad,

works even with more complex set analysis cases like:

LOAD * INLINE [
City, Value, Type
a, 100, P
b, 200, P
c, 300, P
a, 15, G
b, 10, G
c, 12, G
d, 50, P
];

LOAD * INLINE [
City, Use
a, yes
b, yes
c, yes
d, no
];

='min for Type P: '&fieldvalue('City',fieldindex('Value',min( {$<Type = {P}, Use = {yes}>} Value)))&' = '&min({$<Type = {P}, Use = {yes}>} Value)

Any short example from your structure (I would like to learn, too) ?

Christian

[#6]

Let's add a few challenge 🙂 :

To use your example, just concentrate upon Type and value and forget City and use :
I would to diplay the type (P or G) where Avg(Value) is the minimum. That is to say : I'm looking for the minimum of an aggregation (Avg (Value)) and not an existing value of the field 'Value'. 🙂

That's the problem and that's why I'm fighting with Set Analysis and aggr function 🙂 (not sure that's the best way).

Best regards

[#7]

In a text object it is really difficult, I agree.

as a workaround I would give the result in a small table with only one line...

LOAD * INLINE [
 City, Value, Type
 a, 100, P
 b, 200, P
 c, 300, P
 a, 15, G
 b, 10, G
 c, 12, G
 d, 50, P
 a, 70, X
 b, 50, X
 d, 30, X
];

Than I prepared a chart-table with three columns:

1. Dimension: Type [sort by rank(avg(total <Type> Value)) ]
2. Expression (Name: minimum average type): if(count(distinct total Type) =
rank(avg(Value)),Type)
3. Expression (Name: minimum average): avg(total <Type> Value)

Column 1 is hidden, in chart settings presentation I choose max number of
lines = 1, no caption...

This gives the small table with column header and one row with the type of
the min average and the value of the min average...

Keep me updated when you found a better solution...

Regards

Christian

[#8 · Accepted solution]

got it in a text box:

=subfield(concat(aggr(rank(avg(total <Type> Value))&'#'&Type,Type)),'#',-1)&': '&round(min( aggr( avg(total <Type> Value),Type) ),0.01)

Rgds.

Christian

[#9]

Thanks for your efforts upon that difficult case,
it seems to works. 🙂

I've one more question (but maybe you're fed up with that subject) : is it possible ton make an aggregation on two dimensions. ? That is to say same question but with min of (Month and Type) instead of Type alone.

Anyway thanks again for your help

.

[#10]

omg

=subfield(concat(aggr(rank(avg(total Value))&'#'&Type,Type)),'#',-1)&': '&round(min( aggr( avg(total Value),Type) ),0.01)