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

User_0PDV4

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.

Robert Angel

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.

Martin van Donselaar

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.

User_0PDV4

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

Robert Angel

You don't need "dynamical values" (is that a thing?!) on this at all, you just need the base measure sum by year, or base measure filter by year.

If this is not working then check into your base measure and ensure that it is base and there is not additional logic happening in the rpd.

If there is additional logic then kindly share so we can advise.

User_0PDV4

But how do I do it? I need specifications for dummies.

I tried FILTER("Cumulative EUR last year" USING "D1 Time"."Month"='201712') and ok, I get what I want to that certain column BUT my prompted months won't work after that and if my selected month is anything else but that 201712, the column will be empty. If I could tell that month=my prompted month(+ x to get the last month for the year) it could maybe work. I tried that with year, it didn't work at all - it shows only empty column.

User_0PDV4

In RPD everything else works just fine except the whole year calculation:

- base measure ok

- cumulative base ok

- base -1 year ok

- cumulative base -1 year ok

- all differences and percentages between them ok

- cumulative base -1 year for year level does not work and no calculation in Answers works either

Robert Angel

If you put your prompt into a presentation variable called PromptYear

FILTER("Cumulative EUR last year" USING "D1 Time"."Month"='201712')

FILTER("Cumulative EUR last year" USING "D1 Time"."Year"= '@{PromptYear}{2017}')

Note you originally said not changing for the year, otherwise; -

FILTER("Cumulative EUR last year" USING "D1 Time"."Month"= '@{PromptMonth}{201712}')

Hope that is dummy enough...

User_0PDV4

It's dummy enough, thanks. But still it won't work. If I filter like that and prompt for 201706 (or anything except 201712), the filtered column shows empty. Basically it's logical; if prompt filters the data to a certain month, I can't expect it to show data for another month at the same time.

This has been one %&/!" last years, they have similar requirement almost in every subject area. In HR area normal measurement level settings work for some reason but for financial data it won't. Haven't found out what's the difference there, same db, many common dimensions (like time, organization, customer etc), same building technique...

Robert Angel

And did you also try what I suggested - in your business model layer pinning a copy of the measure to the Year level in your time hierarchy to make an invariant version 'Year Total' which can be used to keep the same value when the months change?