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

Issues with YTD

Received Response
2
Views
7
Comments
user499963
user499963 Rank 1 - Community Starter
edited Aug 13, 2024 6:13PM in Oracle Analytics Cloud and Server

I have a fact table housing different granularity (date grain)

  1. Monthly
  2. Daily

The month data can be accessed by filtering by end of month date or using YYYYMM date format. In OBIEE RPD repo, the fact is set to LAST Aggregation.

I want to perform Year to Date analysis. And I want to sum only month end dates.

Using function TODATE(Measure), it tends to sum up all the data through out the month e.grain

Date            Amount  YTD TODate(Amount) <br/>31/01/2106      100     100 <br/>28/02/2016      200     300 <br/>14/03/2016      50      350* <br/>31/03/2016      100     450

I want YTD to ignore 50 and return 400, so also any other dates that falls within any month. And if if I Select 14/03/2016 I want 350 to return.

Thanks.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Physically co-locate your data - but get yourself a row-type flag (D-daily, M-monthly), then logically split it - you are mixing granularity and will have nothing but issues.  Use of information drives model design.

  • user499963
    user499963 Rank 1 - Community Starter

    Yes I can do that, in my date dimension I have ISENDOFMONTH flag. Now I need how to manipulate the function to sum the amount up to make YTD.

    I want to be able to do:

    1. Jan to August (all end of month)

    M + M + M + M + M + M + M + M

    2. Jan to August (Non end of month)

    M + M + M + M + M + M + M +D

    This should work for any time in the year.

    Thanks

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You need to mark the rows in your fact to the grain they are - the dimensional attribute won't help as you could have 12/31/2016 as both a day row and a month end row and a year end row ... you need to logically fragment the fact table on the row-type not a dimensional attribute of the date.

  • user499963
    user499963 Rank 1 - Community Starter 
    you could have 12/31/2016 as both a day row and a month end row and a year end row

    I get 12/31/2016 as end of month row already.

  • Gianni Ceresa
    Gianni Ceresa Mod

    I would say Thomas already said most of it ...

    Your source isn't the best you can have, mixed granularity in a single table is the worse to use in an analytical tool like OBIEE generating queries on the fly if you don't model it right.

    So following Thomas advice you must model splitting the multiple granularity into separate things at the logical level and tell OBIEE about the various levels (content levels etc.).

    Then you YTD is just a standard out of the box functionality you can achieve by the ToDate() function or by modelling it with a join on the calendar dimension.

    user499963 wrote:... Now I need how to manipulate the function to sum the amount up to make YTD.

    That's actually what I would suggest to avoid: why to manipulate/hack/force something special when OBIEE can do it out of the box as long as you give it enough information about your data and model?

  • user499963
    user499963 Rank 1 - Community Starter

    Thanks Thomas.. am very grateful

    Thanks Gianni . noted and well said.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    12/31/2016 as end of month row already -- how do you know it is end of month and not end of year or just the day's values from that table ... your date dimension cannot help you with that determination if your facts are additive.