nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Issues with YTD

user499963

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

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

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

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

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

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

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

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

Thanks Thomas.. am very grateful

Thanks Gianni . noted and well said.

Thomas Dodds

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.