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

Time series functions without date hierarchy

Accepted answer
55
Views
5
Comments
User_WQKHO
User_WQKHO Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

Hello all,

I am trying to use functions like AGO() and TODATE() directly within DV calculations (not within the RPD) on direct database connections or "flat" datasets, but I am unable to do so because there is no Date Hierarchy. Is there any way to do this? Perhaps there would be a way to define the date hierarchy during the data preparation steps?

Thank you

Tagged:

Best Answer

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited Oct 9, 2024 3:27PM

    Hi,

    There is a concept of datasets hierarchies, that I imagine will allow you to design what you ask. But it is a future feature listed in the 2024 Q3 roadmap (on the right of the page)

    https://community.oracle.com/products/oracleanalytics/discussion/22959/oracle-analytics-product-roadmap-q3-2024
  • User_WQKHO
    User_WQKHO Rank 3 - Community Apprentice

    Thank you @Gianni Ceresa! The last video has worked, at least for a calendar year. I tried for a fiscal year using a separate dimension and it's throwing an error. Any idea?

    image.png
  • Gianni Ceresa
    Gianni Ceresa Mod
    edited Oct 9, 2024 4:16PM

    I must admit I never used it on dataset, because it's a "wrong implementation breaking the concept of data modelling" for me (time series should work on hierarchies, because that's how you can be sure of what query is being executed and what the meaning of that calculation is).

    My understanding is that you do not create it as to date for fiscal year, but just create the calculation as todate(Monthly Rate (CAD), YEAR) . This give you a dynamic calculation that will work at the year level depending on what date column you have in your visualization. I didn't try to see what happen if you have 2 dates, it will either give you an error or work just on the first one.

    The YEAR reference isn't a column, it will reference the "virtual" Year representation of the date column.

    image.png

    I just tested in OAS 2024, and it does indeed work like that: a single calculation giving me todate(year) for ship date and for order date, just because I have 2 different visualizations with the 2 dates.

  • User_WQKHO
    User_WQKHO Rank 3 - Community Apprentice

    Thanks @Gianni Ceresa for the explanation, it did indeed work the way you've mentioned it. The reason I'm trying with Fiscal Year is because it's offset, so within our company, even though the calendar year is 2023 it could be Fiscal Year 2024, making the TODATE() incorrect. I'll try to work around this somehow!