0 Replies Latest reply: Feb 6, 2018 4:51 PM by Joey Lutes RSS

    Timestamp date extraction and formatting

    Joey Lutes

      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!)