A calc for a subset of dimension members

ChristianW
Valued Contributor

Hi all

How can I run a calculation for a subset of dimension members. Something like all base members of member Top, but not the none member, or just the members with text1 attribute = "Calculate", or just a comma separated list of members.

Do I need a for loop?

Cheers

1 ACCEPTED SOLUTION

ChristianW
Valued Contributor

No, you don't need a for loop for this, you can use build in functionality of the api.data.calculate function.

 

api.Data.Calculate(formula, accountFilter, flowFilter, originFilter, icFilter, ud1Filter, ud2Filter, ud3Filter, ud4Filter, ud5Filter, ud6Filter, ud7Filter, ud8Filter, onEvalDataBuffer, userState, isDurableCalculatedData)

 

Every variable in this function, with the exception of the formula one, is optional. You can control with the filter variables the members the calculation will be executed for.

image.png

Here are some samples:

 

UD2: All base members of Top, but not None

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,"U2#Top.Base.Remove(None)")

 

The UD3 members with text1 attribute = "Calculate" :

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,,"U3#Americas.Base.Where(Text1 = Calculate)")

 

A comma separated list of UD1 members:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,"UD1#Sales, UD1#None")

 

You can also use them all together:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,"UD1#Sales, UD1None","U2#Top.Base.Remove(None)","U3#Americas.Base.Where(Text1 = Calculate)")

 

or combine filters with a comma (inside the quote):

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,"U2#Clubs.Base,U2#Balls.Base")

 

Even complex filters are supported:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,,"U3#Americas.Children.Where(Text1 = Calculate).base.remove(None)")

 

This feature of api.data.calculate should eliminate the need for for loops to a minimum.

View solution in original post

1 REPLY 1

ChristianW
Valued Contributor

No, you don't need a for loop for this, you can use build in functionality of the api.data.calculate function.

 

api.Data.Calculate(formula, accountFilter, flowFilter, originFilter, icFilter, ud1Filter, ud2Filter, ud3Filter, ud4Filter, ud5Filter, ud6Filter, ud7Filter, ud8Filter, onEvalDataBuffer, userState, isDurableCalculatedData)

 

Every variable in this function, with the exception of the formula one, is optional. You can control with the filter variables the members the calculation will be executed for.

image.png

Here are some samples:

 

UD2: All base members of Top, but not None

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,"U2#Top.Base.Remove(None)")

 

The UD3 members with text1 attribute = "Calculate" :

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,,"U3#Americas.Base.Where(Text1 = Calculate)")

 

A comma separated list of UD1 members:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,"UD1#Sales, UD1#None")

 

You can also use them all together:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,"UD1#Sales, UD1None","U2#Top.Base.Remove(None)","U3#Americas.Base.Where(Text1 = Calculate)")

 

or combine filters with a comma (inside the quote):

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,"U2#Clubs.Base,U2#Balls.Base")

 

Even complex filters are supported:

 

api.Data.Calculate("A#Profit = A#Income - A#Expense",,,,,,,"U3#Americas.Children.Where(Text1 = Calculate).base.remove(None)")

 

This feature of api.data.calculate should eliminate the need for for loops to a minimum.