Built-in Functions → monthsBetween

monthsBetween() is a conversion function that returns a double for the number of months between two dates or timestamps.

Signature

monthsBetween(date_timestamp startDate, date_timestamp endDate)

The following table illustrates the monthsBetween function parameters:

Parameter Description
date_timestamp startDate The date or timestamp value to determine the start date from
date_timestamp endDate The date or timestamp value to determine the end date from

Returns

double representing the number of months.

Examples

  • Identify the number of months between the two given dates, “2020-07-04” and “2021-08-15”.

    monthsBetween(
        DATE(
            "2020-07-04"
        ),
        DATE(
            "2021-08-15"
        )
    )
  • Identify the number of months between the two given timestamps, “2020-12-01 11:24:15” and “2021-03-21 16:30:02”.

    monthsBetween(
        TIMESTAMP(
           "2020-12-01 11:24:15"
        ),
        TIMESTAMP(
           "2021-03-21 16:30:02"
        )
    )

The following table illustrates the behavior of the monthsBetween() conversion function:

Product Revenues monthsBetween(date) monthsBetween(timestamp)
CD $200 13.354 3.645
Hard disk $600 13.354 3.645
Cable $550 13.354 3.645
Hardware $800 13.354 3.645
Memory Card $220 13.354 3.645

Use the following steps for detailed instructions on how to use the monthsBetween() 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.
  • 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 monthsBetween function, monthsBetween(date_timestamp startDate, date_timestamp endDate), to add the formula to the editor.
    • In the Formula Editor,

      • Replace date_timestamp startDate with DATE("2020-07-04")
      • Replace date_timestamp endDate with DATE("2021-08-15")
      monthsBetween(DATE("2020-07-04"), DATE("2021-08-15"))
    • Select Validate & Save.
  • In the Properties panel, for Column Label, enter monthsBetween().
  • Name the insight Revenue Per Category.
  • In the Action bar, select Save.