FirstSortedValue returns NULL despite unique maximum date - detailed example

[hamid]

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:

1. Max(date_consultation) = 45860.4270833333 ✓ (correct latest date)

2. Count(DISTINCT date_consultation) = 2 ✓ (two distinct dates)

3. Count(teleconsultation) = 2 ✓ (both lines have valid "Oui" values)

4. No NULL values in teleconsultation for this employee ✓

5. 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?

[lennart_mo]

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?

[hamid]

Hi 

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

[lennart_mo]

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]'))

[Digvijay_Singh]

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.

[hamid]

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

[marcus_sommer]

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() ...