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.
Agenda Presentation: What's New in FDI 26.R3 Open Q & A: Ask your questions of implementation and product specialists Replays of past FDI CEAL Guidance Office Hours are available on Oracle VideoHub here. Fusion Data Intelligence - CEAL Guidance Office Hours The FDI Office Hours are hosted by CEAL solution architects who…
So when I click the apply button, it uses the value . Also. How do I stop the auto populate table when the report opens???? I am in OAC
Good day, please kindly assist. We are creating a graph to calculate the number of assets per milestones. However, the planners have only populated assets for only one milestone from the list of milestones the customer wants to track. Which gives an incorrect view as the project moves past different milestones that it has…
I scheduled the report in OTBI and the agent was run successfully. However, I do not receive the report after the run in my inbox. The addresses are properly recorded, and the report is in an excel format and saved as a pivot table in otbi. What can be the cause?
Anyone has any idea why the OAC documentation on custom viz is not available now? I was able to access till last week - https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsdv/build-package-extension.html. Does this mean OAC doesnt support custom viz or custom workbook extensions?
I made a parameter with letter range values… put it into a dashboard filter. Then I created an expression filter to bind it to my last name column in the table with the image I uploaded here. I am getting the following error??? Invalid Expression. Validation of parameter values failed. A general error has occurred. Message…
![Photo of [Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)
