7 Replies Latest reply: Jan 30, 2012 12:24 PM by Sergejs Kazakevics RSS

    Expression in Set Analysis

    Sergejs Kazakevics

      Hello,

       

      I've stuck with set analysis formula. Basically I want to get table to dispaly the data only where Statement.Amount devided by Currency.Exchangerate is >= than 10000 or <= than - 10000.

       

      The formula should look something like this:

       

      =sum({$<Statement.amount={">=10000<=-10000"}>}Statement.amount)

       

      But 'Statement.amount' needs to be devided by 'Currency.Exchangerate' first in this formula. Does anyone know how to achieve this?

      Any idea will be very helpful for me. Thank you in advance!

       

       

      ~Sergejs

        • Expression in Set Analysis

          Hi,

           

          I would suggest to do this calculation in the script and to create a new field.

           

          Regards,

          Björn

          • Expression in Set Analysis
            Perumal Ayyappan

            Hi,

            What problem .Please upload your qlikview file.

            • Expression in Set Analysis
              Stefan Wühl

              Why not multiply your limits (10000 and -10000) with Currency.Exchangerate in your search expression?

              (well, does your search expression results in any records? I wouldn't think that >=10000 and <=-10000 is possible for any value of amount, I belive the search string uses an implicite AND operator).

               

              This will probably only work with a fixed, single Currency.Exchangerate in the scope of the set expression.

               

              Hm, I assume you have a Currency.Exchangerate that may change by record, right?

              Then I think you can't use the set expression, you need a record based evaluation:

               

              =sum(if( (Statement.amount / Currency.Exchangerate) >=10000 or (Statement.amount / Currency.Exchangerate) <=-10000, Statement.amount / Currency.Exchangerate)

               

              And I think Björn is right, performing the currency calculation in the script is probably better.

               

              Regards,

              Stefan

              • Expression in Set Analysis

                Hi,

                you can set your Set Analysis like:

                • sum({$<Statement.amount={">=10000"}>+<Statement.amount={"<=-10000"}>}Statement.amount)

                If can use + operator like OR logical.

                This will be behaviour of your expression:

                WHERE

                    (Statement.amount >=10000

                     OR

                      Statement.amount <=-10000)

                 

                Malki Ely

                • Expression in Set Analysis
                  Sergejs Kazakevics

                  Hello guys,

                   

                  Thank you all for quick responses and sorry for poor explanation of the problem from my side.

                  So this formula '=sum({$<Statement.amount={">=10000<=-10000"}>}Statement.amount)' works perfectly for me.

                  We use to use this formula in our reports, but recently I realized that Statement.amount field value needs to be devided by Currency.exchangerate. I just wanted to modify my set analysis formula rather than modify the load script.

                  And I would like to create the formula something like this:

                  =sum({$<Statement.amount/Currency.exchangerate={">=10000<=-10000"}>}Statement.amount/Currency.exchangerate).

                   

                  Thanks,

                  ~Sergejs