topic Re: Comparing sums from different tables in QlikView

Comparing sums from different tables

sibrulotte — Fri, 22 Jun 2018 21:16:31 GMT

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?

Re: Comparing sums from different tables

felipedl — Mon, 25 Jun 2018 12:40:46 GMT

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.

Re: Comparing sums from different tables

sunny_talwar — Mon, 25 Jun 2018 13:00:55 GMT

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]))

Re: Comparing sums from different tables

sibrulotte — Tue, 03 Jul 2018 17:26:28 GMT

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 ?

Re: Comparing sums from different tables

sunny_talwar — Wed, 04 Jul 2018 23:02:23 GMT

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?