Try adding the script below to your Calendar script - adding another two preceeding loads to the top of the calendar script.
This will give you a FiscalYear & Period combined as 2015PP1, 2014PP13 , 2014PP12 etc
FiscalCalendar:
// Additions to get FiscalYear & Period sequence
Load *,
autonumber(FiscalYrPeriod, 'FiscalYrPeriod') as FiscalYrPeriodSeq; //Sequence No for FiscalYr & Period
Load *,
FiscalYear & Period AS FiscalYrPeriod; //FiscalYr & Period as YYYYPPNN
Then get the FiscalYrPeriodSeq for today's date by peeking out the value from a temp load after you have generated the calendar, using peek.
The variable vFiscalYrPeriod_TY will hold the sequence no for the period related to tofays's date.
tmpFYP:
load * resident FiscalCalendar where Date = today() ;
Let vFiscalYrPeriod_TY = peek('FiscalYrPeriodSeq', -1, 'tmpFYP') ;
drop table tmpFYP ;
Now you can use this variable in your set analysis expressions
To sum XXX for this payroll period use sum({<FiscalYrPeriodSeq={$(vFiscalYrPeriod_TY) }>} XXX)
To sum XXX for the prior payroll period use sum({<FiscalYrPeriodSeq={$(= $(vFiscalYrPeriod_TY) -1) }>} XXX)
To sum XXX for 13 periods prior use sum({<FiscalYrPeriodSeq={$(= $(vFiscalYrPeriod_TY) -13) }>} XXX)
Much easier than trying to calculate the periods from dates.
