4 Replies Latest reply: Oct 12, 2017 9:12 AM by Jordan Breiner RSS

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

    Jordan Breiner

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