Oracle Communities

Oracle Analytics and AI

Child Item

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.

Find more posts tagged with

Accepted answers

Federico Venturin - Oracle

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.

All comments

Federico Venturin - Oracle

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.

Rashmi Jadhav-Oracle

@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.

Federico Venturin - Oracle

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.

BalagurunathanBagavathy-Oracle

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.

Rashmi Jadhav-Oracle

Thanks @Federico Venturin approach suggested by you worked.

Rashmi Jadhav-Oracle

Thanks for the query @BalagurunathanBagavathy-Oracle