Built-in Functions → monthWeek
monthWeek() is a conversion function that returns the week number of the month for a given date or timestamp, where a week starts on Sunday and ends on Saturday.
To return the month’s week number for the current date, use the monthWeek() conversion function without a parameter.
The following table illustrates the monthWeek() function parameter:
int representing the month’s week number for a given date or timestamp.
- Identify the week number of the month for the given date, 04/24/2020.
monthWeek( date( "2020-04-24" ) )
April 24th is in the fourth week of the month. Thus, the monthWeek() is 4.
- Identify the week number of the month for the given timestamp, 02/06/2021 03:09:11.
monthWeek( timestamp( "2021-02-06 03:09:11" ) )
February 6th is in the first week of the month. Thus, the monthWeek() is 1.
The following table illustrates the behavior of the monthWeek() conversion function:
Use the following steps for detailed instructions on how to use the monthWeek() conversion function to tackle the first example above in an insight :
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 first monthWeek function,
monthWeek(date_timestamp exp), to add the formula to the editor.
- In the Formula Editor, replace
- Select Validate & Save.
- In the Properties panel, for Column Label, enter monthWeek().
- Name the insight Revenue Per Category.
- In the Action bar, select Save.