Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 12c - how to calculate (and keep!) the value for the whole last year
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
Answers
-
Hi,
when you say; -
"whole cumulative amount for the last year" do you mean - Sum(Jan:Dec) or do you mean Sum(Jan:CurrentDate)
Assuming Jan is your start of year.
If you mean the former of the two then just duplicate your measure in the rpd and pin it to the Year, and when you display it with 2017 it will be Full Year 2017, If 2016 full year 2016, if 2018 Total Year to Date.
0 -
I mean sum(Jan:Dec) and I have done exactly that but it doesn't work, no idea why. Here you see what happens:
-1 -
You could also create a new rpd column based on the original physical measure and add a case statement to filter for the value by only if year - 2017. Again this would need pinning on the time dimension to the Year level, if you want it to be invariant, or leave it at detail level if you do want it to give you a different figure for quarter / month / day granularity.
0 -
Check your Logical Table Levels settings between the fact and the dimensions it joins to.
Check your keys on the dimension hierarchies are actually unique.
Also, try experimenting with 'server complex aggregate' on, on the measure.
0 -
When you say 'exactly that' you mean; -
1. Duplicated base existing measure
2. Change name to Year Total (or whatever) leaving aggregation as sum
3. Dragged Year Total Measure to Time / Year dimension hierarchy level to make its level Year
Yes?
0 -
1-3, Yes. I have checked all joins, all hierarchy keys and tried server complex aggregate and no, still quite interesting amounts. I've even tried to calculate this in db but it's a bit heavy way to do this.
0 -
And you have checked the Logical table source levels are correct fact to dimension?
i.e. Expand the fact / dimension in the business model layer, see the table source, click on it, examine the detail level, make sure the levels are set as appropriate, which in most cases is detail level, assuming no aggregate fact / non-conformed dimensions
In answers you can also use sum by in a formula to achieve this - which does not solve your underlying issue, but does give you a temporary work around if it is urgent - which should work and I have used many, many, many times successfully...
Btw - one other thought, you do not have any case logic at all in the answers report?
0 -
This is a very simple mode with one fact and one dimension, levels are correctly set. Could you give me a working example how to do this in answers? What I got from Oracle support doesn't work anymore, neither anything I have tried myself. And no, there's no case clauses in the analysis at the moment.
I start to suspect that there is a bug, we are running on AIX and it causes problems sometimes...
0 -
Sure, but can you explain the function behind the filter headings at the top of the screenshot that you show, i.e.
Cumulative EUR this Year
Eur This Month
Cumulative EUR last year
Do these drive filters and if so how are the filters "caught", do they apply in the traditional way, a filter that reduces the total data of the entire analysis, or do the filters apply via an alternative function based mechanism on your other members.
If it is easier send a screen shot of the design pane.
Just trying to ascertain if there is anything that can effect the pinned value.
0 -
Okay for my sum by suggestion; -
https://stackoverflow.com/questions/27066406/summing-by-column
For using filter; -
I would have liked to give you a fuller answer, but to do so I need you to give me some more information, so this is the syntax 'cold', the best I can do.
0