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.

Content

Signature

Returns

Examples