Built-in Functions → dayOfYear

dayOfYear() is a conversion function that returns the count of days from the beginning of the year till a given date or timestamp, ranging from 1 to 366.

Signature

dayOfYear(date_timestamp exp)

The following table illustrates the dayOfYear() function parameter:

ParameterDescription
date_timestamp expThe date or timestamp value to determine the year’s day number from
Note

If an invalid date is entered, such as 02/30/2020 (leap year), the dayOfYear() function returns the number of the last valid day, 60.

Returns

int representing the count of days for a given date or timestamp.

Examples

  • Identify the day number of the year for the given date, 04/12/2021.
dayOfYear(
date(
"2021-04-12"
)
)

The dayOfYear() is 102.

  • Identify the day number of the year for the given timestamp, 02/06/2021 03:09:11.
dayOfYear(
timestamp(
"2021-02-06 03:09:11"
)
)

The dayOfYear() is 37.


Use the following steps for detailed instructions on how to use the dayOfYear() conversion function to tackle the first example above in an insight :

Note

In the Cluster Management Console (CMC), create a tenant that includes Sample Data. The Sample Data includes the SALES schema.

  • In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
  • In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
  • In the Action bar, select + (add icon), or select + Add Insight.
  • In the Insight panel, select Listing Table or V.
  • In Tables, select Aggregated Table.
  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, in Tables, select SALES. Close the panel.
  • From the Data panel, drag and drop the following columns to the respective tray:
    • Category from the PRODUCTS table to the Grouping Dimension tray.
    • Revenue from the SALES table to the Measure tray.
      • In the Properties panel, for Number Format, select Dollar Rounded.
  • From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
  • The Formula Builder automatically opens:
    • In Search Functions, select the down arrow, and then select Conversion Functions.
    • Double-click the dayOfYear function, dayOfYear(date_timestamp exp), to add the formula to the editor.
    • In the Formula Editor, replace date_timestamp exp with date("2021-04-12")
    • Select Validate & Save.
  • In the Properties panel, for Column Label, enter dayOfYear().
  • Name the insight Revenue Per Category.
  • In the Action bar, select Save.