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.
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:
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.
- Sales Date to the Grouping Dimension tray
- 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
with1
- Replace
string datePart
with"Year"
sum(SALES.SALES.AMOUNT_SOLD, ago(SALES.SALES.TIME_ID, 1, "Year"))- Replace
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.