Issue with using AGO function in My Calculations

Recently I wanted to do a short demo on how to use Time Series functions in My Calculations in Oracle Analytics, more specifically how to create a new calculation using Ago function in Data Visualization workbook. Very straightforward, one would say, however, it is not. 

There is a known bug, quite well documented in Oracle Support, however, let me describe it briefly and provide a short workaround.

Let’s start with this simple report:

Now, let’s create a new calculation that uses AGO function, which has the following notation:

Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)

There is no secret about that, right? Well, there might be some …

As I mentioned, when you create a new calculation in Data Visualization workbook, you need to be careful when typing the <<Level>> part of the formula.

For example, if you follow the standard notation, then the formula should look like this:

Note: in the formula above I added extra space between “Time” and “Time Hierarchy”. This has been done deliberately as typed text is immideately replaced with text  Year.

When you try to validate the formula, the following error appears.

As I did, you might start questioning yourself about correctness of the formula and try to change it somehow. However, not much would work, unless you use the model name (in our case “OA Bootcamp Data Model"), which is automatically generated by the system (in our case “PC0"). 

But where an ordinary user could get the idea about the “technical” name of his data model, one might ask? Well, there are definitely several ways to do it. Let’s explore one quite handy option, let’s use Developer Reference, which has been updated in Oracle Analytics v6.3.

Begin with a new project and simply add a hierarchical column of the time dimension onto canvas.

Then, navigate to the menu on the top right corner and choose Developer menu item:

At the bottom part of the screen, Developer Reference opens. The first tab contains Performance Tools. Click Refresh to display the content if needed. On the right, click Logical SQL:


You can review the logical query, and the content we are looking for is the following:

Correct technical name for the <<Level>> is “PC0”.”Time”.”Time Hierarchy”.”Year”. Copy this string to Clipboard and paste it into formula in your calculation.

Note: in the formula above I added extra space between “Time” and “Time Hierarchy”. This has been done deliberately as typed text is immideately replaced with text  Year.

Copied string in the formula is automatically replaced with Year. AGO function can be validated …

… and finally, analysis works fine:

Comments

Popular posts from this blog

Oracle Analytics 5.9: Using Web Map Service (WMS) and Tiled Maps (XYZ)

OBI 12c Series: Connecting OBI with Hyperion Planning

Having issue with opening RPD file after you've downloaded a snapshot?