1 Reply Latest reply: Mar 17, 2014 1:38 AM by Jonathan Dienst RSS

    Rolling 12 months - different approach?

    Richard Blakemore

      I am dealing with an accounting system that is fiscal year/period based, and I would like to create rolling 12 month figures.


      I have read everything so far, and wondered if another approach could work.


      I have combined my Fiscal Year/Period data into one field using dual(FISCALYR &'-'& FISCALPERD,'####-##') as FISCYRPER,

      and then auto-numbered this field twice later on as below:

      Load Distinct


      AutoNumber(FISCYRPER) as RollingPeriodEnd,

      AutoNumber(FISCYRPER)-11 as Rolling12PeriodStart,

      Resident Transactions

      Where FISCALPERD < '13'

      Order by FISCYRPER;


      The aim is to sum my results using set analysis on the basis of RollingPeriodEnd being greater than or equal to Rolling12PeriodStart, and less than or equal to RollingPeriodEnd e.g.

      sum({<Rolling12PeriodStart = {'>=$(=Only(RollingPeriodEnd))<=$(=Only(Rolling12PeriodStart))'}>}CR-DR)  or


      sum({<Rolling12PeriodStart = {'>=$(=max(RollingPeriodEnd))<=$(=max(Rolling12PeriodStart))'}>}CR-DR)

      Unfortunately, it is only summing the results for the particular period selected.

      Any ideas?




        • Re: Rolling 12 months - different approach?
          Jonathan Dienst



          I would not use Autonumber for this as it numbers values as they are encountered and not necessarily in the order you expect. Rather use the expression (in the same place you load FISCALYR and FISCALPERD):


               FISCALYR*12 + FISCALPERD As MonthNo


          Then use


               Sum({<MonthNo = {"<=$(=Max(MonthNo)) >=$(=Max(MonthNo) - 12)"}, FISCALYR=, FISCALPERD=>} RangeSum(CR, -DR))


          Note this cannot be used in a table using FISCALYR, FISCALPERD or MonthNo as dimensions.