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 month, quarter, or 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:

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

Content

Signature