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.
You can add the toDate() formula column to the Measure tray only.
toDate(date exp, string datePart)
The following table illustrates the toDate() function parameters:
|date exp||A column of type Date or a formula that outputs date|
|string datePart||The |
timestamp representing the toDate function value based on the aggregation result.
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.
Use the following steps for detailed instructions on how to use the toDate() time series function to tackle the above example in an insight:
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,
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,
date expwith Sales Date from the Data panel
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.