Comparing sums from different tables

[sibrulotte]

Hi,

I have three tables:

Requisitions, chart of account, and GL transactions. ( my document security is on business unit, and I want my managers to see all their requisitions, so I can't play around too much with the model)

I want to sum for every Requisition NO the requisition amount, and compare it the amount in the GL matching the Requisition No GL.

Heres the chart expression I'm using to retrieve GL account from a chart that has Requisition dimensions:

=sum({<[Requisition NO GL] = p([Requisition NO])>} [GL ammount])

The result is in the Requisition amount chart

I would like to find in the Requisition amount chart in the GL amount expression for SP-123: 50$

and for SP-ABC: 55$.

Anyone have a recommendation on how to get the desired result?

[felipedl]

Hi Simon,

Just change your expression to:

=sum({<[Requisition NO] = p([Requisition NO GL])>} [GL ammount])

Your expression:

=sum({<[Requisition NO GL] = p([Requisition NO])>} [GL ammount])

Is getting all possible values (marked in red bellow) twice for each [Requisition NO], since you have the association in your model and so doubling your values.

Additionally, its also getting the value for SP-789 since it's a possible [Requisition NO] value.

[sunny_talwar]

Not sure, but you might be able to try this in addition to felipedl‌'s suggestion

=Sum(If([Requisition NO GL] = [Requisition NO], [GL ammount]))

[sibrulotte]

Thank you both.

Felip: the expression does not return 50 and 55. Returns 105.

Sunny: That brings back in deed 50 and 55, but where's the 150 for SP-789 ?

[sunny_talwar]

It seems that SP-789 doesn't have a link because Business unit is null for SP-789

May be you will need to rethink the data model to get this requirement done?