I have a single table which holds transactions for different areas of the business. The relevant fields are:
Business_Unit (Unique Identifier)
Subsidiary (Cost Codes) (4999 = recovered amount)
Actual_Amount (Currency Value)
What I’m after is a four column summary
Business_Unit (Group by)
TotalSpend (Sum of Actual_Amount where Subsidiary not equal to 4999)
Recovered (Sum of Actual_Amount where Subsidiary equal to 4999)
Difference (TotalSpend subtract Recovered)
Using TDA 3.0 I have added the Business_Unit field and select Group(1). Then I have added the Actual_Amount field twice and tried to create Where condition formula.
Formula for TotalSpend = AutoRange_Sheet1.Subsidiary <> ‘4999’
Formula for Recovered = AutoRange_Sheet1.Subsidiary = ‘4999’
Only one where formula at a time will work if I try to include both the result are blank
Do I need to use the Subquery… button in the Where condition window?
Any help would be most appreciated.