Calculating a unique Month start time during load of SQL date field

[germanboxers]

I'm new to Qlik and would appreciate your insights.  Our days, weeks, months, quarters, and year all start at 9am for the given period, not midnight.  I would like to create a new field during loading of sql data that captures this.

For example:

actual_ship_date          MonthStartShipDate

10/1/17 8:59:59 AM      9/1/17

10/1/17 9:01:00 AM     10/1/17       

In VBA, I used the following function to return my desired date:

Function StartMonth(TurnDate As Date) As Date

'-------Function which returns the start of month ------------------------------

Dim Hours As Integer, MonthStart As Date, DayOfMonth As Integer, DateStr As String

Hours = Hour(TurnDate)

If Hours >= 0 And Hours < 9 Then

   TurnDate = TurnDate - 1

End If

DayOfMonth = DatePart("d", TurnDate)

MonthStart = TurnDate - DayOfMonth + 1

DateStr = Str(DateSerial(Year(MonthStart), Month(MonthStart), Day(MonthStart)))

StartMonth = CDate(DateStr)

End Function

I tried the following script in Qlik Sense, but it doesn't like it.  Any suggestions would be greatly appreciated as I also have some algorithms that I need to deploy in Qlik to also represent which crew is on shift (4 on, 4 off, 9am - 9pm, 9pm - 9am).

Set shours = hour($1);

IF (shours >=0) And (shours <9) Then

Set Turndate = $1-1;

End If;

Let Mstart = monthstart(Turndate,0);

Load ($Mstart(actual_ship_date,0);

Thanks,

Jordan

[eduardo_dimperio]

Hi Jordan,

Maybe this:

LOAD

*,

If (Hour(TurnDate ) >9) ,(TurnDate+1),TurnDate )AS DATE

FROM YOURBASE;

//After that you can break date as you need ,like hour,day,month etc

[Clever_Anjos]

Please try with this

load

Month(yourfield + 9/24) as Month // Adding 9 hours

from your base

[NZFei · Accepted solution]

Load

     *,

     Monthstart(if(hour(actual_ship_date)<9,actual_ship_date-1,actual_ship_date)) as MonthStartShipDate

from your base;

[germanboxers]

This worked perfectly.  Thank you very much!

Jordan