Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

complete newbie to QV - want my days to start at 6am not midnight

first thank you for having this forum!  QV is a completely new tool to me - no training no exposure all SSRS and Crystal previously so please forgive me if I am not following proper protocol or asking my questions correctly.  I have spent hours searching the QV help files, the internet and this forum as well as just "trying to figure it out" all to no avail.  Possibly I am not asking my question correctly - on to the point:

I have an application that grabs records from a shop floor database.  I have just about everything working the way I want it to but the times.  the users of this application will pick a day (or 2 or 3, etc) and will count a full day as 6am to 6am.  there are multitudes of entries per day (aka hundreds broken down by second) so I would like to for the user to be able to pick their days and the straight table to show either the option to select from single hours (not every hour from the output. .  ie 6/6/2014 4:00:00am,  6/6/2014 5:00:00am, etc instead of 6/6/2014 4:00:01am, 6/6/2014 4:02:00am.  so somewhat of a group by hour or rollup on the hours to eliminate clutter.  I would like the straight table to then output all the details between the selections.  I have a current selections box that seems ideal to this but when I launch this the dates are in order but the times are not so I cannot gather the appropriate 6am to 6am requirement.  any and all help is appreciated.

4 Replies
Gysbert_Wassenaar

Subtract 0.25 (=6/24) from the timestamps (aka datetimes) to get the correct day. Use the frac function to get the time part of the timestamp and the floor function to round down to hours:

Load

     ...some fields...,

     , floor(MyDate - 0.25) as MyDate

     , floor(frac(MyDate),1/24) as MyTime

From ...somewhere...;


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

You can use daystart() to define your day start at 6 am

Load

     Timestamp,

     daystart(Timestamp, 0, 0.25) as DayStart,

     Date(floor(daystart( Timestap, 0, 0.25)),'MM/DD/YYYY') as DayStartDate

     hour(Timestamp) as Hour,

     minute(TimeStamp) as Minute

From ...somewhere...;

Not applicable
Author

thank you both ever so much - i will implement these solutions and report back my results - much obliged!

Not applicable
Author

I used a little bit of a combo to accomplish the task – so far it seems to be working – again thank you both

LOAD STARTDATE,

daystart(STARTDATE, 0, 0.25) as DayStart,

dayend(STARTDATE, 0, 0.25) as DayEnd

FROM

C:\Users\COMPUTER\Desktop\DATAFILE.xlsx

(ooxml, embedded labels, table is data);