Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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