topic Re: Min value issue with Aggr function in App Development

Min value issue with Aggr function

KrisF — Sat, 16 Mar 2024 18:44:53 GMT

I have the following input:
obs dDate value
195588 2024-02-21 13,43
194798 2024-02-20 13,55
192428 2024-02-15 13,41
191638 2024-02-14 13,31
190848 2024-02-13 13,23
190058 2024-02-12 13,81
172597 2024-01-11 14,26
170209 2024-01-08 14,62
39596 2023-05-17 13,85
38775 2023-05-16 13,78
37954 2023-05-15 13,64
37133 2023-05-12 13,34
36312 2023-05-11 13,38
35491 2023-05-10 13,44

I use this aggregate function:
=max( aggr(NODISTINCT if(min(total value,Year) = min(value), obs,0), (Year,(NUMERIC, DESCENDING)), (dDate,(NUMERIC, DESCENDING))))

I try to get the 'obs' for each year related to the lowest value of 'value'.
the formula above delivers the correct value (190848), but only for the year=2024.
The value for the previous years delivers a zero.

What is the correct way to solve this issue?

Re: Min value issue with Aggr function

LRuCelver — Mon, 18 Mar 2024 07:37:48 GMT

FirstSortedValue(obs, value)

This expression will always select the obs value associated with the smallest value regardless of what dimensions your chart has.

Re: Min value issue with Aggr function

Dataintellinalytics — Mon, 18 Mar 2024 09:35:58 GMT

PFA, snapshot.

Aggr(FirstSortedValue(obs,value),Year) should give you results for both the years, but because of load order you will see OBS value being displayed in different row.

Re: Min value issue with Aggr function

KrisF — Sun, 24 Mar 2024 16:54:25 GMT

Both options do not work, not in the separate straight table where the statistics are exposed, also not in the straight table where the data is located.

Re: Min value issue with Aggr function

KrisF — Sun, 24 Mar 2024 18:24:55 GMT

In the attached picture I made an example to illustrate the intention.

In dark yellow I marked the values that go wrong

Re: Min value issue with Aggr function

BrunPierre — Sun, 24 Mar 2024 18:30:42 GMT

Perhaps

=Only(If(Aggr(NODISTINCT Min(value), Year) = value, obs))

Re: Min value issue with Aggr function

KrisF — Mon, 25 Mar 2024 02:26:25 GMT

Yes,I've got the expected results, thank you