Year To Date measure

Luigi_Gif

Hi,

I have worked on OBIEE for 2 days and I have my first problem. In my dashboard I have an analysis in which different column values are showed, splitted by months. My purpose consist in having a year to date calculation of each items, does someone can help me please ?

I wish to obtain, if I select February, sum values of the two months of the year

Thank you forward

Kind regards,

Luigi

Gianni Ceresa

Hi Luigi,

If you look at the available functions in the "Time Series Calculations" group you find TODATE.

As the name says it will gives you the to date figures of a given measure.

For example something like: TODATE("Revenue Metrics"."Revenue", "Time"."Time Hierarchy"."Year")

It will return the "Revenue" measure "year to date". The level of the "to date" is set by the second argument of the function. This is the name of the wanted level of the time dimension hierarchy. It isn't the logical column but really the name of the level of the hierarchy.

This function will gives you what you look for.

PS: just realized that you said "2 days", so the function must be used in the formula of a column (in the criteria tab of your analysis you can click on each column and edit the formula)

Luigi_Gif

Hi Gianni,

Your answer is correct, because...reading different forum I just found TODATE function as my problem solving. However, the problem is...it seems TODATE function works only if you set a time dimension, in fact if I write TODATE ("Sale Amount"."Actual", "Market Period"."Year") the code doesn't work

Gianni Ceresa

That's why I wrote this before:

This is the name of the wanted level of the time dimension hierarchy. It isn't the logical column but really the name of the level of the hierarchy.

And you must have a time dimension, or you just forgot to model your things right. And as you are on OBIA there is definitely a time dimension (can't imagine how you can't have it ...).

Just open your subject area and look for it, once you find it expand it to get the name of the level you look for and there you have the name to use 2nd parameter: <presentation table>.<hierarchy name>.<hierarchy level name>

If you don't have a time dimension you can't be looking for a "to date" logic as to have a "something" to date it means you have a proper definition of time in your model.

Luigi_Gif

Ok Gianni, thanks a lot for your help; now it seems working. I am sorry but it's my third day on oracle BI . A last dubt... I want to insert TODATE function into "Actual" column (the 6th from the left of my picture) that has a formula such as: FILTER("Sale Amount"."Order" USING ("Scenario"."Scenario" = 'Actual' AND "Account"."Child"='TOT'))

can you advise me how can i better insert todate function into ?

I am so please for your help

Luigi

[Deleted User]

Luigi_Gif wrote: I am sorry but it's my third day on oracle BI 

And you immediately came here. You did everything right that you can do right. Welcome!

Gianni Ceresa

The function just replace the measure itself, so in your case I would say you replace the "Sale Amount"."Order" by the TODATE(...) inside FILTER.

Didn't check the generated physical query but there anyway many chances the result will be the same if you do TODATE(FILTER....) instead in a normal "simple" analysis.