Built-in Functions → toDate with ago
toDate() with ago() is a combined time series analytic function that first calculates the ago() function value, which is the aggregated value of a measure from a specified start date (e.g. 10/01/2021) back to a specified period of time, such as a month, quarter, or year ago (e.g. a quarter ago, 07/01/2021), and then calculates the toDate function value, which is the aggregated value from the previously specified start date (e.g. 10/01/2021) to a specified period of time, such as a month, quarter, or year later (e.g. a month later, 11/01/2021). The output of the toDate() with ago() function is the summation of the aggregated values (e.g. from 07/01/2021 till 11/01/2021).
You can add the toDate() with ago() formula column to the Measure tray only.
Signature
ago( toDate( date exp, string datePart), int rollBack, string datePart )
The following table illustrates the combined toDate() with ago() function parameters:
Parameter | Description |
---|---|
date exp | A column of type Date or a formula that outputs date |
int rollBack | The integer value to roll back from datePart . A negative value adds up to datePart . For example, if the value of string datePart is "MONTH" , then the value of int rollBack can be as follows: ● 1 representing the last month ● -1 representing the next month |
string datePart | The string datePart is a time dimension. The options are: ● "MONTH" ● "QUARTER" ● `"YEAR" |
Returns
double
, date
, or timestamp
Example
Consider a scenario where you want to calculate the accumulated sales revenue of a year and the year before. In this case, you must use an aggregation function, toDate () function, and an ago() function to perform your calculation.
sum(SALES.SALES.AMOUNT_SOLD,ago(toDate(SALES.SALES.TIME_ID,"Year"),1,"Year"))
Use the following steps for detailed instructions on how to use the todate() with ago() combined 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 the following columns from the SALES table to the respective tray:
- Sales Date to the Grouping Dimension tray
- In the Properties panel, for Date Part, select Year.
- Revenue to the Measure tray
- In the Properties panel, for Number Format, select Dollar Rounded.
- Sales Date to the Grouping Dimension tray
- 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 ago function,
ago(date exp, int rollBack, string datePart)
.
In the Formula Editor,- Replace
date exp
with toDate,toDate(date exp, string datePart
, from the Functions panel - Edit the toDate formula
- Replace
date exp
with Sales Date from the Data panel - Replace
string datePart
with"Year"
- Replace
- Replace
int rollBack
with1
- Replace
string datePart
with"Year"
- Replace
- Replace
sum(SALES.SALES.AMOUNT_SOLD, ago(toDate(SALES.SALES.TIME_ID,"Year"),1, "Year")
- Select Validate & Save.
- In the New Formula Properties panel,
- For Column Label, enter Yago with YTD.
- For Number Format, select Dollar Rounded.
- Name the insight Revenue: Year ago with year toDate.
- In the Action bar, select Save.