Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
"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
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)