Built-in Functions → toDate

toDate() is a time series analytic function that calculates the aggregated value from a specified start date to a specified period of time, such as a month, quarter, or a year. For example, you can use the toDate() function to accumulate the sales revenue from a year ago to the current date.

Note

You can add the toDate() formula column to the Measure tray only.

Signature

toDate(date exp, string datePart)

The following table illustrates the toDate() function parameters:

Parameter	Description
date exp	A column of type Date or a formula that outputs date
string datePart	The `string datePart` is a time dimension. The options are: ● `"MONTH"` ● `"QUARTER"` ● `"YEAR"`

Returns

double, date, or timestamp representing the toDate function value based on the aggregation result.

Example

Consider a scenario where you want to perform a Quarter-to-date (QTD) revenue analysis. In this case, you must use an aggregation function and a toDate() function to perform your calculation.

sum(
SALES.SALES.AMOUNT_SOLD,
toDate(
    SALES.SALES.TIME_ID,  
    "Quarter"
    )
)

Use the following steps for detailed instructions on how to use the toDate() time series function to tackle the above example in an insight:

Note

In the Cluster Management Console (CMC), you can 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 two Sales Date columns from the SALES table to the Grouping Dimension tray.
- In the Properties panel of the first Sales Date, for Date Part, select Quarter.
- In the Properties panel of the second Sales Date, for Date Part, select Month.
From the Data panel, drag and drop 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 Measure tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Aggregation Functions.
- Double-click the second sum function, sum(double[] exp), to add the formula to the editor.
- In the Formula Editor,
  - Replace double[] exp, ... with Revenue from the Data panel, and then add a comma.
  - In Search Functions, select the down arrow, and then select Analytic Functions.
  - Double-click the toDate function, toDate(date exp, string datePart). In the Formula Editor,
    - Replace date exp with Sales Date from the Data panel
    - Replace string datePart with "Quarter"

sum(SALES.SALES.AMOUNT_SOLD, toDate(SALES.SALES.TIME_ID, "Quarter"))

Select Validate & Save.
In the New Formula Properties panel,
- For Column Label, enter QTD Revenue.
- For Number Format, select Dollar Rounded.
Name the insight QTD Revenue Analysis.
In the Action bar, select Save.

Content

Signature