Trailing fiscal periods, Sage 100 Excel 2016

Hi All,

I have a report Sage 100 Financial Report I am creating that I have a problem with.  I enter the parameter month/year to run.  In this case, 2/2019. (fiscal period 4)  I want the previous 11 months trend for Cost of Goods sold. 

Note that our fiscal months are not calendar.    Period 4 is February.  

D3 is the fiscal year and D4 is the fiscal month.  I don't know how to program the formula for the trailing fiscal periods.  Using the formula =GLActual($A4,Parameters!$D$3,Parameters!$D$4-1,"ABC") (Note I am subtracting 1 from the fiscal period.  This works for January (4-1=3) and December (4-2=2) and November (4-3=1)   But when I get to November (4-4=0) and beyond I get into trouble as the result goes to 0 and negative rather than 12, 11,10 etc.   Also how do I program the formula to switch to 2018 when it reaches December (Fiscal Period 2) ?

 

In summary, I don't know how to ask the formula to give me previous months.      

I assume I can use an If Statement to add 12 back in when the value goes to zero.  That would be a solution at least for the months.  But I was hoping (somehow) that there is a way Sage Intelligence has a way to pull the fiscal month and year based on the Month/Year parameters I entered.

Thank you,

Paula Park

Windsormill