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).

Note

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:

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 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.
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 int rollBack with 1
    - Replace string datePart with "Year"

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.

Content

Signature

Returns

Example