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