Ago function

Luigi_Gif · Oct 30, 2017 7:15AM

Dear all,

I am having a problem with Ago funtion application. I created a time dimension and I am interested to have all value YTD of the last year. the formula I am using is:

AGO("Area Sales"."Vendite"."Order Scenario Actual" , "Area Sales"."Schedule Ship Period"."Year" ,1)

It works if the filter is month, while if I try with week, I have no values, or maybe....values are not correct

Does anyone have an advice ?

Thanks

Luigi

Joel · Oct 30, 2017 7:18AM

Can you provide information about your OBIEE version? Also, have you checked the query log files to see what query has been generated by the BI server? Screenshots are always useful to enable people to properly visualize your issue.

asim cholas · Oct 30, 2017 7:24AM

Do you have a proper key for your week in time dimension? Are you using the week column from time dimension in report?

Robert Angel · Oct 30, 2017 7:46AM

Check that you have chronological keys defined at each level of the time dimension hierarchy.

Check that the underlying key for each level is unique at that level

e.g.

Year 2015

Quarter 201504

Month 201510

Week 201548

Date 20151201

Note your keys can be surrogates, the important factor is that they are each unique at that level of the hierarchy and ordered the same as the underlying data

Luigi_Gif · Oct 30, 2017 9:54AM

Hi Joel,

Oracle Business Intelligence Product Version 11.1.1.9.160719

while the error is:

Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27045] Nonexistent column: "Schedule Ship Period"."CAL_WEEK_DESC". (HY000)

SQL Issued: {call NQSGetQueryColumnInfo('SET VARIABLE LOGLEVEL=7; SELECT "Schedule Ship Period"."CAL_WEEK_DESC" FROM "Area Sales"')}

SQL Issued: SET VARIABLE LOGLEVEL=7; SELECT "Schedule Ship Period"."CAL_WEEK_DESC" FROM "Area Sales"

Luigi_Gif · Oct 30, 2017 9:58AM

I set my time dimension as displeyed

[Deleted User] · Oct 30, 2017 10:02AM

That's just a logical dimension and does not pertain to your question. You are missing all and and actual dimensional hierarchy objects?!

Either screenshot the real time dimension object with its configuration or - if they really do not exist - create them.

What kind of RPD is that? OBIA? Looks atrocious.

Joel · Oct 30, 2017 10:09AM

The error is quite clear:

Message returned from OBIS. [nQSError: 27045] Nonexistent column: "Schedule Ship Period"."CAL_WEEK_DESC".

You are referencing a column that doesn't exist in your rpd.

Luigi_Gif · Oct 30, 2017 10:13AM

Dear Christian,

I have never seen a funny RPD ahah, I am a Deloitte consulting and now I am trying to drive a costumer of ours in his BI activity. So they contact me when there is a problem, this RPD was build by them...

[Deleted User] · Oct 30, 2017 10:19AM

Is that column blocked for access by that user/role?

Robert Angel · Oct 30, 2017 10:48AM

Hi,

CAL_WEEK_DESC - call me psychic but I suspect that is a descriptor field, not a key. You need to reference a key in AGO function.

Might be worth a punt with CAL_WEEK_ID, but I would need to see what the corresponding dimension hierarchy looks like to comment more fully.

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Ago function

Answers