1 2 Previous Next 29 Replies Latest reply on Jan 31, 2018 1:45 PM by 863364

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")

BR,

Mari

• 1. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 2. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

I mean sum(Jan:Dec) and I have done exactly that but it doesn't work, no idea why. Here you see what happens:

• 3. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 4. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 5. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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?

• 6. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last 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.

• 7. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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?

• 8. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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...

• 9. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 10. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 11. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

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

Hi,

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.

• 13. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

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.

• 14. Re: OBIEE 12c - how to calculate (and keep!) the value for the whole last year

"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.

1 2 Previous Next