Calc’d A# with ‘VaryByTimeId ’

[BarryC]

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

[MichaelSBrokaw]

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]

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]

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]

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

[Koemets]

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.