Timestamp date extraction and formatting

joey_lutes — Tue, 06 Feb 2018 21:48:13 GMT

I have now scoured the forums for a week and tried everything I can find to no avail.

While my issue is more complex (multiple date fields, linking to common data, etc), let's start small.

My date fields are in timestamp format. The timestamps are important to the visualizations I will display.

Format: YYYY-MM-DD hh:mm:ss (2017-10-04 05:38:00) = Start

My DateFormat =

SET DateFormat='YYYY-MM-DD';

One of the simplest things I'd like to be able to do is to extract the date into its own field.

The closest thing I've found that *kind of* works is

Date(Floor(Start)) as StartDate

My requirement would be to be able to select a date and see all the timestamps / ids associated with that date.

I have yet to be successful in that.

So, for example, let's say I have an id coming in every hour. For any given day, I'd have 24 timestamped ids in my data.

I should be able to select StartDate (even from a filter) and see no more, no less than 24 IDs and timestamps.

I've tried:

Date(Floor(Timestamp#(Start, 'YYYY-MM-DD hh:mm:ss')), 'YYYY-MM-DD') as StartDate

(this is DIRECTLY from the QlikVIEW documentation, though I'm working in Qlik Sense)

Date(Start, 'YYYY-MM-DD hh:mm:ss') as StartDate

Date(Date#(Start, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') as StartDate

Date(Left(Start, 10), 'YYYY-MM-DD) as StartDate

Various combinations on the above.

Some of them don't populate anything, some do, but none of them produce the intended result.

In addition, when I get a date and try the min(StartDate) function within a variable in the visualization (NOT load script) it doesn't give the correct date, while a simple KPI measure min(StartDate) does - which tells me I have formatting issues.

Again, it's very important that the date links/relates to all of the IDs/timestamps which occurred on that date

Help please? Ready, GO! (and thank you!)

topic Timestamp date extraction and formatting in App Development

Timestamp date extraction and formatting