Forum Discussion

BarryC's avatar
BarryC
New Contributor II
3 years ago

Calc’d A# with ‘VaryByTimeId ’

SOURCE: ONESTREAM CHAMPIONS

Hi all,

I’m largely self taught with vb, but have hit a wall with this, and thought I’d share here rather than ask the OS helpdesk, as it seems to be something that could prove useful to others. I’m trying to write a calculated account which refers an entity Text field, using multiple values varied by time in the calculation.

We set a value for Cost of production for certain categories of products (spares and accessories) by % on a varied by time basis on the entity. This can vary from year to year (in theory could vary in year)

Specifically, I want to find the value that is valid at prior year end and subtract the current value from that. The value may have been set earlier M12 may conain no value but M8 of the same prior year does, for example.

I’ve introduced a loop to try and go back through periods until a value is found, but can’t see whether that’s working as I’m still getting an error message.

Dim PeriodPOV As Integer = api.POV.Time.MemberId
Dim PriorPeriod As Integer = api.Time.GetLastPeriodInPriorYear() 
Dim dCurrValue As Decimal = api.Entity.Text(EntityID,2,,PeriodPOV)
Dim dPriorValue As Decimal = api.Entity.Text(EntityID,2,,PriorPeriod)

Dim ValueChk As Boolean = True
Dim PriorValueChk As Integer = dPriorDDU
Dim PriorPeriodChk As Integer = ""

Do While ValueChk = True
PriorPeriodChk = api.Time.GetPriorPeriod(PriorPeriod)
PriorChk = api.Entity.Text(EntityID,2,PriorPeriodChk)
If PriorChk = 0 Then
ValueChk = False
End If
Loop

api.data.Calculate("(A#BALSHEETACCOUNT:U7#SOURCE_ADJ:UDX#" & _
"= (A#MEMOACCOUNT:U7#SOURCE_TOP:UDX# * 1 - ( 1 / (1 + " & dCurrValue & “))” & _
"- (A#MEMOACCOUNT:U7#SOURCE_TOP:UDX# * 1 - (1 / (1 + " & dPriorValue & “)))”)

Error message is that “Conversion from string “” to type ‘Integer’ is not valid”. Is the issue in the format of dCurrValue and dPriorValue and that I’m pulling those through as strings when they should be integers/decimals? Or am I missing something else?

Many thanks in advance.

Barry

Finance
  • MichaelSBrokaw's avatar
    MichaelSBrokaw
    New Contributor III
    3 years ago

    Dim PriorPeriodChk As Integer = "" would result in this error as empty string ("") cannot be converted to a valid Integer value. Try setting initial value as 0 instead of “”.

    • Cosimo's avatar
      Cosimo
      Contributor II
      3 years ago

      Also want to add that UDX# in your calc formula is not valid. In the OS reference guide, they mention UDX though the X represents a value from 1 to 8 (i.e. dimensions UD1 to UD8).

      You’ll most likely get an error once you get to the api.data.calculate line.

      • BarryC's avatar
        BarryC
        New Contributor II
        3 years ago

        Thanks, Michael, and yes Cosimo I used UDX to represent UD’s in string rather than list them out (as per OS reference guide). Here at Dyson we use all eight UD’s in our main cube and they wouldn’t have added anything to the query 

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    3 years ago

    If you have a default text value, the loop only stops, when running into an error, because there is no prior period.

  • Koemets's avatar
    Koemets
    Contributor
    3 years ago

    Ok, from business logic point, if the value in question is in one of the periods in prior year (and you don't know which one), then last year's M12 V#YTD should be greater than zero and should be equal to said value. Does it make sense?

     

    Notes for the rest:

    • As in was already said, you never set initial value to integer as "", it should be 0
    • As it was already said, your loop is virtually infinite, so don't do while, limit it one way or another. You can run for loop, say, for set number of iterations and make your while condition an if condition, if it comes to it.
    • You do not want to use regular divide sign "/" in your calculations. use OS DIVIDE() function, that mitigates division by zero.