topic Re: Firstsortedvalue weird behavior in App Development

Firstsortedvalue weird behavior

SGV — Thu, 16 Oct 2025 21:06:10 GMT

Hello, please see scenario below:

App A:

Based on sample data:

Load * inline [
Date, Price
1/10/2025,10
1/10/2025,12
1/10/2025,15
2/10/2025,18
2/10/2025,19
3/10/2025,25
];

Close = Avg(price)

App B:

Without Distinct keyword

Close = Avg(price)

With Distinct Keyword

Close = Avg(price)

Not sure what is causing the Firstsortedvalue to work with a Distinct. Since the Aggregation should output only a distinct value per row by default right? Maybe beacuse the Date field is derived from a Timestamp field?

My question is, am I hallucinating? or have I missed a simple trick. Please assist.

Thanks!

@rwunderlich @hic

Re: Firstsortedvalue weird behavior

rwunderlich — Sat, 25 Oct 2025 22:38:07 GMT

With some input from my colleague @Oleg_Troyansky I think I can explain. First you need to add a second 3/10 value to make your example equal to App B. I'll also add a record id for illustration.

Load *, RecNo() as RecNo inline [
Date, Price
1/10/2025,15
2/10/2025,18
2/10/2025,19
3/10/2025,25
3/10/2025,30
];

While it's true that the Aggr() returns three values, they get associated back to the five Date rows. You can see this if you add the RecNo to a chart:

Hence there are two values associated with the last Date, so you must use DISTINCT.

-Rob