Forum Discussion

Sergey's avatar
Sergey
Contributor III
3 years ago

DynamicCalc : get data from siblings (or parents)

Hello,

 

I have a use case where I need to get data from siblings (or parents) for a dynamic calc.

Entities A,B are part of the same parent EntityC.

Cost1 / Entity A : 1000

Cost1 / Entity B : 500

Cost2 / Entity A : 2000

Cost2 / Entity B : 1000

The calculation of the KPI should be as follow for each entity : (0.5*Cost1 + Cost2) / ( 0.5*Cost1:EntityC + Cost1:EntityC).

or : (0.5*Cost1 + Cost2) / ( 0.5*Cost1:EntityA+EntityB + Cost1:EntityA+EntityB). (it's the same really)

Just to give more fun to it, this formula should be dynamic, as I have multiple entities and multiple parent...

 

I'm looking for a formula that would help me achieve this ?

 

Regards,

  • You can retreive the parent of the POV Entity using the api.Member.sGetParents function. This returns a list of all parents so you can use .First to grab the first one. If base entities have multiple parents via alternate hierarchies, you need to be careful which one gets returned as it can be unpredictable which one OneStream lists first (I believe it is order in which the parents are created). Here is some script:

     

     

    Dim povEntityId As Integer = api.Pov.Entity.MemberId
    Dim povEntityDimPk As DimPk= api.Pov.Entitydim.DimPk
    Dim entityParent As List (Of Member) = api.Members.GetParents(povEntityDimPk, povEntityId, False)
    Dim firstEntityParent As Member = entityParent.First
    
    Return api.Data.GetDataCell("Divide((0.5 * A#Cost1 + A#Cost2), ( 0.5 * A#Cost1:E#" & firstEntityParent.Name & " + A#Cost2:E#" & firstEntityParent.Name & "))")

     

     

    Something to consider is that the Parent Entity may not be consolidated or aggregated to reflect recent changes to the base entities. You can use the GetEntityAggregationDataCell function to get the Cost data for the parent and it will run the parent entity aggregation at execution of the Dynamic Calc.

     

     

    Dim povEntityId As Integer = api.Pov.Entity.MemberId
    Dim povEntityDimPk As DimPk= api.Pov.Entitydim.DimPk
    Dim entityParent As List (Of Member) = api.Members.GetParents(povEntityDimPk, povEntityId, False)
    Dim firstEntityParent As Member = entityParent.First
    
    Dim parentAggCost1 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent & ":A#Cost1")
    Dim parentAggCost2 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent & ":A#Cost2")
    
    Return api.Data.GetDataCell("Divide((0.5 * A#Cost1 + A#Cost2), (0.5 * " & parentAggCost1.CellAmount & " + " & parentAggCost2.CellAmount & "))")

     

  • TheJonG's avatar
    TheJonG
    Contributor III

    You can retreive the parent of the POV Entity using the api.Member.sGetParents function. This returns a list of all parents so you can use .First to grab the first one. If base entities have multiple parents via alternate hierarchies, you need to be careful which one gets returned as it can be unpredictable which one OneStream lists first (I believe it is order in which the parents are created). Here is some script:

     

     

    Dim povEntityId As Integer = api.Pov.Entity.MemberId
    Dim povEntityDimPk As DimPk= api.Pov.Entitydim.DimPk
    Dim entityParent As List (Of Member) = api.Members.GetParents(povEntityDimPk, povEntityId, False)
    Dim firstEntityParent As Member = entityParent.First
    
    Return api.Data.GetDataCell("Divide((0.5 * A#Cost1 + A#Cost2), ( 0.5 * A#Cost1:E#" & firstEntityParent.Name & " + A#Cost2:E#" & firstEntityParent.Name & "))")

     

     

    Something to consider is that the Parent Entity may not be consolidated or aggregated to reflect recent changes to the base entities. You can use the GetEntityAggregationDataCell function to get the Cost data for the parent and it will run the parent entity aggregation at execution of the Dynamic Calc.

     

     

    Dim povEntityId As Integer = api.Pov.Entity.MemberId
    Dim povEntityDimPk As DimPk= api.Pov.Entitydim.DimPk
    Dim entityParent As List (Of Member) = api.Members.GetParents(povEntityDimPk, povEntityId, False)
    Dim firstEntityParent As Member = entityParent.First
    
    Dim parentAggCost1 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent & ":A#Cost1")
    Dim parentAggCost2 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent & ":A#Cost2")
    
    Return api.Data.GetDataCell("Divide((0.5 * A#Cost1 + A#Cost2), (0.5 * " & parentAggCost1.CellAmount & " + " & parentAggCost2.CellAmount & "))")

     

    • Sergey's avatar
      Sergey
      Contributor III

      Hi,

      I'm trying to apply this formula to the dynamic calc account however I get some messages when I validate the formula:

      Unable to compile formula.

      1) Error at line 29: Operator '&' is not defined for types 'String' and 'Member'.

      2) Error at line 30: Operator '&' is not defined for types 'String' and 'Member'.

       

      This is probably just a VB.Net error message so I'll have a look into it.

      Many thanks !

      • Sergey's avatar
        Sergey
        Contributor III

        Adding ".Name" did the trick there !

         

        Dim povEntityId As Integer = api.Pov.Entity.MemberId
        Dim povEntityDimPk As DimPk= api.Pov.Entitydim.DimPk
        Dim entityParent As List (Of Member) = api.Members.GetParents(povEntityDimPk, povEntityId, False)
        Dim firstEntityParent As Member = entityParent.First
        
        Dim parentAggCost1 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent.Name & ":A#Cost1")
        Dim parentAggCost2 As DataCell = api.Functions.GetEntityAggregationDataCell("E#" & firstEntityParent.Name & ":A#Cost2")
        
        Return api.Data.GetDataCell("Divide((0.5 * A#Cost1 + A#Cost2), (0.5 * " & parentAggCost1.CellAmount & " + " & parentAggCost2.CellAmount & "))")

         

  • Sergey's avatar
    Sergey
    Contributor III

    Hello Davy,

     

    Here's the configuration I used for the Dynamic Calc member :

     

     

    Best Regards,

  • Davy's avatar
    Davy
    Contributor

    Can someone please give me a screen shot of the settings of this  account member that has Dynamic calc ?  Or point me to section in documentation to find them.