Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OBIEE 12c - how to calculate (and keep!) the value for the whole last year

User_0PDV4Jan 16 2018 — edited Jan 31 2018

Hi,

I need help with a kind on cumulative calculation in OBI 12c. I have an analysis with euros in certain times:

- month = certain month value

- month YTD = cumulative from the start of the year

- month YTD previous year = cumulative from the start of the last year, for comparison to previous column

- whole cumulative amount for the last year - THIS IS THE PROBLEM

I have tried several ways to calculate it (in Admin and in analysis) but it just don't work and gives insane numbers. It should show the same amount all the time, no matter what is the base month in analysis. For example base month is 201704, this should show the whole 2016 value aka cumulative value for 201612. If base month changed to 201712, it should still show the whole 2016 value. How can I do this? Our time dimension is three levels, month-quarter-year.

In analysis I have tried this, won't work (got this from Oracle support when we still got 11g): AGO((AGGREGATE("MyFact"."ActualEur" AT "D1 Period"."D1 Time"."Year")), "D1 Period"."D1 Time"."Year", 1)

In Admin I have tried this, won't work either:  TODATE("MySubjectArea"."MyFact"."ActualEURPreviousYear", "MySubjectArea"."D1 Time"."Year")

Thanks in advance!

BR,

Mari
This post has been answered by User_0PDV4 on Jan 31 2018
Jump to Answer

Comments

gdarling - oracle
Do you see the same behavior when connecting via SQLPlus after setting the environment variables? Is this an ODP.NET specific issue? If it's not specific to ODP, you'll probably get better insight by posting in one of the generic database forums.

With respect to ODP specific solutions, have you already checked out the OracleGlobalization class?
http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleGlobalizationClass.htm#i1009565

Hope it helps,
Greg
928114
Yes even connecting to SQLPlus having environmental variable setting.. facing same problem.. However on explicitly setting NLS cases insensitive session values on each logon it is working..
Don Kleppinger
We have always used the login trigger to set the NLS settings. Our DBA set this up when we were on 10g and still do it now that we are on 11g. There must have been a reason he set up up using the trigger. Maybe same issue you're having. Since we are using connection pooling anyway I believe the login trigger only fires once anyway and we don't have any performance problems with getting connections.
Don Kleppinger
note that if you use the global NLS setting, all of your indexes on text columns must be function based indexes or they won't be used.
i.e
NLSSORT("FIRST_NAME",'nls_sort=''BINARY_CI''')
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 28 2018
Added on Jan 16 2018
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee
29 comments
2,296 views