Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

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

Expression Filter that will exclude the latest/maximum month?

Received Response
195
Views
7
Comments
JobDeLuna
JobDeLuna Rank 5 - Community Champion
edited Aug 13, 2024 7:33PM in Oracle Fusion Data Intelligence

Hi,

The challenge here is how to exclude the latest month since Dec-24 is not complete and shouldn't be included in the reporting yet.

Also, since I am using the ‘CURRENT_DATE’ function, the earliest month, which is Dec-23, also becomes incomplete.


faw1.png


Tagged:

Answers

  • Mallikarjuna Kuppauru-Oracle
    Mallikarjuna Kuppauru-Oracle Rank 8 - Analytics Strategist

    Hi @ JobDeLuna

    The below expression : Current date - 13 month value.

    TIMESTAMPADD(SQL_TSI_MONTH,-13,CURRENT_DATE)

    Fiscal Day Date == What is the value are you getting from DB table?

    Regards,

    Arjun

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hello, @Mallikarjuna Kuppauru-Oracle

    Thank for your response. this the value I am getting based on the expression filter I have.


    The earliest is 12/11/2022 and my aim is to be 12/01/2022 and the latest is 12/04/2023 which should be to 11/30/2023 only. I should exclude the latest/max month which for this one is December because usually that is not complete.

    faw2.png


  • Sumanth V -Oracle
    Sumanth V -Oracle Rank 8 - Analytics Strategist

    @JobDeLuna

    Why don't we use the date range filter provided within the visualization tool to achieve this requirement? Please share the challenge if it was already tried. Thanks.

    image.png


  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Sumanth V -Oracle

    I don't want to use the date range filter because I am planning to create a table with the columns Last 12 months, Last 3 months and Last 1 month. Using date range filter in a table will give me the same result for all columns.

  • Richard Kemp-Eyre
    Richard Kemp-Eyre Rank 4 - Community Specialist

    Hi JobDeLuna,

    Would the following work:

    (("Time"."Date" > (TIMESTAMPADD(SQL_TSI_MONTH, -12,TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))))) AND ("Time"."Date" <= (TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))))

    I've used Time.Date dimension but you could replace this with Fiscal Date. It effectively does a between the first day of the last month minus 12 months and the last day of the last month. I used the following website as a reference http://obiee11gtips.blogspot.com/2015/11/useful-timestamp-codes-in-obiee11g.html

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hello, @Richard Kemp-Eyre!

    Thank you for your response.

    I tried the calculation you have sent but I'm having an error.


    image.png


  • Renuka Nuguru-Oracle
    Renuka Nuguru-Oracle Rank 5 - Community Champion

    I believe the above sql should be in where clause

    Some think like this

    SELECT 

      "Time"."Date" saw_0

     FROM "PC0"

     WHERE 

    (("Time"."Date" > (TIMESTAMPADD(SQL_TSI_MONTH, -12,TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))))) AND ("Time"."Date" <= (TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))))