nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Ago function

Luigi_Gif

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

Find more posts tagged with

Accepted answers

All comments

Joel Acha

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

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

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

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

I set my time dimension as displeyed

[Deleted User]

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 Acha

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

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]

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

Robert Angel

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.