Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OTBI Parameter - formula of numeric number of the month

Accepted answer
32
Views
5
Comments
Nazera Shabnam.
Nazera Shabnam. Rank 2 - Community Beginner
in Oracle Analytics Cloud and Server

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

Tagged:

Best Answers

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    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:

    image.png

    Hope this help.

    Thank you.

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    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
    Gianni Ceresa Mod
    edited Jun 3, 2025 7:58AM 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.

Answers