6 Replies Latest reply on Nov 9, 2018 5:45 AM by Jan Paulsen

    Use Exchange Rate from Secondary Data Source (No Linking Field)

    Jan Paulsen

      Dear Community,

       

      I have to data sources.

       

      Values (in EUR):

      CategoryValue in EUR
      A5
      B6

       

      Exchange Rate:

      CurrencyExchangeRate
      EUR1
      USD1.2

       

      I want to be able to toggle with a currency parameter between USD and EUR. When selecting USD the new values for category A should be 6 (5*1.2) and for category B 7.2 (6*1.2). Please see also the attached example.

       

      I tried the following syntax: If [Parameters].[ExRate] = ATTR([Exchange Rate (Data)].[Currency]) THEN sum([Value  in EUR])*SUM([Exchange Rate (Data)].[Exchange Rate]) END. But it won't work. Can anyone help?

       

      Kind regards,

       

      Jan

        • 1. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
          Jennifer VonHagel

          Hi Jan,

           

          Are you ok with letting the exchange rate be held in a parameter?  The downside is that you'll need to manually update the parameter values if the exchange rate changes. The upside is you don't need to establish a relationship between the data sources.  For this method:

           

          Create a new parameter of type float. You can add the exchange rate values from your field, then manually type in the "Display As" with the appropriate descriptions.

           

          Your transformation calc will be simple:

           

          And...

           

          Hope this helps,

          Jennifer

          • 2. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
            Jennifer VonHagel

            Here's another way of doing it that would let your exchange rates remain in a table and be more easily refreshable.  There is still some manual setup mapping the parameter to the Exchange Rate values, but I assume there won't often be new currencies to add, and this way after the manual setup, you can just refresh your data sources when the exchange rates update.

             

            I've attached the Excel and Tableau workbooks.

             

            Transpose your Exchange Rate table so the exchange rates are set up as measures:

             

            Now bring your Values table in, then join the Alt ExchangeRate table simply on a calculated 1 = 1. This has the effect of joining every record from both tables, which is fine since Alt ExchangeRate basically has no dimensions and only measures.

             

            Use your original ExRate text parameter to map the parameter choice to the measure:

            Now the transformation calculation:

             

            Best,

            Jennifer

            • 3. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
              Jan Paulsen

              Good idea, but unfortunately I need to take the exchange rates from the database. They are updated frequently.

              • 4. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
                Jan Paulsen

                Regarding joining via 1=1. This will double the size of my data set, won't it? I have hundreds of currencies in my real data set... So I am afraid this won't work:/

                • 5. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
                  Jennifer VonHagel

                  Hi Jan,

                   

                  Usually a join like that would more than double the size of your dataset and isn't advisable, but in this case there are measures only, no dimensions. It would add as many columns to your data set as your have currencies, but it wouldn't add any rows.

                   

                  Check out this article on using Custom SQL with parameters.  You should be able to use the join type of my second solution if you use custom sql and your ExRate parameter to grab the exchange rate directly from the database. So in this case it would add a single column to your main dataset: the exchange rate selected from the parameter.

                  Connect to a Custom SQL Query - Tableau

                   

                  For this solution, you need to maintain a live connection to your database. Every time your use selects a new currency from the parameter dropdown, a query will go to your database to retrieve that one currency value. You'll have to check if Tableau has the Custom SQL option for your particular database.

                   

                  I created a quick example of this using my local postgres database. If this sounds like a solution that will work for you (Tableau allows custom sql for your database and your end solution can maintain a live connection), I can detail the steps.

                   

                  Best,

                  Jennifer

                  • 6. Re: Use Exchange Rate from Secondary Data Source (No Linking Field)
                    Jan Paulsen

                    Thanks very much, that is a clever solution!

                     

                    BTW: I came across another way to do it. Create a the field "currency" which shows the values of the parameter. Then use this field for blending:

                    Bild1.png