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

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

Find more posts tagged with

Accepted answers

All comments

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

build basic calendar table (fiscal, gregorian, etc)
build basic dates table keyed to calendar & date <- simple google searches reveal the simple logic needed for the foundation (modify to suit)
join fact tables to dates table
build logical hierarchies for year, month, week, day (modify to suit)
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?