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

Run analysis for the previous month data only

Received Response
755
Views
2
Comments
user1106547
user1106547 Rank 2 - Community Beginner
edited Aug 13, 2024 6:16PM in Oracle Analytics Cloud and Server

i want to run an analysis that displays data for the previous month.

so if i set up an agent to run say 1st day of every month, the data would display for the preceding month only.

i have created this field TIMESTAMPDIFF(SQL_TSI_MONTH,"Date (procedure)"."Date",CURRENT_DATE) which returns a value i thought i could filter on but the value returned isn't accurate.

For example if i run the report  today to include data up to 26th july , early July values are 1 while later july values are 0. i would expect all july values to be 0, all june 1 etc.

Anyway above is probably not  the right approach. Is there a simpler and accurate way to achieve this?

Any help appreciated

thanks

Answers

  • Andrew Fomin.
    Andrew Fomin. Rank 6 - Analytics Lead

    That may be confusing, but that's how TimeStampDiff works. If you take a look at the SQL generated for your report, you'll see something like (for Oracle DB):

    ROUND( MONTHS_BETWEEN( TO_DATE('2016-07-26' , 'YYYY-MM-DD'), T1714.DAY ) ).

    This formula returns 0 for values starting with 11th July and ending with 10th August.

    I'd say the best choice is to use a repository variable for the previous month. Or you can play around with Month(...) and Year(...) functions. But I think a repository variable approach is much better.

  • user1106547
    user1106547 Rank 2 - Community Beginner

    thanks for suggestion re variables but in the end just used below as filter:

    First day of previous month

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

    Last day of previous month

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

    seems to work ...