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

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

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

Thanks,

Jordan

• ###### Re: Calculating a unique Month start time during load of SQL date field

Hi Jordan,

Maybe this:

*,

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

• ###### Re: Calculating a unique Month start time during load of SQL date field

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

• ###### Re: Calculating a unique Month start time during load of SQL date field

*,

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

• ###### Re: Calculating a unique Month start time during load of SQL date field

This worked perfectly.  Thank you very much!

Jordan