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.
You can add the ago() formula column to the Measure tray only.
ago(date exp, int rollBack, string datePart)
The following table illustrates the ago() function parameters:
|date exp||A column of type Date or a formula that outputs date|
|int rollBack||The integer value to roll back from
For example, if the value of
timestamp representing the ago function value based on the aggregation result.
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:
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,
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,
date expwith Sales Date from the Data panel
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.