01-10-2022 03:05 PM - last edited on 05-24-2023 01:54 PM by JackLacava
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
01-10-2022 03:06 PM
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 “”.
01-10-2022 03:07 PM
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.
01-10-2022 03:07 PM
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
01-11-2022 05:17 AM - edited 01-11-2022 05:18 AM
If you have a default text value, the loop only stops, when running into an error, because there is no prior period.
01-11-2022 09:06 AM
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: