Time Travel Made Easy: Working with Time Series Functions in Oracle Analytics

March 19, 2024 | 5 minute read
Luis Rivas
Director of Product Management - Oracle Analytics
Text Size 100%:

Time series functions are widely used in analytics for trend analysis, year-over-year comparisons, seasonal analysis, forecasting, and more. Whether it's uncovering seasonal variations in sales data or forecasting future trends, understanding how to use time series functions such as AGO and TODATE is indispensable for any data analyst.

So let’s get started with some example uses of the AGO function. The AGO function calculates the aggregated value of a measure in a specified time period in the past based on some parameters that need to be supplied by the user when creating the desired calculations.  For example, consider a scenario where we're tasked with analyzing sales data for a retail company over the past few years to identify year-over-year growth or decline.  To create a year-over-year analysis using the AGO function, we need the report to provide the year, sales that year, sales the prior year, and the growth or decline calculated by the difference between the current year and the previous year’s sales. 

Let’s start by creating a calculation and typing AGO(Sales, Year, 1) – In this example:

  • “Sales” is the measure to report on in our dataset.
  • “Year” is the time interval, which must be Year, Quarter, Month, Week, or Day.
  • “1” is the Offset: the number of time intervals to calculate back to, which in our case, is going back one year. 

Let's name this calculation Year Ago Sales.

Create Year Ago Sales Calc


Now let's create a slightly more complex calculation to show the year-over-year growth or decline. We take the sum of the current year’s sales and subtract the prior year’s sales. 

Let’s create another calculation by typing in Sales – AGO(Sales, Year, 1) which does exactly what we need. Let's name this calculation YEAR OVER YEAR DIFF.


Create Year Over Year Sales Calc


Now let’s create the visualization. The time series functions work with the date or datetime column specified in the visualization. There's no need to specify different ones for each date or date level in the dataset. For this visualization, let's select the Year level from the ORDER_DATE column, the SALES column to get the aggregated sales for each of the years, and the two new columns we created earlier (the YEAR AGO SALES that provides the sales of the prior year in the visualization and the YEAR OVER YEAR DIFF column that provides the difference between the current year and prior year sales.  Notice how easy it was to create a very interesting YOY Sales visualization.

Note: Because the system uses the date in the visualization to calculate the prior year sales, there must only be one date time column in the visualization or the system displays an error message indicating that it can’t determine which date to use.


Year Over Year Sales Viz


Additionally, we can change this table to a pivot table and add dimensions to make the report even more insightful. Let's add a percent growth/decline section and conditional formatting to help us find any product categories that might be having issues. Let's also remove the year 2024 using a filter, because it's a partial year and would produce erroneous results. We could address this by doing a Month-over-Month comparison of only the year 2024.


Year Over Year Sales Viz


Now that we've created an interesting year-over-year analysis of our sales for each of our product categories, let’s analyze how each year's sales progress month over month. Let's use the TODATE function to do that. The TODATE function calculates the aggregated value of a measure from the start of a time period to the current date.  For example, you can create a report for monthly sales and add a column to track year-to-date sales. 

Let’s create a calculation by typing TODATE(Sales, Year) – In this example:

  • “Sales” is the measure in the dataset that we want to report on.
  • “Year” is the time period that we want to keep a running sum for; the function supports Year, Quarter, Month, Week, or Day. 

Let's name this calculation YEAR TO DATE SALES.

Year to Date Sales


Now that we have our YEAR TO DATE SALES calculation, let's create a simple visualization of month-to-month sales with a year-to-date column. For this visualization, let's use the Month grain of ORDER_DATE, the SALES measure column, and the newly created YEAR TO DATE calculation.  Notice that the YEAR TO DATE calculation keeps a running total starting in January through December and starts again in the following year for January.


Create Year to Date Sales


And finally, these examples are based on self-service datasets. These functions are also supported in subject areas. There's a slight difference when working with subject areas that you should be aware of. When defining expressions such as AGO(Sales, Year, 1), you must drag the level from the available time hierarchies in the subject area. In this example, the expression is identical, but we needed to drag Year from the hierarchy tree. The cool thing about subject area time hierarchies is that we can pick the highest level for the visualization and then we can drill down to the lower levels and the time series calculations dynamically adjust to those lower levels automatically.


Create YEAR AGO for Subject Area


Call to Action

We hope you've enjoyed diving into the world of time series analysis with us! We challenge you to start creating time series analysis reports with these two functions and hope that you find them to be both powerful and user-friendly. Keep exploring these functions to uncover deeper insights in your data and stay tuned for our upcoming posts, where we'll share more tips and tricks on both new and existing features of our product!

Luis Rivas

Director of Product Management - Oracle Analytics

Previous Post

Disaster Recovery for Oracle Analytics Server on Oracle Cloud Using RCU Schemas Replication and File System Replication with Rsync

Next Post

Oracle Analytics Server is Available on Oracle Cloud Marketplace

Adrienne Howard | 4 min read