DATE_STARTOF function

The Klipfolio DATE_STARTOF function retrieves the start date of a given unit relative to the given date.

The syntax for the DATE_STARTOF function is:

DATE_STARTOF( values , unit , relative value , first )

Note: If you specify the first value, you must also provide a relative value.

Parameters

values The data set number vector
unit

Options include:

week, month, quarter, year.

text scalar
relative value

Use the  "Insert literal string or number" button to type a relative value.  For example, if the unit value is month, a relative value of -1, returns the values for the previous month.

number scalar
first

Optional: Options include:

Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

January (default), February, March, April, May, June, July, August, September, October, November, December.

Note: The day of the week options for first only affect calculations when unit is 'day of week' or 'week'. The month options for first only affect calculations when unit is 'day of year' or 'quarter'.

text scalar

DATE_STARTOF example

Let's look at a DATE_STARTOF function example.

 

A

B

1 5/16/14 16
2 5/15/14 12
3 2/1/14 14
4 1/1/14 10

The following example is based on the above text in an Excel spreadsheet where today's date is 5/16/14:

SUM( SELECT( B:B , ( BETWEEN (DATE( A:A , "M/d/yy" ) , DATE_STARTOF( TODAY( ) , year ) , TODAY( ) ))))

-> ["52"]

Result: This formula sums the values from the beginning of the year until today's date.

Related links: