How to get YTD sale for the Financial year ( 1st Apr 2018 to 31st Mar 2019) In OBIEE 11g

Ramamohan Reddy

Hi All,

I have a fact table for Sale_Amt with date wise details. It is designed with self-join.

One of my reporting requirement is to show yesterday (sysdate-1), MTD (1st Nov to till date) and YTD sales (1st Apr 2018 to till date -- a Financial year)

Fact table data:-

Date                 item1 Sales     item2 sales

01-Nov-2018      100            200

02-Nov-2018      200            300

03-Nov-2018      300            400

Report requirement:

        Yesterday    MTD (1st Nov to till date)        YTD (1st Apr 2018 to till date)

Item1   300           600                             10000

Item2   400           900                             15000

I have applied below logics for Yesterday and MTD and it's working. But I am unable to find out the logic for YTD.

Yesterday:

FILTER("Sales"."Amount" USING ( "Date"."Date Time" =  TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)))

MTD:

FILTER("Sales"."Amount" USING ( "Date"."Date Time"  between   TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE) and TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)))

Kindly suggest me the logic for YTD.

Thanks in advance

Best Regards,

Ram Reddy               

[Deleted User]

Do you have an actual time hierarchy with a properly modeled Fiscal Year calendar in your time dimension?

If you have a correct basis you don't need all those convoluted formulas.

Ramamohan Reddy

Hi Christian,

Thanks for a prompt response.

There is a Financial year column in the time dimension table.

Date dim:

Financial_year   Financial_quarter                          Date_time

FY-2018  -19      FY2018-19 Q3, December            30-Oct-2018

If possible please send me timestampadd function for the First day of the Financial year.

Best Regards

Ram

[Deleted User]

It's not about timestampadd. It's about correctly configured time dimensions and then things become automatically available through timeseries functions.

Do you have a correct time dimension set up in your RPD?

Ramamohan Reddy

Hi Christian,

Can we achieve this requirement without time hierarchy?

If yes, Kindly suggest me design approach.

Regards,

Ram

Gianni Ceresa

3063133 wrote:Can we achieve this requirement without time hierarchy?If yes, Kindly suggest me design approach.

The "design approach" is to design a proper time hierarchy and let the tool do the job for which you paid the licensing price.

If you don't want to invest 10 minutes into modelling a time dimension with the proper hierarchies (as you can have alternate hierarchies for the normal calendar and the fiscal one), there isn't a design approach. It's all about logic in doing your calculation.

Similar to the "ugly" (sorry but that kind of formula is to be considered ugly when compared to the out of the box function for those calculations based on a time hierarchy) MTD formula, all you need is to add a piece of logic deciding if the 1st April is of this year or of last year, that's all you need. So a CASE WHEN there in the middle to manage that aspect and done.

[Deleted User]

Beaten by Gianni but I can't say it any better.

Use the tool you - or better your employer / client - PAYS money for. Use it the way it's meant to be used and how it works.

Or do you also buy a car and then as the first thing not use the engine to drive it but install a large hamster wheel and run yourself to keep the wheels spinning?

Thomas Dodds

Kindly suggest me the logic ...

1. build basic calendar table (fiscal, gregorian, etc)
2. build basic dates table keyed to calendar & date <- simple google searches reveal the simple logic needed for the foundation (modify to suit)
3. join fact tables to dates table
4. build logical hierarchies for year, month, week, day (modify to suit)
5. use calendar as a filter when building x-TD measures in RPD - after all a date's a date and now you can have YTD/QTD/MTD for any/all calendars

User_Z12HD

If I can't modify the RPD is it possible to use timeseries functions by referencing a date dimension?

Gianni Ceresa

@User_Z12HD ,

If the date dimension exists and has a time hierarchy yes. If a time hierarchy isn't defined no.

For a more precise reply, start a new thread explaining the whole context.

User_3K65B

Hi,

I have a same kind of project requirement and hierarchy for fiscal is defined at rpd level.so what are the steps i need to do to get data like YTD (1st Apr 2018 to till date) in my report.Please suggest?