Year on Year (YoY) time series calculations - YoY growth/decline

Question

I'd like to know if the  AGO and TODATE time series functions are working by design.  Here are the details:

We are planning toadded in our dashboard a Quarterly or Yearly Trend which would show the Quarter on Quarter (QoQ)  or Year on Year(YoY)  growth or decline.

Sales YoY growth/Decline =  (Sales Current Year - Sales Previous Year)/Sales Previous Year

OR if the 2023 is the year we'd like to check:

Sales YoY growth/Decline =  (Sales for 2023  - Sales for 2022)/Sales for 2022

Saw some article that discussed how "time series functions" can be used for the QoQ and YoY calculations using AGO and YTD time series functions. Here's the link to the blog:

Oracle Analytics Cloud & Server

Time Travel Made Easy: Working with Time Series Functions in Oracle Analytics

However, included in the "Known Issues for Oracle Fusion ERP Analytics Common" is the

"Aggregation of YTD Metrics Is Incorrect in Certain Scenarios" from this link: OracleCloud Known Issues for Oracle Fusion Data Intelligence

Having error in "YTD or Year to Date metrics" might be related to our current issue where the AGO function does not seem to work.

For example, when wetry to create a new calculation:

YearAgo Sales  = AGO(Sales,Year, 1)

Ago Time Series function definition:

The AGO function calculates the aggregated value from the current time to a specified time period.

Say the Year that we are checking is 2023 so the 1 year ago is 2022:

2023 Sales  - 1.5 M USD

2023 Year Ago Sales  - 1.5 M USD

2022 Sales - 2M USD

2022 Year  Ago Sales - 2M USD

Note: These are just arbitrary number just for illustration.

No matter which year we check, the "Year Ago Sales" does not reflect the correct values:

Should be:

2023 Year Ago Sales= 2022 Sales  =  2M USD

My understanding is that the AGO function indicates the aggregated value from a year ago, if year is the time level selected.

If the AGO time series function is not the correction function to use to the Year on Year (YoY) growth or decline, please let me know your suggestion on which function or calculation to use.

https://blogs.oracle.com/analytics/post/time-travel-made-easy-working-with-time-series-functions-in-oracle-analytics

User_OS6BE · Answer

Hitesh Patel-Oracle:

In your suggestion, if you try to valid this calculation, what is the result?

Followed your suggestion on my end but it give a lot of different errors.

First error is this one:

Error message:

"Function AGO requires at least one measure column in its first argument"

I've confirmed that the measure is numeric type since I was able to aggregate in different ways. Please advise.

User_OS6BE · Answer

I've filed SR ticket before I even posted this question. Have provided all the information needed by support but the action is pending on their end.

Hitesh Patel-Oracle · Answer

I have tested it for a couple of reports and it works.

If none of your facts are working, I would suggest providing more information or raise a SR if you think its not working as expected/documented in the blog