Forum Discussion

ChristianW's avatar
ChristianW
Valued Contributor
4 years ago
Solved

A calc for a subset of dimension members

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

  • 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.

    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.

1 Reply

  • ChristianW's avatar
    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.

    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.