Discussions
Categories
 17.9K All Categories
 3.3K Industry Applications
 3.3K Intelligent Advisor
 60 Insurance
 534.5K OnPremises Infrastructure
 137.7K Analytics Software
 38.5K Application Development Software
 5.3K Cloud Platform
 109.1K Database Software
 17.5K Enterprise Manager
 8.8K Hardware
 70.8K Infrastructure Software
 105.1K Integration
 41.5K Security Software
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.
Best Answer

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 precalculated 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.
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 precalculated 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.

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

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.

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

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