Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
In OAC how to set last month's first day to a filter
I have a datetime filter with a start date, but I want it to default to the first day of last month. How can I make this happen?
I created a calculation using TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE) and assigned it as the filter, but it's not affecting the visuals.
Best Answer
-
HI @User_TUWA3 ,
You are using a calculated column to initialise the available values for the parameter, not as a default selection (Initial Value is blank, so nothing is selected by default).
You should proceed as follows:
- Get the logical SQL generated by your calculation. You can add a table with your calculated column in it, and use Developer Options to see the generated logical SQL.
- Change Initial Value to Logical SQL Query in your parameter definition, and paste the query that corresponds to your calculated column
- Bind the parameter to the start value of your filter
Please note that your current calculation is not going to return the first day of last month, but today's date minus one month.
0
Answers
-
Hi @User_TUWA3 ,
Can you please share a screenshot of your filter definition and how you assigned your calculation to it?
My understanding is that you are in a DV workbook. I would create a new parameter, initialise it with the first day of last month date, and then bind it to the start date in a date filter.
0 -
@Federico Venturin
This is how my calculation looks like
And this is how my parameter looks
Binding this parameter to the filter shows no value in the field.I want it to set and display the last months first date.
0 -
HI @User_TUWA3
To get the first day of the last month, the calculation below can be used because TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE) does not return the first day of the last month.
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, -1*DAYOFMONTH(CURRENT_DATE)+1, CURRENT_DATE))
Regards,
Bala.
2 -
Thanks @Federico Venturin approach suggested by you worked.
0 -
Thanks for the query @BalagurunathanBagavathy-Oracle
0
