Conditional Sum

[Rafael Esperidião]

Hi there,

I've been reading all possibles forums trying to find a solution for my conditional summing, the problem is not so different but i don't know why I can't get it to work.

like this in 1 table:

A field with "IN" and "OUT" as a Radio Botton Set

A field with the Value ( number value )

What I want is to make two new fields that gives me the sum of all INs and all OUTs in the table:

Sum IN

Sum OUT

Thx!

[isaaccs]

Create two new text fields in your table, make them globals. One will always contain the text "IN" and the other will always contain the text "OUT". If you're working with existing records make sure you actually fill these fields in with that text once you've created them.

Then, create two self joining relationships for your table- one keyed with your first field and the "IN" global field, and the other one keyed with your first field and the "OUT" global field.

Then create two calculation fields in the table, the calculation being the summary of the number fields in records in each respective joined table, obviously evaluated in the context of the root/original table.

Make sense?

OR

Ccreate two additional calculation fields in your table.

for your first calculation field"

if the value of the first field is "ON", then copy the value from your number field, otherwise 0

for your second field

if the value of your first field is "OFF" then copy the value from your number field, otherwise 0

then create two additional "Summary" fields, based on these two calculation fields you've just defined.

Edited February 4, 2010 by Guest

[Rafael Esperidião]

Awesome!

Thank You!

The second one, worked better for me!

xD

[Rafael Esperidião]

Hello again

since its the same topic.

is there a way to do this using matching value list?

Like this:

I have 2 layouts: Accounts and Movements

In Accounts I have these fields: AccountName and AccountValue

and in Movements I have as fields: Account and Value

The field Account is a ValueList that refers to AccountName

How do i get AccountValue as just the sum of Value from all records whenever AccountName = Account

Some think like:

if ( AccountValue = Movements::Account; Sum ( Value from all records in Movements that matches the condition; 0 )

Thank You!