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

Configuring an analysis to automatically use the previous month in the date column.

Received Response
395
Views
3
Comments
hoisonsauce
hoisonsauce Rank 1 - Community Starter
edited Aug 13, 2024 5:38PM in Oracle Analytics Cloud and Server

I have an analysis that I want to send out automatically once monthly. Is there a way to configure the analysis to use the previous month's data? I currently have it set up to pull anything greater than or equal to the first of the year, and users will filter out the previous months.

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    This filter returns dates from the previous month:

    "Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-2,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE))

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Oops.  That one is for two month before the present.  Use this one:

    "Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE)

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    OR build a proper Calendar dimension and dispense with the calculations each time you need a variation on it.  Calc ONCE use MANY ...