Forum Stats

  • 3,752,193 Users
  • 2,250,468 Discussions
  • 7,867,747 Comments

Discussions

Trailing Twelve Months(PBCS) - Without SubVars

User_XUC2I
User_XUC2I Member Posts: 45 Green Ribbon
edited Dec 16, 2020 10:44PM in Planning and Budgeting

Hi,

Unusual request as I need TTM but needs to be dynamic no matter the year as client actually does variance reporting on Current Year TTM vs. Prior Year TTM

I got this to work, but it's ugly and thinking there has to be a better way


So a colleague of mine and I were tinkering and we can't figure out why this snippet of code retrieves in SmartView perfectly:

IF(@ISMBR(@iDescendants("Balance Sheet")) AND (@ISLEV("Period",0)))

"MTD";

ELSEIF (@ISMBR(@iDescendants("Income Statement")))

   IF(@ISMBR("P10"))

@SUMRANGE("MTD"->"FY19",@CURRMBRRANGE("Period",LEV,0,1,2));  

ENDIF

ENDIF



However this when we retrieve doesn't work and error is 'The Form is Invalid'. Change formula back and same form retrieves fine.

IF(@ISMBR(@iDescendants("Balance Sheet")) AND (@ISLEV("Period",0)))

"MTD";

ELSEIF (@ISMBR(@iDescendants("Income Statement")))

   IF(@ISMBR("P10"))

@SUMRANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),@CURRMBRRANGE("Period",LEV,0,1,2));  

ENDIF

ENDIF


Thanks for any help, we are trying to do TTM in BSO without SubVars and avoiding my long attached calc.


Appreciate any responses!!!

Answers