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:

ParameterDescription
date expA column of type Date or a formula that outputs date
int rollBackThe 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 datePartThe 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.