nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Time series functions without date hierarchy

User_WQKHO

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

Find more posts tagged with

Function

Data Modelling and Datasets

Data Visualization

Dataset

Accepted answers

Gianni Ceresa

In the meantime, you should be able to do it without a time hierarchy:

https://www.youtube.com/watch?v=Bps3fuNgtw0&ab_channel=OracleAnalytics

All comments

Gianni Ceresa

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

Gianni Ceresa

In the meantime, you should be able to do it without a time hierarchy:

https://www.youtube.com/watch?v=Bps3fuNgtw0&ab_channel=OracleAnalytics

User_WQKHO

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?

Gianni Ceresa

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.

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

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!