Planning

Get Involved. Join the Conversation.

Topic

    Joe Steinbrenner
    Cleardata rule - How to exclude certain accounts?
    Topic posted June 1, 2018 by Joe SteinbrennerSilver Medal: 2,000+ Points, tagged Financial Planning 
    79 Views, 3 Comments
    Title:
    Cleardata rule - How to exclude certain accounts?
    Content:

    In our Forecast Cube we are loading the Revenue Forecast to several  different regions (i.e., REV_Region1,REV_Region2, REV_Region3, REV_Region4), these revenue accounts have no associated actuals, but I still want to keep the data for these accounts. Before loading the actuals we clear the Scenario. How can I exclude certain accounts from getting cleared out. The rule is below.

     

    Code Snippet:

    Comment

     

    • Justin Thormodsgard

      I would use the @LIST function to create the list of accounts you want to protect, then remove them from the clear with the @REMOVE function.   It's been a while since I've used this setup, but I would think it would look something like this:

      Fix (USD, {RTP_Years},{HypFcst_Mth1}:{HypFcst_Mth2},{HypFcst_Versions},  @REMOVE(@IDESCENDANTS("Account SUMMARY"), @LIST(Member1, Member2, Member3))

      I'm almost positive that what I typed above will have a syntax problem, so please check out the essbase documentation to check it.
      Hope that helps -Justin

       

    • Mark Rinaldi

      Yes to @REMOVE and @LIST but assuming your parent Accounts are all set to Dynamic Calc, I wouldn't use @IDESCENDANTS("Account SUMMARY).  I would use @RELATIVE("Account SUMMARY",0).

      So,

      FIX(USD, {RTP_Years},{HypFcst_Mth1}:{HypFcst_Mth2},{HypFcst_Versions},  @REMOVE(@RELATIVE("Account SUMMARY",0), @LIST(Member1, Member2, Member3)))

      With 3 closing parentheses:  1 for FIX(, another for @REMOVE(, and another for @LIST(.

    • Mark Rinaldi

      Also, if the Accounts you wish to exclude are conveniently in the same hierarchy and you with to exclude them all, you can do @REMOVE(@RELATIVE("Account SUMMARY",0),@RELATIVE("Group to Exclude",0)).

      There are plenty of ways to exclude lists from excluded groups.  You can also leverage UDAs, etc.