Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 210 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
how to build a timestamp function to retrieve data from beginning of fiscal year to current date
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?
Answers
-
Create a Fiscal Year column using a case statement around your date field, say "Table"."Date".
case when MONTH("Table"."Date")<5 THEN YEAR("Table"."Date")-1 ELSE YEAR("Table"."Date") END
This places January-April of 2020 into FY 2019. Jan-April of 2021 will be in FY 2020.
0 -
mmm, not sure about this one. This will return only the correct year right? I do not have an additonal column for Month or so. The date field is called "Filters.Business Date". So i need to filter the business date, so that it will retrieve room nights and room revenue from 01-May-2020 till today and when i run the report next year let's say on the 15-Jan-2021 it needs to retrieve from 01-May-2020 till 15-Jan-2021
0 -
I used the Month and Year functions to create the Fiscal Year column. You can then filter on FY to retrieve the dates you want.
Filtering on Fiscal Year=2020 will return May 2020 to April 2021. You don't have to display fiscal year, just filter on it.
Of course, the best way would be a logical time dimension in your data model.
0 -
I see what you mean and if I would like to make a comparison with previous fiscal year on same report?
case when MONTH("Table"."Date")<5 THEN YEAR("Table"."Date")-2 ELSE YEAR("Table"."Date") END ?
So i would need to join same query and filter differently correct?
0 -
No, you wouldn't change the column formula. You'd change the filter to include whichever Fiscal Years you want. Use it as you would any existing-in-the-data model field.
If you filter on case when MONTH("Table"."Date")<5 THEN YEAR("Table"."Date")-1 ELSE YEAR("Table"."Date") END IN (2020, 2021)
you could then compare Fiscal 2020 with Fiscal 2021 in whatever manner you wish. (pivot table, chart, etc.)
0 -
Do you have the dual hierarchy in your time dimension? Calendar and Fiscal? If you do all the normal timestamp functions will work without any issue by just pointing to the right branch of the hierarchy.
A "Fiscal year" is a business object, it follows business rules saying when it starts, when it finishes etc.
Model it as such: an alternative hierarchy for your time dimension (which should contains all the required info for all the hierarchies you need).
0 -
Hi Gianni,
I am an end user, meaning i just use a business intelligence tool.
There is a dual hirarchy, but I was told Fiscal is not working, therefore I need to find a way to build manually.
The previous answer was helpful, using a CASE function to build the fiscal year to be used then as a filter.
2 limitation with that:
a. I return the fiscal year with that function, but the user still need to filter by which Year, which i would like to avoid.
b. It is working as a filter, but i was trying to implement into the prompt and there is not working. It let me select the year, but then doesn't run the report though i have prompted the column in the report.
The case function i am talking about is
case when MONTH("Filters"."Business Date")<5 THEN YEAR("Filters"."Business Date")-1 ELSE YEAR("Filters"."Business Date") END
Regards
Giuliano
0 -
If you put the formula into a column prompt, it will work, but your user will be selecting a fiscal year.
Use the edit window on the column prompt:
Then you can enter the formula, label it Fiscal Year. In your query, set the filter on that column to "Is Prompted".
0 -
Hi Jerry, this solution would work if i could set the a filter on the report using a presentation variable which i would define in the prompt, however as this is a combined report , meaning is using UNION requests, you can not set prompts on the combined reports. This is why Important to get it with a timestamp function so that there is no user imput.
0 -
I use prompts on union queries all the time, so I'm not clear why you can't. If the Fiscal Year Prompt sets a variable "varFY", every leg of the union should contain a filter for:
case when MONTH("Filters"."Business Date")<5 THEN YEAR("Filters"."Business Date")-1 ELSE YEAR("Filters"."Business Date") END IN (@{varFY}['@']{2020,2021}) (The ['@'] is used when multiple selections are passed in the variable.)
If Market Code and Channel exist in the same data model, you may be overcomplicating this with an unnecessary union. You could include a "By Market Code" or "By Channel" clause in all of your calculations.
If it has to be a union, one thing I usually do is to add a "bin" column that differentiates the type of records, which in your case would contain the text 'Market Code' or 'Channel'. Then you can do a single pivot that has "Bin" in the section box.
If you haven't already done so, you should read up on "Result Columns", which allows you to do calculations outside the union, referencing column positions. It's one of the most versatile aspects of union operations..
0
