Built-in Functions → ago

ago() is a time series analytic function that calculates the aggregated value from a specified date back to a specified period of time, such as a day, month, quarter, or a year ago. For example, you can use the ago() function to compare the current sales and the corresponding sales a month ago.

Note

You can add the ago() formula column to the Measure tray only.

Signature

ago(date exp, int rollBack, string datePart)

The following table illustrates the 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:
    •   "DAY"
    •   "MONTH"
    •   "QUARTER"
    •   "YEAR"

Returns

double, date, or timestamp representing the ago function value based on the aggregation result.

Example

Consider a scenario where you want to perform a Year-over-Year (YoY) revenue analysis. In this case, you must use an aggregation function and an ago() function to perform your calculation.

sum(
SALES.SALES.AMOUNT_SOLD,
ago(
SALES.SALES.TIME_ID,
1,
"Year"
)
)

Use the following steps for detailed instructions on how to use the ago() 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 Sales Date from the Data panel
      • Replace int rollBack with 1
      • Replace string datePart with "Year"
      sum(SALES.SALES.AMOUNT_SOLD, ago(SALES.SALES.TIME_ID, 1, "Year"))
    • Select Validate & Save.

  • In the New Formula Properties panel,

    • For Column Label, enter One year ago Revenue.
    • For Number Format, select Dollar Rounded.
  • In the Action bar, select Settings (gear icon).

  • In the General section, enable the Total toggle. Close the panel.

  • Name the insight YoY Revenue Analysis.

  • In the Action bar, select Save.