## Forum Stats

• 3,770,705 Users
• 2,253,155 Discussions

Discussions

# How can we calculate Rolling 13 Week Last Year ?

Member Posts: 33 Red Ribbon
edited Sep 19, 2020 2:45AM

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.

Tagged:

• Member Posts: 1,370 Gold Trophy
edited Sep 15, 2020 8:23AM Accepted 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 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.

• Member Posts: 1,370 Gold Trophy
edited Sep 15, 2020 8:23AM Accepted 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 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.

• Member Posts: 4 Red Ribbon
edited Sep 18, 2020 12:54PM

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

• Member Posts: 33 Red Ribbon
edited Sep 18, 2020 1:02PM

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.

• Member Posts: 4 Red Ribbon
edited Sep 18, 2020 2:07PM

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

• Member Posts: 33 Red Ribbon
edited Sep 19, 2020 2:45AM

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