OTBI Parameter - formula of numeric number of the month

Question

Could anyone help me with the formula to get the numeric number of the current month and previous month to use in parameter of OTBI Report

Example:

If current month is June, then I should get the value as 6 for current month and values as 5 for previous month

If current month is July, then I should get the value as 7 for current month and values as 6 for previous month

Bhaskar Konar · Accepted Answer

Hi @Nazera Shabnam.

Welcome to the Oracle Analytics Community!

Can you please try the following formulas:

Current Month:

MONTH(CURRENT_DATE)

Previous Month:

MONTH(TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

It is working for me:

Hope this help.

Thank you.

Bhaskar Konar · Accepted Answer

Hi @Nazera Shabnam.

Additionally, please find below the sample dashboard prompt where the formulas have been used which might be useful for the understanding.

Current_Previous_Month_Pmt.catalog

Hope this help.

Thank you.

Gianni Ceresa · Accepted Answer

@Nazera Shabnam. ,

You don't need to use time calculation for the previous month number. Those calculations generate quite heavy queries while you can do the same with simple mathematics.

For example, MOD(MONTH( CURRENT_DATE )  -13, -12) + 12 gives you the previous month number (1-12) without any date calculation (adding days, subtracting days etc.), which usually become a poor SQL query for your database. While with this formula you have the same "weight" on your database as having the current month number, because mathematical operations are usually simple and faster.