Forum Stats

  • 3,770,705 Users
  • 2,253,155 Discussions
  • 7,875,553 Comments

Discussions

How can we calculate Rolling 13 Week Last Year ?

Steelbird
Steelbird Member Posts: 33 Red Ribbon

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:

Best Answer

  • Joel Acha
    Joel Acha 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.

Answers

  • Joel Acha
    Joel Acha 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.

  • Regis Peregrin
    Regis Peregrin 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

  • Steelbird
    Steelbird 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.

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

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

  • Steelbird
    Steelbird 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