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.
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:
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"
- Replace
- Replace
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.