| Excerpt |
|---|
Returns the serial date number for the last day of the month before or after a specified number of months from a starting date. |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:eomonth(myDate, 3) |
|---|
|
eomonth(myDate, 3) |
Output: Returns the values for the last date of the month that is three months after the serial date number value in the myDate column.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:eomonth(start_date,delta_months) |
|---|
|
eomonth(start_date,delta_months) |
| Argument | Required? | Data Type | Description |
|---|
| start_date | Y | Datetime | Starting date. |
| delta_months | Y | integer | Number of months before or after the starting date to apply to the date value. |
start_date
Starting date values from which to compute end-of-month values.
| Required? | Data Type | Example Value |
|---|
| Yes | Literal Datetime or column reference | LastContactDate |
delta_months
Number of months to add to the date value to determine the month whose last date is returned.
- Negative integer values are accepted.
| Required? | Data Type | Example Value |
|---|
| Yes | Integer | -3 |
Example - EOMONTH Function
Source:
In the following table, you can see how the the end-of-month values are calculated from a baseline date of June 15, 2020.
| eventId | eventDate | deltaMonth |
|---|
| 1 | 06/15/2020 | -24 |
| 2 | 06/15/2020 | -18 |
| 3 | 06/15/2020 | -12 |
| 4 | 06/15/2020 | -6 |
| 5 | 06/15/2020 | -3 |
| 6 | 06/15/2020 | -2 |
| 7 | 06/15/2020 | -1 |
| 8 | 06/15/2020 | 0 |
| 9 | 06/15/2020 | 1 |
| 10 | 06/15/2020 | 2 |
| 11 | 06/15/2020 | 3 |
| 12 | 06/15/2020 | 6 |
| 13 | 06/15/2020 | 12 |
| 14 | 06/15/2020 | 18 |
| 15 | 06/15/2020 | 24 |
Transformation:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'deltaMonthSerialDate' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:eomonth(eventDate,deltaMonth) as:'deltaMonthSerialDate' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | eomonth(eventDate,deltaMonth) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Results:
| eventId | eventDate | deltaMonth | deltaMonthSerialDate |
|---|
| 1 | 06/15/2020 | -24 | 43281 |
| 2 | 06/15/2020 | -18 | 43465 |
| 3 | 06/15/2020 | -12 | 43646 |
| 4 | 06/15/2020 | -6 | 43830 |
| 5 | 06/15/2020 | -3 | 43921 |
| 6 | 06/15/2020 | -2 | 43951 |
| 7 | 06/15/2020 | -1 | 43982 |
| 8 | 06/15/2020 | 0 | 44012 |
| 9 | 06/15/2020 | 1 | 44043 |
| 10 | 06/15/2020 | 2 | 44074 |
| 11 | 06/15/2020 | 3 | 44104 |
| 12 | 06/15/2020 | 6 | 44196 |
| 13 | 06/15/2020 | 12 | 44377 |
| 14 | 06/15/2020 | 18 | 44561 |
| 15 | 06/15/2020 | 24 | 44742 |
