value of max(date) in set analysis

[wunderch]

Hi guys, 

I have the following challenge and I hope you can help me.

I have a table like that:

KFZ	Date	KM
1	05.01.2025	100
1	15.01.2025	150
1	08.02.2025	1800
1	24.02.2025	230
1	18.04.2025	400
2	02.01.2025	70
2	10.01.2025	80
2	14.02.2025	150

So I need a formula that gives me the "KM" of the min(date) and max(date) by KFZ

When I select Date from 01.01.2025 until 24.02.2025 I want 

KFZ	KM_MIN	KM_MAX
1	100	230
2	70	150

Can anybody help me?

[wunderch · Accepted solution]

Sorry guys,
I found the solution myself.

Here is the magic formula:

KM_MIN:

=only(if(aggr(nodistinct min(Date), KFZ)=num(Date), KM))

KM_MAX:

=only(if(aggr(nodistinct max(Date), KFZ)=num(Date), KM))

[marcus_sommer]

I could imagine that you didn't need an aggr() within an if() and that only() might be risky in the case that there are more as a single record per date. Therefore you may try:

min({< Date = {"$(=date(min(Date))"}>} KM)
max({< Date = {"$(=date(max(Date))"}>} KM) 

[wunderch]

I tried it, but sorry, it doesn't work.

[dncontin]

try

VehiclesData:
LOAD
KFZ,
Date(Date#(Date,'DD.MM.YYYY')) AS Date,
KM
INLINE [
KFZ, Date, KM
1, 05.01.2025, 100
1, 15.01.2025, 150
1, 08.02.2025, 1800
1, 24.02.2025, 230
1, 18.04.2025, 400
2, 02.01.2025, 70
2, 10.01.2025, 80
2, 14.02.2025, 150
];

min  = FirstSortedValue(KM, Date)
max = FirstSortedValue(KM, -Date)