AGO function error

Cameron Loepker

Hello Board,

I am on OBIEE version : Oracle Business Intelligence 12.2.1.3.0

I am working on implementing a time series calculation on a new report.

So far - have successfully implemented the AGO function on another report with no issues. everything is working fine and was able to use that column to create a YTD difference field....  when i move to another subject area and implement the time hierarchy, then go to the analysis and use the AGO function, am getting an error saying:

To use Ago function, the storage level of the query ('[A01_DW_TIME_VIEW_RPS_ADD_DT.DIMENSION_KEYmust be a static level. (HY000)

I am joining the time dimension alias in the exact same way that its joined in the SA where it works properly and also set up the parent child in the exact same way with same keys.

any ideas?

thanks,

Cameron

Joel

Hi

Do you have all the levels in the new date dimension? It appears that the logical levels of granularity of the dimension and the AGO function aren't the same.

Take a look at this:

OBIEE: [nQSError: 22040] To use AGO function, the storage level of the query ('[time1.time_keymust be a static level…

Cameron Loepker

Hi Joel,

Thanks for the response.

Looking at this article it has a solution as:

Solution : Just include one more logical level for the Time dimension hierarchy and add the date_value(or a column which holds granularity for the row.) as a logical key.

I have included a day logical level, and this exact setup has been used in another SA successfully. I wish the error OBIEE gave was more specific.......

Joel

OK, so what's the syntax of your AGO function?

Cameron Loepker

I have it as :

AGO("DW_RETURN_TRACKING_F"."DOC_LOC_NBR_CNT",  1)

which does not throw an error when inputting the formula... played around with it a couple ways and every other way but this one threw an error inside the actual edit formula form.......

Joel

The syntax should be:

AGO(Measure, Level, Number of Periods to offset)

The Level is optional but can be used to override the grain of query in your analysis. Try adding the level in your AGO function.

Cameron Loepker

I am not exactly sure what happened but without changing the formula, now i am getting data return... except only problem now is, it is returning the same years total, and not the year before.

when i try to add the time level in i get the following error inside the edit formula form :

[Deleted User]

Are you using "Year" as in a column? It needs to be a hierarchical level.

Cameron Loepker

That's the name of the hierarchical level. Column name for year (in our case) is CAL_YEAR_NUMBER

Joel

You need to reference the presentation layer name of the hierarchical level in the AGO function. Quick trick is to right click on it in the rod  copy and paste into a text editor and you should see it’s fully qualified name in the XML.

Cameron Loepker

so i had read an article saying that if the time column is being used in the analysis, then it is not needed in the forumla calc. this was the case in the instance that I got this to work on another analysis.

also i am now past the error when analysis loads, but it is showing the same total as current year, and is not looking back a year. in the instance where I get there to work in the other analysis i noticed i am looking back on a dollar amount, where in this case we are just looking at a count.. not sure if that would make a difference or not.