3 Replies Latest reply: Aug 16, 2011 4:28 PM by Stefan Wühl RSS

    Currency Conversion Expression

      Hi,

       

      I have a currency conversion expression as per below:

       

      sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>}  IF([FX Code From]='USD',_Amount,  IF([FX Code To]='ZAR',_Amount / 6.92,_Amount / [FX Rate])))

       

      The problem is to replace the 6.92 dynamically with the [FX Code To] = USD, FX Rate in order to use the current exchange rate.

       

      The expression works "perfectly" except for the case that the rate for that condition is hard coded.

       

      How do I modify the expression to use the "USD FX Code To" rate?

       

      Do you have any idea what the syntax should look like to replace the 6.92? Something like _Amount / {<[FX Code To]=’USD’>} [FX Rate] ?

       

      I'd appreciate any help or suggestions

       

      Thanks in advance.

       

      Regards,

      Werner

        • Currency Conversion Expression
          Stefan Wühl

          Werner,

           

          the best solution might bedepending on your data model.

           

          If the Exchange rate data is associated to the field [FX Code From], you could maybe use a simpler expression like

          =sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>}  _Amount / [FX Rate])

          but anyway, a change to

           

          _Amount / only({<[FX Code To]=’USD’>} [FX Rate])

           

          should / could work to use a dynamic exchange rate.

           

          Regards,

          Stefan

            • Currency Conversion Expression

              Thank you Stefan, I really appreciate your help and advice. I'm new to QlikView, set analysis etc...I have to start supporting an existing QlikView model.

               

              I’m getting a “error in expression” when I use the expression as per below:

               

              sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>}

              IF([FX Code From]='USD',_Amount,

              IF([FX Code To]='ZAR',_Amount / Only({<[FX Code To]=’USD’>} [FX Rate]),_Amount / [FX Rate])))

               

              Is there something else I have to add to the only statement?

               

              Thanks again.

               

              Regards,

              Werner

                • Currency Conversion Expression
                  Stefan Wühl

                  Ah yes, of course, my fault.

                   

                  It is not allowed to use a aggregation function inside an aggregation without using the TOTAL qualifier.

                   

                  Not sure if you want this qualifier here (it will disregard the charts dimension).

                   

                  Could you put the  [FX Code To]=’USD’ inside the first set expression? Or don't you want to convert to USD in all cases?

                   

                  If you could post your data model (or an relevant part of it ) or a sample app here (upload available in advanced editor), this may make help a little easier.

                   

                  Regards,

                  Stefan