Hello, I wanted to create a calculation to get the last day of the month for the last 6 months, I would appreciate a suggestion how I can do that? i.e. what function to use?, does OAC have a feature I can turn on or off for this?
Yes, you can use a calculation where you enter your formula.
The functions you will use are just 2: TIMESTAMPADD to add or substract days and months, and DAYOFMONTH to have the number of the day of the month.
The logic is just what I said above.
For a given date, you will subtract (1 - number of the day of the month for that date) days from your date. This give you the 1st day of the month for that given date.
Then you add 1 month to it, to get the 1st day of the month of the following month of the given date.
And finally you subtract 1 day to get the last day of the month of the given date.
But it's up to you to have a column giving you 6 dates for your 6 months. Otherwise you need to create 6 calculation using CURRENT_DATE and then subtracting 1, 2, … 6 months. And you will need to use those 6 calculation (you can also make them parameters or whatever you want, but it will be 6: because the tool doesn't generate rows of data out of nowhere).
PS: I could have posted the formula, but I don’t do it on purpose because it’s more important to understand the logic and functions than just copy a piece of code randomly from a forum.
And the logic I posted can be optimized with a timestampadd less easily, but again its easier to take a longer path at first to get the concept… The shortest form has been posted below, despite being hardcoded for a single date.
yes unfortunately there is no LAST_DAY_OF_MONTH function, you have to go with -1 day from the following month to get the date for the preceeding month
Example: DAYOFMONTH( TIMESTAMPADD( SQL_TSI_MONTH, -5, TIMESTAMPADD( SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE), CURRENT_DATE ) ) )
The inner TIMESTAMPADD() is subtracting the DayOfMonth result from the current date, getting the final date of last month. So the outer TIMESTAMPADD() is subtracting 5 months from the last date of last month.
Hi,
In what place of OAC are you trying to do that?
Because the last day of a month is something "dynamic" (28, 29, 30 or 31), you can generally easily get it by combining a number of simple functions available in the tool (assuming "classic", DV workspace or RPD): subtract the day of month from a date, add 1 month, substract 1 day. Because nothing "generate" rows of data out of nowhere, this works by applying that formula to any date (it's up to you to get 6 records, one for each month).
But still, at the end of the day, the ideal way to handle this is by having a clean, well build, time dimension with all the required attributes available all the time (first day or months, last day of month, ago dates etc.).
Amen to that @Gianni Ceresa. Properly modeled time dimension :)
@Gianni Ceresa - I am trying to do it on the workbook side. does this mean I have to use a calc for it? if so which functions would be helpful.
Some examples
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
@kalikhan , are you trying some GenAI service and randomly posting it?
How about first reading the generate answer and see if it does make sense? If you really believe you need 4 calls to TIMESTAMPADD to get the first day of current month, you better think at it twice.
Please don't post GenAI content: if people would like ChatGPT or other generated randomness, they would ask there and not in a forum.
One point to remember: Whatever logic or code you use - it will get executed row-by-row. So some examples in this thread may work nicely in theory and in practice for 20 rows but probably aren't really a viable choice to run against data sets that source themselves from multi-billion row fact tables…
Edit: And always test all formulas posted in here in detail. As Gianni said it's about the concept and not all code posted here will yield the results you expect ;)
By the way, the formulas are wrong… Or maybe somewhere in a virtual world the first day of current month is November 30.
Description: The Oracle Fusion Data Intelligence (FDI) Product Roadmap Update provides an overview of the latest developments and future plans for FDI. Oracle Product Management experts will share upcoming capabilities, key enhancements, and product direction, helping attendees understand what is changing and how these…
I'm trying to index a Local Subject Area so I can use it in my AI Agent, however, I keep receiving the generic error below. Request status(500) : error = {"prefix":"DSS","code":50000,"message":"There was an unexpected error while processing this request. Please try again."}
it is observed that in OAC while using case statements the output is not expected.For example if we have a case statment like case when table1.column1=table2.column1 then 'yes' else 'No' end as Attribute1 Now if i use this attribute with a fact-amount column attribute 1|| Amount on a report then we dont get the aggregation…
Once I select that value from the list box Region I created, I want to filter the table by my selection. See the images
Hi, While using OAS or OAC latest version can we generate a report to view complete defition of semantic model/subject area. It helps to document and troubleshoot. thanks
![Photo of [Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)
