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.
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:
