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

How to calculate month end dates from all months?

Received Response
762
Views
5
Comments
ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD
ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD Rank 5 - Community Champion
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

Hi,

I am getting last day of current month. That formulae is

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


But, I want month end dates only from all months.i need  formula for this.

Please help me.

Thanks & Regards,

A.kavya.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    what does it means "for all months" for you?

    Because there is an infinite number of months ....

    Somewhere you must have dates or months or something and if you use that instead of CURRENT_DATE you will be able to calculate the last day of the references month.

    Remember OBIEE never generate rows from nothing, so if you have a row / column referencing a month you can get the last day of that month, if you don't have anything OBIEE will not generate anything ...

  • Sandeep Andhale
    Sandeep Andhale Rank 3 - Community Apprentice

    Hello Kavya,

    you can use repeat() function under string folder otherwise switch case option is there

    regards

    Sandeep

  • Gianni Ceresa
    Gianni Ceresa Mod

    The REPEAT() function? this one will just output the first param as many times as the second one, so how is it supposed to give you end of month for all the months?

    REPEAT('abc', 4) => 'abcabcabcabc' (so 'abc' 4 times)

    And CASE WHEN will never generate any rows of data, if there aren't dates somewhere in the dataset it will never generate new columns with data inside.

  • Sandeep Andhale
    Sandeep Andhale Rank 3 - Community Apprentice

    Hello Kavya,

    in your expression contain the current date instead of that ,

    create new date column it must contain every month date ,

    created date column pass  input to your expression .

    new created column create as(New Calculated measure or New calculated Item or new Group )

    regards

    Sandeep

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    create min(date),max(date) fields in the rpd and set content level for Month level

    or try

    start->TIMESTAMPADD(SQL_TSI_DAY,1,min("Time"."Date" by "Time"."Month Name"))

    last->TIMESTAMPADD(SQL_TSI_DAY,1,max("Time"."Date" by "Time"."Month Name"))

    in report.