Forum Discussion

Marco's avatar
Marco
Contributor II
4 months ago

How to Create a .base in a Step Using a Calculate

Hi everyone.

i want to make the following calculate, but i want to make the one of all the departments and all the projects, but when i put it as .base it gives me error in the calculate, then i would like to know how i can solve it or in what other way it should be made so that it works correctly.
an image of my step configuration

api.Data.Calculate("CB#[HLFPLN]:E#"& entity &":S#[" & scenario & "]:T#" & time & ":A#cra_639950:F#InCostsAllocation:O#Forms:I#11201_11207:U1#ALL_DEPARTMENTS.Base:U2#P0000:U3#LOCAL_DATA:U4#ALL_PROJECTS:U5#None:U6#None:U7#None:U8#None = (E#11201_11207:S#[ForecastAllocation]:T#" & time & ":A#cra_ALLOCPRCT:F#None:O#Import:I#11201_11257:U1#ALL_DEPARTMENTS.Base:U2#P0000:U3#LOCAL_DATA:U4#ALL_PROJECTS:U5#None:U6#None:U7#None:U8#None)*(E#11201_11207:S#[" & scenario & "]:T#" & time & ":A#cra_639950:F#None:O#Top:I#None:U1#ALL_DEPARTMENTS.Base:U2#P0000:U3#LOCAL_DATA:U4#ALL_PROJECTS:U5#None:U6#None:U7#None:U8#None)",False)

 

Finance
  • Henning's avatar
    Henning
    Valued Contributor II
    4 months ago

    Hi,

    you cannot use filters in a calculation like this. You will have to use a different way, out of which there are several. In your case you'll probably want to explore overload functions.

    You can find more information e.g. here:

    Writing Stored Calculations (onestream.com)

    And then here:

    Overload Function (onestream.com)

    You can also find an account-based example here in this forum, such as:

    Solved: U8 Member formula - OneStream Community (onestreamsoftware.com)

    • Marco's avatar
      Marco
      Contributor II
      4 months ago

      Hi Henning.

      check the other method, but I wanted to ask if the way I have it is correct, since what I want to modify is an exact section, as I have it in the question, but I don't know if this way it will work

      api.Data.Calculate("E#"& entity &":S#[" & scenario & "]:T#" & time & ":A#cra_639950:F#InCostsAllocation:O#Forms:I#11201_11207:U2#P0000:U3#LOCAL_DATA:U5#None:U6#None:U7#None:U8#None = (E#11201_11207:S#[ForecastAllocation]:T#" & time & ":A#cra_ALLOCPRCT:F#None:O#Import:I#11201_11257:U2#P0000:U3#LOCAL_DATA:U5#None:U6#None:U7#None:U8#None)",,,,"U1#ALL_DEPARTMENTS.Base",,,"U4#ALL_PROJECTS.Base",,,,,,,True

      or do I need something else to be able to do what I want to do?

      • Henning's avatar
        Henning
        Valued Contributor II
        4 months ago

        Hi, as rhankey said, not all dimensions make sense to be included in the target (left hand side of the equal sign). All data unit dimensions are naturally pulled / used from the point of view that the rule is executed in. Also, you had a comma missing. And the durable flag in the end, I removed this as this is often not needed.

        api.Data.Calculate("A#cra_639950:F#InCostsAllocation:O#Forms:I#11201_11207:U2#P0000:U3#LOCAL_DATA:U5#None:U6#None:U7#None:U8#None = (E#11201_11207:S#[ForecastAllocation]:T#" & time & ":A#cra_ALLOCPRCT:F#None:O#Import:I#11201_11257:U2#P0000:U3#LOCAL_DATA:U5#None:U6#None:U7#None:U8#None)",,,,,"U1#ALL_DEPARTMENTS.Base",,,"U4#ALL_PROJECTS.Base")

        There is plenty of material on writing rules which may help you such as training and a book, amongst other things. And the forum here is a fantastic resource too, of course, so thank you for participating!

  • rhankey's avatar
    rhankey
    Contributor
    4 months ago

    Use FilterMembers() to filter the source UD1, and remove UD1 from the destination intersection, as expansion/filter functions are not valid in any intersection definition.

    If U4#All_Projects is a parent member, then remove it from both the source and destination intersections, as you cannot write to a parent member.  If the formula must be applied to the All_Projects member, then specify that on the source side, but specify a U4# base member on the destination side.

    Why are you referencing so many dims in your intersections?  Time as an example is totally pointless to include.