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
- 60 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
How can we calculate Rolling 13 Week Last Year ?
Hi Team,
How can we calculate Rolling 13 Week LY sales ?
I am calculating Rolling 13 weeks Sales as - PeriodRolling("Mac"."Fact - Tex"."Actuals", -12, 0) . This is working as expected.
Applying Ago function on top of it on year as - Ago(PeriodRolling("Mac"."Fact - Tex"."Actuals", -12, 0),"Mac"."Date - Year",1)
But the report is giving error as [nQSError: 42023] Illegal aggregate nesting in query. (HY000)
Thank you,
Steel.
Answers
-
As it states, you can't nest aggregate time functions. Your best bet (if possible) would be to calculate this value either as part of your ETL or store it pre-calculated in your data source and pick it up in your ETL. Once in your ETL, you can load this into your fact table as another fact.
0 -
Hello Steelbird,
Re: How can we calculate Rolling 13 Week LY sales ?
Period Rolling should be able to handle this.
PeriodRolling(measure,x,y)
1) Measure is any measure you want to aggregate
2) X is the period to start
- a rolling 13 weeks would be the previous 12 weeks plus the current month,
- the starting period would -12
3) Y is the period to end
- If you want to end at the current week, it is 0
- for a rolling 13 weeks that starts backward 12 weeks and ends with the current week for a total of 13 weeks : PeriodRolling(measure,-12,0)
- for a rolling 13 weeks for a year ago, it needs to starts with the same week as current only in the last year (subtract 52) and end with current period only in the last year (subtract 52): PeriodRolling(measure,-64,-52) (I couldn't test this because I don't have weeks in my hierarchy)
Remember that Period Rolling, unlike AGO, does not specify the time hierarchy increment level. We are using weeks in this explanation because you mentioned weeks. It uses whatever level you put in your query.
Hope this helps.
Revised 9/18/20
0 -
PeriodRolling(measure,-64,-52) -64 is less than -52.
0
