SAC-P Account Member Formula Scope Restriction based on Version and Date

[Insan28]

Hi,

I want to build a  model with many different KPI's created on account dimension. Some of these KPI's are coming from source system while some are uploaded via excel for actual. I want to use on the fly formula for calculated KPI's for only planning months and I don't want to calculate anything for actualized months but rather take it from source system or excel directly.

Can I restrict the scope of the calculation of on the fly account formulas and make it calculated only for planning months? At the same time I need actualized months' data from source?

example  of one such calculated Acc 62000xxx = SUM (CALCULATED ACC1+CALCULATED ACC2+CALCULATED ACC3+((ACTUALS OF ACC4 PREV YEAR /  ACTUALS OF ACC5 PREV YEAR)*CALCULATED ACC6)/12)

CALCULATED ACC refers to account where i want to use formula for Plan version.

This is one such account where i need to create a calculated formula in Account Dimension but again it should only be applicable to plan version because i will directly get the value for Acc 62000xxx for actuals from import. Also, I am not aware of how i can restrict actuals of previous year at Account formula level please let me know if this is possible also.

In this case my challenge is even if i create two sub accounts under parent node 62000xxx ->62000xxx_A and 62000xxx_P and write the calculated formula on _P accounts, But when i use this hierarchy in my table where i will have version in column,  _P accounts will also show some value under Actual column because in my case my formula refers to actuals value as well.

Also, If i go with Data Action method to calculate all the accounts in advanced script instead of Account Formula, I am worried on performance and complexity because i have 30+ such accounts where i need formula on accounts for plan data. Plus this calculation have to be performed per cost center.

Actually it is like user want to enter value for only the lowest level 2 accounts and rest everything should be calculated for plan months . To create a replica of how it is at the moment in excel where everything is calculated on the fly based on the formula applied at cell level.

Please can someone provide your thoughts on how this challenge should be tackled.

Thanks in advance.

Sana