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.
Explore how to build agents using code in AI Data Platform. We'll create an agent that examines and analyzes the contents of a Github repository and reports its findings. The session demonstrates how to use different developer tools to create agents including the AIDP extension for VS Code and Codex. Using these tools we…
The user icon in the upper-right corner of OAC (DV) is displaying more menu options for our non-admin users than we wish to display. We would like to restrict these to only "Profile" and "Sign Out" for regular users. Is there a way to customize these menu items? Especially if we can control this via security, that would be…
What is wrong with this code
Am I missing something or is there no way to search through previous posts to make sure I don't duplicate a question? All I see on the filter options are tags or status… a general search for a word or phrase would be helpful.
We were under the impression that when exporting a visualization in DV to CSV format hidden columns would be include from the grammar panel. This is not happening. See below for a screenshot showing 3 hidden columns that do not appear when exporting the pivot table to CSV. This seems to have worked a month or so back but…
Migrated RPD from obiee12c to OAS 2024. post migration when we try to access dashboards on OAS we are getting below error: State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000) State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS…
![Photo of [Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)
