Before you Begin

This tutorial shows you how to create calculations using AGO, TODATE, and PERIODROLLING time series functions in Oracle Analytics workbooks without subject areas.

Background

Your workbook's dataset must contain a natural calendar period such as day, month, year, and your visualization must have a date column. Custom date ranges aren't supported by the time series functions in a workbook without a subject area.

What Do You Need?

Create a Dataset

In this section, you add the sample_order_lines2023.xlsx file to Oracle Analytics to create a dataset table.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines2023.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK. Click Save Save icon.
  5. In Save Dataset As, enter sample_order_lines, and then click OK.


    Description of sample_order_lines.png follows
    Description of the illustration sample_order_lines.png

Create a Visualization

In this section, you create a workbook from the dataset and a visualization with a time data element.

  1. Click Create Workbook.
  2. In the Data Data pane icon pane, expand Ship Date. Hold down the Ctrl key, select Sales and Month from Ship Date.
  3. Right-click, select Pick Visualization, and then select Table table vis icon.


    Description of all_sales.png follows
    Description of the illustration all_sales.png

Create an AGO Calculation

In this section, you create a time series expression using the AGO function.

  1. In the Data Data pane icon pane, right-click My Calculations and select Create Calculation.
  2. In Create Calculation, enter Sales Previous Month in Name.
  3. In the expression, enter AGO(Sales, Month, 1), click Validate, and then click Save.
  4. In My Calculations, select and drag Sales Previous Month to Rows in the Grammar pane and then release it under Sales.


    Oracle Analytics updates the table visualization with the calculation element.

    Description of sales_1_month_ago.png follows
    Description of the illustration sales_1_month_ago.png

Create a TODATE Calculation

In this section, you create a time series expression using the TODATE function.

  1. In the Data Data pane icon pane, right-click My Calculations and select Create Calculation.
  2. In Create Calculation, enter Sales YTD in Name.
  3. In the expression, enter TODATE(Sales, Year), click Validate, and then click Save.
  4. In My Calculations, select and drag YTD to Rows in the Grammar pane and then release it under Sales Previous Month.


    Description of sales_ytd.png follows
    Description of the illustration sales_ytd.png

Create a PERIODROLLING Calculation

In this section, you create a calculation using the PERIODROLLING function. For example, you can compute sales for a period that starts at a quarter before and ends at a specific number of quarter after the current quarter. You can use Month, Quarter, and Years as the time periods.

  1. In the Data Data pane icon pane, hold-down the Ctrl key, select Sales and Ship Date, and then drag them to the canvas.


    Oracle Analytics creates a line visualization with the data elements.

    Description of sales_by_shipdate.png follows
    Description of the illustration sales_by_shipdate.png
  2. Right-click Ship Date in Category (X-Axis), select Show By, and then select Quarter.


    Description of sales_shipdate_qtr.png follows
    Description of the illustration sales_shipdate_qtr.png
  3. In the Data Data pane icon pane, right-click My Calculations and select Create Calculation.
  4. In Create Calculation, enter Period Rolling Quarterly Sales in Name.
  5. In the expression field, enter Per and select PERIODROLLING.
  6. Click measure and click Sales under the sample_order_lines2023 folder.
  7. Enter -1 for the previous quarter in the first integer argument and enter 4 as the number of time periods for the calculation in the second integer.
  8. Drag Period Rolling Quarterly Sales to join Sales in Values (Y-Axis).


    The Period Rolling Quarterly Sales line represents the average sales over the quarters by ship date.

    Description of periodrolling_sales_qtr.png follows
    Description of the illustration periodrolling_sales_qtr.png

Learn More