Rolling 12 months - different approach?

[#1]

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

FISCYRPER,

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?

[jonathandienst]

Hi

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.

HTH

Jonathan