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

Previous Year Values using Date Between dashboard prompt

Received Response
1
Views
2
Comments
user3507711
user3507711 Rank 1 - Community Starter
edited Aug 13, 2024 5:19PM in Oracle Analytics Cloud and Server

Hi,

There is Date dashboard prompt, where " is Between" is the operator for this date dashboard prompt.

Can you please let me know how to display the previous year values in a simple report.

Thanks in Advance

Ajith

Answers

  • Marco Poloni
    Marco Poloni Rank 1 - Community Starter

    Ajith,

    could you please elaborate a bit on what are trying to do?

    Do you want to know how to filter the report through a dashboard prompt showing the previous year data?

    Why the "is between" than? Is only to specify Month or day+month and the year is always the previous year?

    A couple of details or a screenshot would help to make a dedicated suggestion.

    Ciao

    MP

  • user3507711
    user3507711 Rank 1 - Community Starter

    Hi Marco,

    Thank you for your response.

    1). Through Date Dashboard prompt, while using the is "Between' Operator in the prompt. I will get it in below format as single column

    pastedImage_1.png

    2). Now I have separated this single column in to 2 separate columns by using the SUBSTRING, which is like below:

    Date 1 Edit Column Formula: Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,SUBSTRING('@{PBFROMTO}' FROM 1 FOR 10),'YYYY-MM-DD','MM/DD/YYYY')

    Date 2 Edit Column Formula: Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,SUBSTRING('@{PBFROMTO}' FROM 12 FOR 20),'YYYY-MM-DD','MM/DD/YYYY')

    pastedImage_2.png

    3)Based on this Date 1 & Date 2 separate columns, I need to display the previous year date values

    For achieving the previous year date values, I have written the below code in the separate columns (Date 3 & Date 4) to display previous year date values

    Date 3 Edit Column Formula:

      Evaluate('TO_CHAR(%1)' AS CHAR  ,TIMESTAMPADD(SQL_TSI_YEAR,-1,

      Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR , SUBSTRING('@{PBFROMTO}' FROM 1 FOR 10),'YYYY-MM-DD','MM/DD/YYYY')))

    Date 4 Edit Column Formula:

      Evaluate('TO_CHAR(%1)' AS CHAR  ,TIMESTAMPADD(SQL_TSI_YEAR,-1,

      Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR , SUBSTRING('@{PBFROMTO}' FROM 12 FOR 20),'YYYY-MM-DD','MM/DD/YYYY')))

    I am getting the below error, when i place the Date 3 column in the criteria:

    Not a valid month at OCI

    Your help is highly appreciated.

    Thanks in Advance

    Ajith