Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Rolling 12 months - different approach?

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?

1 Reply
MVP
MVP

Re: Rolling 12 months - different approach?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser