Oracle Analytics and AI

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.

Find more posts tagged with

OBIEE

Accepted answers

All comments

Joel Acha

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.

Regis Peregrin

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

Steelbird

It did not worked out @Regis Peregin .It does not allow to add like that The starting Point of Period rolling function is larger than the End point.

Regis Peregrin

PeriodRolling(measure,-64,-52) -64 is less than -52.

Steelbird

I will try this one as well and we have approached another way like adding year ago wid in day dimension.

@Regis Peregrin Thank you