topic Re: FirstSortedValue returns NULL despite unique maximum date - detailed example in QlikView

FirstSortedValue returns NULL despite unique maximum date - detailed example

hamid — Mon, 13 Apr 2026 14:52:20 GMT

Hello Qlik Community,

I'm encountering an unexpected behavior with FirstSortedValue and would appreciate your insights.

Context:
I have a table tracking employee appointments with fields:

EmployeeID (id_dim_salarie)
VisitID (id_visite_unique)
DateTime (date_consultation) - numeric Excel datetime
Teleconsult (teleconsultation) - text field ("Oui"/"Non")

Goal:
For each employee, retrieve the Teleconsult value from their latest appointment (maximum DateTime).

Expression used:

text

FirstSortedValue(teleconsultation, -Num(date_consultation))

Sample data (attached: Data_exploration.xlsx):

EmployeeID VisitID DateTime Status Reason Teleconsult 1000011682 10000116822025-07-15 09:00 2025-07-15 09:00:00.000 Annulée Erreur de saisie Oui 1000011682 10000116822025-07-22 10:15 2025-07-22 10:15:00.000 Annulée Absence excusée Oui

Expected result for EmployeeID 1000011682:

Latest date: 2025-07-22 10:15:00.000 (Excel numeric: 45860.4270833333)
Teleconsult value at that date: "Oui"
Should return: "Oui"

Actual result: NULL

Debug information I've verified:

Max(date_consultation) = 45860.4270833333 ✓ (correct latest date)
Count(DISTINCT date_consultation) = 2 ✓ (two distinct dates)
Count(teleconsultation) = 2 ✓ (both lines have valid "Oui" values)
No NULL values in teleconsultation for this employee ✓
Dates are properly loaded as numeric datetime values

According to the documentation, FirstSortedValue should return NULL only when "more than one resulting value shares the same sort_weight for the specified rank". Here I have a unique maximum date with a valid teleconsultation value.

Why does this expression return NULL? Is there something about the numeric datetime format or the specific context that's causing this behavior?

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

lennart_mo — Mon, 13 Apr 2026 15:16:26 GMT

Hi @hamid,

using the data sample you provided, I'm unfortunately unable to recreate your issue. The formula you used returns 'Oui', as expected.

Have you checked the tables properties in the Data model viewer? Especially "Density" and "Has duplicates".

Are you using any set expressions you didn't post here?

Which vizualization are you using with which dimensions?

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

hamid — Mon, 13 Apr 2026 15:37:40 GMT

I'm using the expression in the script as bellow:

LOAD

id_dim_salarie ,

Timestamp(Max(date_consultation) ) as Last,

FirstSortedValue(teleconsultation, -Num(date_consultation)) as last_consultaiton

From...

Groupe by id_dim_salarie

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

lennart_mo — Mon, 13 Apr 2026 15:57:48 GMT

Could you please check if your variables set to these formats?

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[,fff]';

Or if you do not want to alter these try:

FirstSortedValue(teleconsultation, -num(date#(date_consultation, 'YYYY-MM-DD hh:mm:ss[,fff]'))

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

Digvijay_Singh — Tue, 14 Apr 2026 01:50:08 GMT

Weird as it works fine for me as well. I tried in Qlik Sense, may be the attached app can help to understand the difference you have in your app.

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

hamid — Tue, 14 Apr 2026 08:17:49 GMT

Actually i have this Date format on the DB : 2025-07-22 10:15:00.000000

And the timestamp was already set to SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

Qlik Sense has recognise it as the date. what's weird is that ithe process works for other rows, there are some cases whare i've this bug.

I tried FirstSortedValue(teleconsultation, -num(date#(date_consultation, 'YYYY-MM-DD hh:mm:ss[.fff]')) still not work

I'm sharing then another extraction if you can check again please

Re: FirstSortedValue returns NULL despite unique maximum date - detailed example

marcus_sommer — Tue, 14 Apr 2026 11:59:40 GMT

I doubt that there is a bug else that you struggle with the number of digits and/or respectively the really stored values behind the visible ones which is caused from a technically limitation in regard to the used number-system. More hints to the matter: Rounding Errors - Qlik Community - 1468808.

I would apply a rounding to cut the values by max. 14 digits (the lesser the better) and applying recno() + rowno() + various num/text/len/isnum ... within the load and checking the results within a table-box. Afterwards I would continue in the script with the firstsortedvalue() ...