Built-in Functions → weekEndDate

About weekEndDate function

weekEndDate() is a scalar date function that returns the date of the last day of the current week.

Note

A week ends on Saturday and starts on Sunday.

Signature

weekEndDate()

Returns

Date representing the last date of the current week.

Example

Consider the following table as a sample data:

Date        Quarter    Month    Sales Amount 

5/22/2021    Q2         M5       100
5/21/2021    Q2         M5       200
5/23/2021    Q2         M5       100
4/22/2021    Q2         M4       150
5/20/2021    Q2         M5       250
6/22/2021    Q2         M6       150
3/22/2021    Q1         M3       200
5/10/2021    Q2         M5       250 
5/01/2021    Q2         M5       300 
5/09/2021    Q2         M5       100 
5/30/2021    Q2         M5       50 
2/22/2021    Q1         M2       50

Consider a case where you want to identify the end of the current week and the sales amount that occurred before that date, for example, the current week is 5/22/2021.

  • End date of the current week

    weekEndDate()
  • Sales prior to week end date

    SALES_ACME.SALES.TIME_ID < weekEndDate()

This example returns all Sales amount that occurred before the end of the current week.

Quarter Date Sales Amount
Q2 5/21/2021 200
Q2 4/22/2021 150
Q2 5/20/2021 250
Q1 3/22/2021 200
Q2 5/10/2021 250
Q2 5/01/2021 300
Q2 5/09/2021 100
Q1 2/22/2021 50

Use the following steps for detailed instructions on how to use the weekEndDate() date function:

Note

The sample data is uploaded, and then added and loaded into a Schema named SALES_Acme. For more information on creating a data source and a schema, refer to the Guides→Start document.

  • 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 Data panel, select Add Data Set (+).
  • In the Manage Data Sets panel, in Tables, select SALES_Acme. Close the panel.
  • From the Data panel, drag and drop the following columns to the respective tray:

    • From the SALES table, drag and drop Quarter to the Grouping Dimension tray.
    • From the SALES table, drag and drop Sales Amount and Date to the Measure tray.
    • In the Properties panel for Sales Amount:

      • For Format, select Dollar Rounded.
  • From the Data panel, drag and drop Add Formula to the Individual Filter tray. The Formula Builder automatically opens:

    • In Search Functions, select the down arrow, and then select Date Functions.
    • Double-click the weekEndDate function, weekEndDate(), to add the formula to the editor.
    • In the Formula Editor, use the following condition:

      • SALES_ACME.SALES.TIME_ID < weekEndDate()
    • Select Validate & Save.
  • In the Measure tray, double-click the New Formula pill and rename it to weekEndDate().
  • Name the insight Sales per Quarter Before Current Week End Date.
  • In the Action bar, select Save.