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.
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.
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.
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
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.
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?
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...
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.
Okay for my sum by suggestion; -
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.
I don't use filters unless I filter the whole analysis. Column headings are done like this, all in Admin:
Cumulative EUR this Year: TODATE(Eur This Month, Year). Aggregation level is month so it calculates sum(jan:this_date)
Eur This Month: Base fact column, no calculations. Aggregation default (sum)
Cumulative EUR last year: TODATE(Eur This Month Last Year, Year). Eur This Month Last Year is calculated from base fact using AGO-function and level is month like in CumEUR
I'll check the links, thanks.
if 'Eur This Month' is your base level (lowest level of detail against the time dimension) then sum 'Eur This Month' (full syntax, don't know your folder names!) group by Year (ditto) as a formula should work.
Otherwise if there is a straight 'Eur' at detail level use that as the basis instead.
The filter version should work also, with a similar caveat around using the base measure.
Did you notice that the amounts are equal to the right columns multiplied by 144 (or 12*12)?
That might give you a clue what is happening.
Other then that not much to add to the help Robert is already providing.
"sum 'Eur This Month' (full syntax, don't know your folder names!) group by Year (ditto) as a formula should work."
Yes it should. Really. But it won't and that's the problem.
I have never understood how that filter syntax works with dynamical values, it could probably solve this.
"Did you notice that the amounts are equal to the right columns multiplied by 144 (or 12*12)?"
In this case they happen to be but changing the organization changes that too. Multiplying was the first thing I checked.