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.
Where AI meets business outcomes Oracle AI World brings the Oracle community together to explore how AI and other technology innovations are driving real business outcomes across industries. Discover what’s new through: Mainstage keynotes to hear the latest directly from Oracle leadership Product demonstrations Customer…
I tried the following expression and it blows up when I select more that one value in the list see image
We are using Oracle Analytics Server 2026 with WebLogic Server 14.1.2. Our Account Statement report is accessed through '/xmlpserver'. We need to capture the following details in the OAS/Publisher log files whenever a user runs the report: Username Report name and path Report execution timestamp Account Number From Date To…
We need to download a report with the order number as the file name. In the main report, we have a data action (hyperlink) on each order number. When we click an order number, the linked report is downloaded with the corresponding order details. Currently, the file is downloaded using the report name. We need to download…
Hi all I work in UK healthcare across 30 hospitals nationwide, and we're currently reviewing our Oracle estate and roadmap. Part of that is deciding whether to make a case for investing further in Oracle Analytics Cloud for workforce reporting, or whether OTBI and our existing tools are enough for now. Our HR data sits in…
![Photo of [Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)
