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 Caluculate the current year revenue and previous year revenue

Received Response
1
Views
4
Comments
3061821
3061821 Rank 2 - Community Beginner
edited Aug 13, 2024 6:12PM in Oracle Analytics Cloud and Server

Hi,

Can anyone help me with the below criteria:

For a product I have to show the revenue for current year(i.e. 01-01-2016 to 31-08-2016) and for previous year(i.e. 01-01-2015 to 31-08-2015 with out using the dashboard prompts.

Your help is highly appreciated.

Thanks

Mithun

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Have a look at time series functions, TODATE and AGO.

    Actually TODATE isn't needed as you can take the revenu directly at the year level and on 2016 you have the current year revenue and the same using AGO will give you 2015.

  • Shams Abbasi
    Shams Abbasi Rank 5 - Community Champion

    Try putting these sql in your edit formula and update if you are getting proper results

    Previous Year

    FILTER("Revenue Column Here" USING ("DATE Column" Between  TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) -364,CURRENT_Date) and TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE),CURRENT_Date) ))

    Current Year

    FILTER("Revenue Column Here" USING ("Date Column" BETWEEN  TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) +1,CURRENT_Date) and TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) +366,CURRENT_Date) ))

    Although i am not sure what will happen for leap year

  • Gianni Ceresa
    Gianni Ceresa Mod

    You can also simple add a filter to your analysis with your "year" column between YEAR(CURRENT_DATE)-1 AND YEAR(CURRENT_DATE) and use a pivot view ....

    Actually there are many ways as that's the way OBIEE works: if you have a correct model with a proper time dimension by default your figures are aggregated at the year level. So no need of complication function (giving you a really bad performance).

  • 3061821
    3061821 Rank 2 - Community Beginner

    Hi All,

    Thank you for your responses.

    i will check it out and get back to you.

    I will check whether it is fetching the revenue for the current year from 01-01-2016 to 31-08-2016 and previous year from 01-01-2015 to 31-08-2015.

    Thanks

    Mithun