FirstSortedValue ?

[Anonymous]

I have a table with 2 columns: AL_Datetime and AL_count.  Each hour, there is a row in the table with a count. 

In a KPI, I'm trying to display the count for the MAX AL_Datetime. 

I thought this was easy: FirstSortedValue( AL_count, -AL_Datetime). 

However, this returns ' - ' ... What am I missing?

Sample value for AL_Datetime = 04/18/2018 10:00

When I show the data in a table on the dashboard, I'm able to sort successfully by AL_Datetime so Qlik Sense seems to know how to sort by that dimension in a table.

[Anonymous]

Using Set Analysis doesn't give me a result either:

Sum({$<AL_datetime={'$(=Max(AL_datetime))'}>} AL_cntFormer)

It does when I hardcode a datetime value in the set analysis, but not when I introduce the {'$(=Max(AL_datetime))'}

Creating a KPI with formula Max(AL_datetime) also displays ' - '.  Not sure what's going on with this field ...

P.S. In the Set Analysis above, Sum technically speaking isn't needed as each datetime has a unique row.

[marcus_sommer]

"Max(AL_datetime) also displays ' - '." indicates that AL_datetime isn't a timestamp else a string and needs to be converted. See here what is meant: Get the Dates Right.

- Marcus

[Anonymous · Accepted solution]

Thanks.  I ended up adding a new field to the load script that adds the date and time portions together: I'm using this new field for sorting.  Using the "timestamp" and "timestamp#" functions kept providing ' - ' even when I rebuilt the string and added together the date and time portion.

Date(Left(datetimefield, 10)) + Right(datetimefield, 5)