topic Re: FirstSortedValue ? in App Development

FirstSortedValue ?

Anonymous — Wed, 25 Apr 2018 22:36:39 GMT

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.

Re: FirstSortedValue ?

Anonymous — Wed, 25 Apr 2018 23:28:22 GMT

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.

Re: FirstSortedValue ?

marcus_sommer — Thu, 26 Apr 2018 13:31:45 GMT

"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

Re: FirstSortedValue ?

Anonymous — Thu, 26 Apr 2018 15:33:44 GMT

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)