Skip to main content
Newcomer
Jan 9, 2023 12:21 AM UTC

Member Formula - Net Calc

Hi all, 

I am having trouble with a member formula that essentially subtracts interest expense from interest income:

DGC_0-1673223506539.png

DGC_1-1673223583746.png

I'm sure I am missing additional dimensions/qualifiers in the member formula, but everything was the exact same except the account of course.

Any direction would be greatly appreciated!

3 replies

Employee - Advisor
Jan 9, 2023 7:58 AM UTC

Good morning, try including the minus sign in the quote / formula that is enclosed by quotation marks. The Design and Reference Guide offers a wide range of examples, e.g.:

Henning_0-1673251071705.png

 

Those should be removed:

Henning_1-1673251114894.png

 

Despite that, this could just as well be a dynamic calculation in my view. Or the net account could just be the parent of the two base members. Saves you the need to re-calculate, and, more importantly, avoids unnecessary data being written (i.e. stored) into the database.

Employee - Sage
Jan 9, 2023 8:37 AM UTC

The way Calculate works, it's meant to create records in the database that will contain the results of a calculation. To do that, you always have to specify a set of destination coordinates for the records you will be creating; that has to be on the left hand side of the assignment operator (=). So in your case, the formula should probably look something like this:

api.data.calculate("A#Interest_Expense_Net:F#Cash_Input = A#WC_Interest_Income_ThirdParty:F#Cash_Input - A#WC_Interest_Expense_ThirdParty:F#Cash_Input")

 You should probably review the parts of the Design & Reference Guide that deal with Stored Calculations and Member Formulas. I would also recommend the OneStream Finance Rules and Calculation Handbook, available at online bookstores (Amazon etc).

Employee - Advisor
Jan 9, 2023 8:40 AM UTC

Absolutely, totally overlooked that the destination is missing as well. Thank you for the spot!