Aggr and set analysis expression

[inescastelhano]

Hi all,

The following table is a sample of my data. I have the countries, the FIELD_VALUE, the ID, MyField (only to filter) and my VALUE_TO_CALC.

What I want to calculate is the only of VALUE_TO_CALC, filtering by MyField=something, and where the ID = max(ID) per country. Then, for AT it should return 3, for BE it should return 4, etc.

This is my expression, but it is returning the max of ID, instead of the max of VALUE_TO_CALC:

only({<MyField={'something'}>}

aggr( Max(ID),COUNTRY))

I would really appreciate any help.

Thanks in advance.

Cheers,

Inês

[sunny_talwar · Accepted solution]

May be try this:

Only({<MyField={'something'}>} Aggr(FirstSortedValue({<MyField={'something'}>} VALUE_TO_CALC, -ID), COUNTRY))

[sunny_talwar]

Not sure what your dimensions are, but you may not need Aggr() function here:

FirstSortedValue({<MyField={'something'}>} VALUE_TO_CALC, -ID)

[inescastelhano]

Hello!

Thanks, it works but there is one problem: this is retrieving the value in the first entry, and not in the max. For example I think that for AT it should return 55 but in the row of 55, and not in the row of 57. Do you have any idea to solve this?

Thanks!

[sunny_talwar]

You want to have VALUE_TO_CALC as one of your dimensions?

[inescastelhano]

Nope.

[sunny_talwar]

May be try like this:

Dimension

COUNTRY

Expressions

FirstSortedValue({<MyField={'something'}>} FIELD_VALUE, -ID)

Max({<MyField={'something'}>} ID)

FirstSortedValue({<MyField={'something'}>} MyField, -ID)

FirstSortedValue({<MyField={'something'}>} VALUE_TO_CALC, -ID)

[inescastelhano]

Thank you!!