topic Aggr and set analysis expression in QlikView

Aggr and set analysis expression

inescastelhano — Tue, 27 Sep 2016 13:48:27 GMT

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

Re: Aggr and set analysis expression

sunny_talwar — Tue, 27 Sep 2016 13:51:45 GMT

May be try this:

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

Re: Aggr and set analysis expression

sunny_talwar — Tue, 27 Sep 2016 13:54:25 GMT

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

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

Re: Aggr and set analysis expression

inescastelhano — Tue, 27 Sep 2016 14:31:25 GMT

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!

Re: Aggr and set analysis expression

sunny_talwar — Tue, 27 Sep 2016 14:35:16 GMT

You want to have VALUE_TO_CALC as one of your dimensions?

Re: Aggr and set analysis expression

inescastelhano — Tue, 27 Sep 2016 14:42:37 GMT

Nope.

Re: Aggr and set analysis expression

sunny_talwar — Tue, 27 Sep 2016 14:43:32 GMT

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)

Re: Aggr and set analysis expression

inescastelhano — Tue, 27 Sep 2016 14:49:59 GMT

Thank you!!