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 build a timestamp function to retrieve data from beginning of fiscal year to current date

fabryddorf-Oracle

Hi Guys,

I am looking for a timestamp function that will return data for the fiscal year period:

01-May till 30-Apr of every year as well as from 01-May of current year till current_date

I have figure out so far the below expression, but it won't work next year when it still needs to refer to FY 01-May-2020

"Filters"."Business Date" between timestampadd(sql_tsi_month,4, TIMESTAMPADD(sql_tsi_day, -dayofyear(current_date), current_date)+1) and current_date

Could you please help with that?

Find more posts tagged with

Accepted answers

All comments

fabryddorf-Oracle

Hi Jerry,

Thanks for this. As my prompts were not working and I google it, I found that they work not with union queries and also the error message says exctly that, but good to know they work. Will do more testing.

I am not sure if it is called Bin function, but actually i did so, creating an extra column for channel, market etc.. and created the label 'market', 'channel' etc..

I have been further working on the filter and came out with this which seems to work for now, as I do not have data for last year.

FY YTD="Filters"."Business Date" between CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, case when CURRENT_DATE >= CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, 0,CURRENT_DATE)) AS CHAR) AS DATE) then -0 ELSE -1 END ,CURRENT_DATE)) AS CHAR) AS DATE) and current_date

FY LY YTD= "Filters"."Business Date" between CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, case when CURRENT_DATE >= CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, 0,CURRENT_DATE)) AS CHAR) AS DATE) then -0 ELSE -1 END ,CURRENT_DATE)) AS CHAR) AS DATE) and current_date

Maybe it is not elegant, but it seems to work. I will try your solution though, so i can learn more.

Jerry Casey

I'm glad you found something that works, but you should really be working with your data base folks to model a correct time dimension, as Gianni recommended. Then, the analysis you're doing would be a simple exercise . Otherwise, it's an inefficient use of your time, and leaves a maintenance mystery for anyone that has to update or tweak your work.

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/comment/16773665#Comment_16773665

+1'000'000 that "logic" gets processed for every single row every single query which runs. Performance, maintenance etc all suffer badly from such "solutions".