1 Reply Latest reply on Nov 12, 2016 5:08 AM by Chris McClellan

# Calculation Help - 4 Data Sources

Hello,

I have four data sources:

"2016 Expenses YTD" - Primary

"2016 Expense Budget" - Secondary

"2016 Capital Expenses YTD" - Secondary

"2016 Capital Budget" - Secondary

They all have a relationship created with the field "Project ID"

I am trying to take the "Expense Actual" field from "2016 Expenses YTD"

add the "Amount" field from "2016 Capital Expenses YTD" and subtract that from

the total of "Translated Debit Minus Credit Amount" from "2016 Expense Budget" and "Translated Debit Minus Credit Amount" from "2016 Capital Budget"

My formula is:

SUM([Expense Actual])+SUM([2016 Capital Expenses YTD].[Amount])-SUM([2016 Expense Budget].[Translated Debit Minus Credit Amount])+SUM([2016 Capital Budget].[Translated Debit Minus Credit Amount])

The results come back blank for all the fields. However, if I only use:

SUM([Expense Actual])+SUM([2016 Capital Expenses YTD].[Amount])

The results come back as expected. Similarly, if I break the calculation into any of its four pieces:

1) SUM([Expense Actual])

2) SUM([2016 Capital Expenses YTD].[Amount])

3) SUM([2016 Expense Budget].[Translated Debit Minus Credit Amount])

4) SUM([2016 Capital Budget].[Translated Debit Minus Credit Amount])

They all come back with the results I would expect. Can anyone explain what I need to do to get the proper result from the entire formula? Thank you!

• ###### 1. Re: Calculation Help - 4 Data Sources

Is there NULL values in there at all ?

I would try:

SUM(ZN([Expense Actual]))+SUM(ZN([2016 Capital Expenses YTD].[Amount]))-SUM(ZN([2016 Expense Budget].[Translated Debit Minus Credit Amount]))+SUM(ZN([2016 Capital Budget].[Translated Debit Minus Credit Amount]))

as soon as you hit a NULL, the result will be NULL