Advanced formula to multiply two KFs within a version

[amritagoswami]

Hi All,

I am trying to write an advanced Formula which will work within the same Target version and calculate Salary = Headcount * Amount for each period where Amount and HC are maintained. User will select the date range for the Operation in

TargetYearFrom and TargetYearTo

My code is as follows:

CONFIG.TIME_HIERARCHY = FISCALYEAR

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

CONFIG.GENERATE_UNBOOKED_DATA = OFF

MEMBERSET [d/Measures] = ("Amount", "HC", "Salary") 

MEMBERSET [d/AUDITTRAIL] = (BASEMEMBER([d/AUDITTRAIL].[h/AuditHier], "ALL")) 

MEMBERSET [d/Date] = %TargetYearFrom% TO %TargetYearTo%

INTEGER @N = 0

FOREACH [d/Date]   

DATA([d/Measures] = "Salary", [d/AUDITTRAIL] = "HC_New", [d/Date] = NEXT(@N, "MONTH", %TargetYearFrom%)) = RESULTLOOKUP([d/Measures] = "Amount", [d/Date] = NEXT(@N, "MONTH", %TargetYearFrom%)) * RESULTLOOKUP([d/Measures] = "HC", [d/Date] = NEXT(@N, "MONTH", %TargetYearFrom%))   

    @N = @N + 1   

ENDFOR

However this code only produces Salary value for the first period in the memberset and does not loop over the other months. Please advise what is wrong with this code.

I need to run this calculation in a data action as the value needs to be further copied to another model and Story calculations are not available for Crossmodel Copy.

Thanks,

Amrita

[amritagoswami]

Issue is resolved. In this case the Audit trail for Measure - HC was set to CALC in First period and # for the later periods.

After changing the Audit trail to # for all periods we were able to get the desired results.

[Susanne_Helbig]

Dear amritagoswami,

maybe the following Blog helps you to solve your issue

Advanced Formulas – How they work

Best regards

Susanne

[MKreitlein]

Hello Amrita,

unfortunately I'm not the expert for data actions yet... if you know Integrated Planning Fox formulas it is quite difficult to understand this concept and create good or at least working data actions 🙂

However, here is a simple example of one of my formulas:

MEMBERSET [d/Date] ="202102" TO "202112"
MEMBERSET [d/Measure] = "PRICE"

FOREACH [d/Date]
    Data() = RESULTLOOKUP([d/Date] = "202101", [d/Measure] = "PRICE") 
ENDFOR 

What I'm not sure about your coding is ... you try to loop over 2 years, which you defined in the memberset [d/Date]... but your data is stored on a monthly level.

I think you should try to loop via the months...

Hope this helps you to proceed one step further.

BR, Martin

[amritagoswami]

The names TargetYearFrom and TargetYearTo are a misnormer in my case. These variable hold month level values.