YTD wrong values on year level — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

YTD wrong values on year level

Received Response
21
Views
6
Comments
User294199
User294199 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:18PM in Oracle Analytics Cloud and Server

Hi,

i have following problem.

I have following analisys:

pastedImage_1.png

The result is correct. Quantità YTD, for example for February Month is the sum of Quantity for January and February and so on. The total of Quantita YTD è 288.950.856

It should be the value of Quantità Ytd for Year 2017, but when i drop Month level, the result of Quantita YTD is :

pastedImage_2.png

instead 288.950.856

Strange thing is that the YTD is the value of December 2017.

I have setted my rpd hierarchy as following:

pastedImage_3.png

Anno level settings:

pastedImage_4.png

The anno field contains values 2010, 2011,2012,2013 and so on

Mese level settings:

pastedImage_5.png

The AnnoMese field contains the concatenation of the year with the month: for ex. 201001, 201104

The Mese fields contains values January, February, March and so on...

Giorno level settings:

pastedImage_6.png

The DataAS400 field contains values 20100201, 20110406 and so on.

What's the error on hierarchy? Can you help? It's quite urgent.

Regards

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    What OBIEE return is correct, that's the meaning of YTD.

    You posted many things but not the formula of your "Quantità YTD" column, what is this one like?

    I guess it uses TODATE(<your measure>, <the year level>).

    YTD is simply the sum of all the months from the beginning of the year level till the present month.

    If you remove the month totally, YTD is just the sum of all the months of the year, which is your 47,549,596.

    OBIEE is doing everything right based on what you asked it to do.

    Of course it's all based on the guess of formula behind your column....

  • Gianni Ceresa
    Gianni Ceresa Mod

    The 288.950.856 you look for is January * 12 + February * 11 + March * 10 + ... + December * 1

    This isn't the definition of YTD and it also make little (if not no) sense.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

  • User294199
    User294199 Rank 3 - Community Apprentice

    Hi Gianni,

    so at year level there isn't difference between Quantità and Quantità YTD?. If I want what i expect (288.950.856 at year level) i have to use another function and not ToDate?

    Thanks

  • Gianni Ceresa
    Gianni Ceresa Mod 
    User294199 wrote:so at year level there isn't difference between Quantità and Quantità YTD?

    You still didn't show us what the formula of "Quantità YTD" is like, so I can only answer based on the meaning of 'year to date': a year to date figure at the year level is the same as the sum of the 12 months of the year, therefore if you base measure is aggregated by SUM, the YTD and normal measure are the same at the year level.

    If you want what you expect you have to make a weird formula because as I said what you are asking for is January * 12 + February * 11 + March * 10 + .... + November * 2 + December * 1.

    This can be calculated by using the a formula ...

    I'm not even aware of a possible name to call that kind of formula, just because I really don't see a possible meaning of such kind of formula.

  • User294199
    User294199 Rank 3 - Community Apprentice

    Hi Gianni,

    resolved!!!. I added a hidden Month column. In this way at year level, for each year, OBIEE calculates the sum of monthly YTD. in my example 288.950.856 for year 2017.

    Thank you

    Regards.