10 Replies Latest reply: May 21, 2016 7:36 AM by Stefan Wühl RSS

    Sum a value only if two fields are equal

    Emilio Leo

      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 ?

        • Re: Sum a value only if two fields are equal
          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

            • Re: Sum a value only if two fields are equal
              Emilio Leo

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

                • Re: Sum a value only if two fields are equal
                  Ben Myers

                  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.

              • Re: Sum a value only if two fields are equal
                Stefan Wühl

                Does this work?

                 

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

                  • Re: Sum a value only if two fields are equal
                    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?

                      • Re: Sum a value only if two fields are equal
                        Stefan Wühl

                        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.

                          • Re: Sum a value only if two fields are equal
                            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 

                              • Re: Sum a value only if two fields are equal
                                Stefan Wühl

                                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.

                                  • Re: Sum a value only if two fields are equal
                                    Emilio Leo

                                    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.

                                      • Re: Sum a value only if two fields are equal
                                        Stefan Wühl

                                        So there are at least three tables involved?

                                         

                                        We now know a little more about your data model, but to my understanding, not enough to come up with a profound solution.

                                        Set Analysis is all about sets (i.e. the symbol tables) and the relation of the symbols in your data model, which tables are involved and what the relation of the key fields are.

                                         

                                        If you are really interested in the set analysis approach, you might need to post a more detailed description of your data model.

                                        If you don't have a key field that is granular enough to put into a dimension and then Only() show unique values for both NumeroOrdine and NumeroSO fields per key dimension line in expressions, this approach will not work without creating this kind of key field (changing your model).