1 of 1 people found this helpful
You're close, change the calculation to be and place Joint and Application somewhere on the view:
(IF MAX([Joint])="Primary" THEN SUM([Amount]) END)+
(SUM([20130904_planned_gifts].[PG_AMOUNT])-(IF (MAX([Application])="Planned gift" AND MAX([Joint])="Primary") THEN SUM([Amount]) END))
However, the issue with this is that there is only one ID that meets all of the requirements and therefore, only that ID returns a value (ID=0124171).
Hope this helps!
Thanks for that reply, tracyfitzgerald. It does help. I should've been more precise in my original question. My end goal is to have a tabular view in a viz that contains a column, per ID, that displays the sum of that ID's giving (outright plus planned). I reworked my sample twbx to demonstrate what I'm after (attached). Is there a way to get a tabular view that includes ONLY the ID and the TOTAL COMMIT columns?
gift_aggregation.twbx.zip 314.0 KB
Yes, Ville Tyrväinen, hiding the columns will work. I'm still getting used to the idea that the fields needed for a calculation must be on the visualization somewhere. Also, since I don't care about anything other than "Primary" gifts, I filtered all other values to get rid of the multiple lines per ID. I guess this'll work just fine. Thanks again for your reply!
Except that I've now learned that I can't apply a filter to these tabular results, say in a dashboard with a map that would filter these IDs, since there's a table calculation on this sheet. Have I misinterpreted that restriction, Tracy Rodgers?