Skip to main content
P
PieterDR
Newcomer
September 13, 2023
Solved

Sum two columns in a datasource

  • September 13, 2023
  • 3 replies
  • 0 views

Hi all, 

In my delimited upload file (trail balance) there are two columns that include amounts: beginning balance and the sum of periodic movements. The sum of the two is the number I want uploaded in the import stage. 

Is there a way to make OneStream add an additional calculated column (source dimension) to the datasource with the sum of the two columns (so prior to transformation / validate / process)? 

Any help would be much appreciated!

Best answer by ChristianW

You will find samples in Snippets:

ChristianW_0-1694684114665.png

If snippets are not available with your installation, you can find them on the solution exchange. An administrator needs to install them for you and then they will be available on all applications.

Of cause, you need to summ the columns instead of concatenating them.

3 replies

C
OneStream Employee
ChristianWOneStream Employee
OneStream Employee
September 13, 2023

You can use a matrix load setup for this (e.g. flow member PerBegBal and Periodic) and then sum it up with the transformation rule (e.g. PerBegBal -> None and Periodic -> None).

P
PieterDRAuthor
Newcomer
September 14, 2023

Thanks! That is basically what I am doing now in a separate dimension (both columns are set up as UD4Root and the UD4 dimension calculates the sum).

However I am also working with derivative (source) rules:
Input account X is OneStream account 1 if amount > 0, and is OneStream account 2 if amount < 0.

The current order of the upload to transformation first applies the derivative and than applies the transformation.

The result is that if beginning balance (BB) is <0, and periodic movements PM are >0, the beginning balance will be transformed to OneStream account 2, and the movements to account 1, effectively blowing up my balance sheet. 
I want OneStream to look at the total of BB and PM first, and then apply the derivative to that total.

C
CuroLewis
Newcomer
September 13, 2023

I think prior to transformation you'd use the data source member target Logical Operator which can be either None, a BR or Complex Expression. When you select either BR or Complex Expression your logic goes in the Logical Expression. 

 

 

P
PieterDRAuthor
Newcomer
September 14, 2023

Thanks, I was thinking towards that direction myself as well. I have very basic knowledge of VBA: any chance you could help me with the code?

C
OneStream Employee
ChristianWOneStream EmployeeAnswer
OneStream Employee
September 14, 2023

You will find samples in Snippets:

ChristianW_0-1694684114665.png

If snippets are not available with your installation, you can find them on the solution exchange. An administrator needs to install them for you and then they will be available on all applications.

Of cause, you need to summ the columns instead of concatenating them.

P
PieterDRAuthor
Newcomer
September 21, 2023

Thanks a lot! This has worked and helps a lot

Terms & ConditionsAccessibility statement