Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Year on Year (YoY) time series calculations - YoY growth/decline
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.
Answers
-
Hi,
Here's how you could use AGO function assuming you want to report on Enterprise Year.
AGO("CX - Opportunity Revenue Line"."Facts - Analytics Currency"."Opportunity Line Revenue", "CX - Opportunity Revenue Line"."Opportunity Revenue Line Expected Close Date"."Opportunity Revenue Line Expected Close Date Enterprise Calendar"."Enterprise Year", 1)
While creating the formula, this is how it would appear.
The level (Enterprise Year) is coming from the hieararchy columns for date as shown below. You can drag drop from here.
With this, it should work for you.
0 -
Hi Hitesh,
Thank you for the info. I've already tested the Ago function. The problem is, the function does not provide the correct values for our report.
0 -
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 blog0 -
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.
0 -
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.
0
