get the text of a field in straight table

[#1]

HI all.

I have a straight table like this:

In Expression column (in set analysis), I have to use the text of the corresponding country (on the same row). How could I do?

Thank a lot.

luca

[#2]

thank you,

but I have no selection, neither ALT_CONTRY nor COUNTRY. So, P() is not good for me.

Unfortunately, I think Gysbert is right..

luca

[swuehl]

Changing the data model would be best, if feasible.

If it's not feasible, maybe something like this returns what you want

=SUM( AGGR( IF( ALT_COUNTRY = Country, COUNT( DISTINCT CITY)), ALT_COUNTRY, Country))

[#4]

Gysbert,

could you post an example of if statement?

thank you

[rubenmarin · Accepted solution]

The 'If' version can be:

Count(DISTINCT If(Country=ALT_COUNTRY, CITY))

[#6]

swuehl, your answer is correct too!

many many thanks to everybody

luca

[#7]

thank you very much!

luca

[rubenmarin]

And if for some reason you need to count the distinct cities across all countries his solution will give you the correct answer.

In example, with my previous solution will count 1 'Valencia' in Spain and 1 'Valencia' in Venezuela but the total distinct cities around the world will becounted as 1 city, his proposal will count 2 cities wich is more accurate.

Also this can count 2 cities:

Sum(Aggr(Count(DISTINCT If(Country=ALT_COUNTRY, CITY)), Country))

[#9]

thank you