Sum a value only if two fields are equal

[#1]

Hi all,

  I'm running qlik sense, and I need to sul the invoice value (ImportoFattura) only if the value of two fiels "NumeroOrdine" and "NumeroSO" are equal ... this two fields belong to different tables.

I've tried reading in some blogs

• Sum({<NumeroOrdine=P(NumeroSO)>}ImportoFattura)
• Sum({<NumeroOrdine={"=NumeroOrdine=NumeroSO"}>}ImportoFattura)

But it doesn't work ...

By the way: I cannot load "NumeroSO as NumeroOrdine" otherwise I catch a "circular expression" error

Any Ideas ? Is it possible ?

[sunny_talwar]

May be this:

Sum({<UniqueFieldHere={"=NumeroOrdine=NumeroSO"}>}ImportoFattura)

For UniqueFieldHere you need a field which uniquely define the combination of NumeroOrdine and NumeroSO

[swuehl · Accepted solution]

Does this work?

=Sum( If(NumeroOrdine = NumeroSO, ImportoFattura))

[#4]

Sorry I don't understand what field choose for "UniqueFieldHere "

[sunny_talwar]

Stefan -

Knowing you, I would have thought that you would also provide a set analysis solution. Is set analysis solution won't work in this case? Is there a reason you provided if statement solution here?

[swuehl]

There might be a set analysis solution possible, but given the fact that the fields are in different tables and we know nothing about the model, I just wanted to know if this simple aggregation with the implicite join of the tables works.

Seems like the OP is already satisfied with this solution.

[sunny_talwar]

I got you. That makes complete sense. So hypothetically lets say a set analysis solution does exist, would it be better than the if statement. Always wondered this and I think this is a great place to ask you this.

Best,

Sunny 

[swuehl]

If your UniqueFieldHere is getting too granular, I believe (but remember what John just told us) that it wouldn't make much sense to use set analysis.

[bme]

Sunny's answer is correct if a bit brief. The syntax he is calling out is the use of search string. @Henric Cronström did a more detailed write up on it on the blog which is worth a read. The Search String

Basically the value you put in the set analysis is the same as you would put in the search string. In this case you need to use the expression search syntax. Imagine making a chart with one dimension and one expression and the expression can only evaluate to true or false. The expression is then evaluated within the context of the specified dimension. The expression search is doing the same thing except that it the field being searched, or specified in the set analysis, serves as the dimension and the records that are selected are those that evaluate to true.

In your case the "UniqueFieldHere" should probably be something like invoice number or equivalent that would represent the unique record id at the level of granularity to be evaluated.

However if the fields NumeroOrdine and NumeroSO actually exist on a single record in one table then your more efficient option will be to create a flag in script during the data load so that it can be a simple selection rather than an evaluation.

[#10]

Really an exciting discussion guys, really many thanks to add so much contents to my question.

Just to give more information to Benjamin and Stefan (who wrote "we know nothing about the model") I'd like to give this information more

Actually NumeroOrdine and NumeroSO are the same field contained in 2 different table ... I had to change in the second table NumeroOrdine to NumeroSO otherwise qlik will put in relation this two tables ending with a loop (the first table is already in relation with another table with field Customer Purchase Order). So to avoid loop I made the change NumeroOrdine --> NumeroSO.

Hope it helps in understanding.

Really many thanks to you all.