Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trailing Twelve Months(PBCS) - Without SubVars

User_XUC2IDec 16 2020 — edited Dec 16 2020

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
TTM_PBCS_Dynamic.txt (5.26 KB)
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!!!

Comments