Using dates in functions

Follow

In BIME, you can use date functions to create, compare, and perform calculations on dates. This article lists all the available date functions and provides examples of some common use cases. For information on using other function types, see Formula writing resources.

This article contains the following sections:

Available date functions

This section will list all available date functions. In the Functions window, you can view only date functions by clicking the Filter button, then selecting the Date category.

Date functions require you to use pre-existing dates in a parameter. You can use your date attribute, manually entered dates, or other date functions. The examples in this article will demonstrate how you can use other date functions in the date parameter to create dynamic calculations (see Writing formulas with date functions).

You can use date functions for the following two purposes:

Note: All date functions can be used in the date parameter.

Returning a new date or date range

You can use date functions to add a calculated attribute containing a new date or date range. The table below describes all functions you can use to return a new date or date range.

Table 1. Functions for returning new dates
Function Description
DATE (year, month, day, hour, minute, second) Returns a new date that holds the entered parameters, all of which must be numbers, including metrics. At least year, month, and day are needed.
YEAR (date) Returns the year of the entered date.
MONTH (date) Returns the month of the entered date.
HOURS (date) Returns the hour of the entered date.
MINUTES (date) Returns the minute of the entered date.
SECONDS (date)

Returns the second of the entered date.

DATE_FROM_TIMESTAMP Returns a date from the entered timestamp in seconds
DATE_FROM_MILLI_TIMESTAMP Returns a date from the entered timestamp in milliseconds
DATE_TO_TIMESTAMP Returns a timestamp from the entered date.
DATE_TO_MILLI_TIMESTAMP Returns a timestamp in milliseconds from the entered date.
ND_OF_MONTH(date) Returns the end date in the month of the entered date.
START_OF_MONTH(date) Returns the first date in the month of the entered date
START_OF_QUARTER(date) Returns the first date in the quarter for the entered date.
END_OF_QUARTER(date) Returns the end data in the quarter for the entered date.
START_OF_WEEK_ISO(date) Returns the first date in the week for the entered date.
END_OF_WEEK_ISO(date) Returns the end date in the week for the entered date.
START_OF_WEEK_MONDAY(date) Returns the first date of the week for the entered date, with the week starting on Monday.
END_OF_WEEK_MONDAY(date) Returns the end date of the week for the entered date, with the week starting on Monday.
START_OF_WEEK_FRIDAY(date) Returns the first date of the week for the entered date, with the week starting on Friday.
END_OF_WEEK_FRIDAY(date) Returns the end date of the week for the entered date, with the week starting on Friday.
START_OF_WEEK_SATURDAY(date) Returns the start date of the week for the entered date, with the week starting on Saturday.
END_OF_WEEK_SATURDAY(date) Returns the end date of the week for the entered date, with the week starting on Saturday.
START_OF_WEEK_SUNDAY(date) Returns the start date of the week for the entered date, with the week starting on Sunday.
END_OF_WEEK_SUNDAY(date) Returns the end date of the week for the entered date, with the week starting on Sunday.
TODAY() Returns the current date.
NOW() Returns the current GMT date and time.
CURRENT_YEAR() Returns the current year.
CURRENT_HALFYEAR() Returns the current semester as text (H1 or H2).
CURRENT_QUARTER() Returns the current quarter as text ( Q1, Q2, Q3, Q4).
CURRENT_MONTH() Returns the current month as text (January, February, etc).
CURRENT_MONTH_NUMERIC() Returns the current month as a number (1 for January, 2 for February, etc).
CURRENT_DAY() Returns the current day as a number within the month (1-31).
CURRENT_WEEKDAY() Returns the current weekday as text (Monday, Tuesday, etc).
CURRENT_WEEKDAY_NUMERIC() Returns the current weekday as a number (0 for Sunday, 1 for Monday, etc).
CURRENT_HOUR() Returns the current hour as a number.
CURRENT_MINUTE() Returns the current minute as a number.
CURRENT_SECOND() Returns the current second as a number.
FISCAL_YEAR(date, start month) Returns the fiscal year according to the fiscal time period defined in the Data structure menu.
FISCAL_QUARTER(date, start month) Returns the fiscal quarter according to the fiscal time period defined in the Data structure menu.
FISCAL_WEEK_NUMBER(date, start month) Returns the fiscal week number according to the fiscal time period defined in the Data structure menu.

Performing calculations on dates or date ranges

You can also use date functions to perform calculations on dates. These calculations can reformat dates, compare dates, or perform mathematical operations on dates.

Table 2. Functions for performing calculations on dates
Function Description
DATE (Text) Returns a new date that holds the entered text. This must include the month, day, and year.
MONTH_NUMERIC (date) Returns the month of the entered date as a number (1 for January, 2 for February, etc).
WEEK_NUMBER(date, offset) Returns the week number of the entered date, according to the entered offset. The entered offset specifies the day of the week your week starts. For example, weeks starting on Sunday have an offset of 0.
WEEK_NUMBER_ISO(date) Returns the correct week number of the entered date.
WEEK_NUMBER_MONDAY(date) Returns the week number of the entered date, with the week starting on Monday
WEEK_NUMBER_FRIDAY(date) Returns the week number of the entered date, with the week starting on Friday.
WEEK_NUMBER_SATURDAY(date) Returns the week number of the entered date, with the week starting on Saturday.
WEEK_NUMBER_SUNDAY(date) Returns the week number of the entered date, with the week starting on Sunday.
WEEKDAY(date) Returns the day of the entered date as text (Monday, Tuesday, etc).
WEEKDAY_NUMERIC(date) Returns the day of the entered day as a number (0 for Sunday, 1 for Monday, etc).
DAY(date) Returns the day of the entered date as a number.
TO_MONTH_NUMERIC(text) Returns the numeric value of the entered month (01 for January, 02 for February, etc). Returns NA if the entered parameter does not match with any month.
TO_MONTH_TEXT(text) Returns the text value of the entered month (January for 01, February for 02, etc). Returns NaN if the entered number does not match any month.
SECONDS_TO_TIME(time) Converts seconds to HH:MM:SS format
DATE_EQUAL(date, date) Returns TRUE if the two entered dates are equal.
DATE_NOT_EQUAL(date, date) Returns TRUE if the two entered dates are different.
DATE_LESS(date, date) Returns TRUE if the first entered date is from before the second entered date.
DATE_LESS_OR_EQUAL(date, date) Returns TRUE if the first entered date is before or equal to the second entered date.
DATE_GREATER(date, date) Returns TRUE if the first entered date is more recent than the second entered date.
DATE_GREATER_OR_EQUAL(date, date)

Returns TRUE if the first entered date is more recent or equal to the second entered date.

DATE_FORMAT Returns the entered date in the entered format.
DATE_ADD(date, date_part, increment) Returns the given date, updated by the entered increment. The date_part parameter could be for example, 'year', 'quarter', 'month', 'day', 'hour', etc. The entered increment must be an integer value, but it can be negative.
DATE_DIFF(date, date, date_part) Returns the difference between the two entered dates, calculated on the entered date_part. To compute relative difference, the date part should be 'year', 'month', 'day', etc.
DATE_DIFF([Metric date],[Metric date],"nb_of_days") To compute absolute difference, enter the different metrics dates then "nb_of_days", "nb_of_months", or "nb_of_years".

Writing formulas with date functions

This section will provide some examples of how you can use date functions in your reports. These examples utilize both types of date functions to create dynamic custom metrics.

Calculating the number of days between two dates

You can calculate the difference between two days, years, quarters, half years, hours, minutes, or seconds using the DATE_DIFF function. This example uses the DATE_DIFF function to calculate the number of days between when a ticket was created to the current date.

To calculate the difference between the created date and today
  1. In the Calculation engine menu, select Standard calculated metric.
  2. Name your metric. This example uses Days since created date.
  3. Click on the +Add button under Functions.
  4. Select the DATE_DIFF function.

  5. In the first _date parameter, enter the older date. You have three options for entering your date:
    • Type in a specific date, using month/day/year format.
    • Select a date attribute from the Select a field drop-down list. This example uses the date attribute Date(ticket_created_date)
    • Select a date function.
  1. For the second _date parameter, enter the more recent date. This example uses the TODAY() function to ensure the calculation is always using the current date.
  2. In the _date_part select the part of the date you want to subtract in "double quotes". This example is measuring the number of days, so "day" is entered.

  3. Click Save.
  4. Click the + button on Metrics to add your calculated metric to your query.

Displaying results from a previous time period

You can use the DATE_ADD function to display your previous week, month, or year results. This example uses DATE_ADD to compare revenue at the weekly level. You can use this formula to compare any date range by selecting a different date function as your reference point. Depending on your data source, this formula might vary.

To compare the previous week to today

  1. In the Calculation engine menu, select Standard calculated metric.
  2. Enter a name for your calculated metric. This example uses Tickets previous week.
  3. Click the +Add button under Functions.
  4. Select the IF THEN ELSE function.
  5. Click inside of the _boolean_condition parentheses.
  6. Select your date attribute from the Select a field drop-down list or type in the attribute name. This example uses the attribute WEEK(Date).
    Note: If you manually type in the attribute, you must either select BIME's auto-complete suggestion or replicate the exact format. Calculations will not process incorrect formatting.

  7. After the closing bracket, enter an equal sign.
  8. Click the +Add button under Functions.
  9. Select the function for the date level you would like to use. This example uses the WEEK_NUMBER_ISO function to set results at a weekly level.
    Note: If your query is from a on-premise data source, use the WEEK_NUMBER function instead.
  10. Click the +Add button under Functions.
  11. Click the + button next to the DATE_ADD function.

  12. In the _date parameter, enter the date you would like to compare to previous results. This example compares previous results to the current date, so the function TODAY() is selected.
  13. For _date_part, enter the portion of the date you would like to increase or decrease. Your _date_part must be entered in "double quotes". This example uses the date part "day".

    https://zen-marketing-documentation.s3.amazonaws.com/docs/en/dateadd_3.png

  14. For the _increment, enter an integer to compare how far back in the past or ahead in the future the returned date will be. This example compares the previous week with the date_part "days", so -7 is entered.
  15. If you're using the WEEK_NUMBER function instead of WEEK_NUMBER_ISO, enter the CURRENT_WEEKDAY_NUMERIC function as the offset, so the start date is consistent between weeks.
  16. For the _value_if_true select your metric from the Select a field drop-down list or type the metric name. This example uses SUM(Revenue).
  17. Delete ELSE and _value_if_false.

  18. Click Save.

If you want to compare your results to the current week, you can either create a date range calculate metric or create another standard calculated metric with the following formula:

IF ([WEEK(DATE)=WEEK_NUMBER_ISO(TODAY())) THEN SUM(Tickets) ENDIF
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk