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
withDATE("2020-07-04")
- Replace
date_timestamp endDate
withDATE("2021-08-15")
- Replace
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.