4 Replies Latest reply on Mar 23, 2018 8:22 AM by David Núñez

    Calculated field for currency converter

    David Núñez

      Hi,

       

      Here's the situation, my data for sales is shown in my local currency (NIO), I would like to see this data in USD. We came up with a simple calculated field but we have to update it monthly in each dashboard, the field goes like this:

       

      IF YEAR([Sales date])=2018

      AND MONTH([Sales date])= 01 THEN ([NIO value]/30.9188)

      ELSEIF YEAR([Sales date])=2018

      AND MONTH([Sales date])= 02 THEN ([NIO value]/31.0347)

      ELSEIF YEAR([Sales date])=2018

      AND MONTH([Sales date])= 03 THEN ([NIO vaue]/31.1636)

      END

       

      As you can see, I would need to create another line when April comes around. Can you think of a calculated field that will allow the USD value to be calculated automatically? I was thinking about linking my data to a Google Drive file, so I could update the exchange rate there and then it would automatically update the USD value in all of my dashboards, but I can't seem to come up with a way how.

       

      Thank you in advance.

        • 1. Re: Calculated field for currency converter
          Eric Hammond

          Hi David,

           

          Whether a table for exchange rate history is created in the database that has sales data, in Excel, or in Google Sheets, the approach should be similar.

           

          The exchange rate table should have a field for MMYYYY, and a field for the exchange rate.

           

          On the data Source tab for the sales connection, right-click on Sales Date and choose Create Calculated field; name it MMYYYY:

           

          RIGHT('0' + STR(MONTH([Order Date])),2) + STR(YEAR([Order Date]))

           

          LEFT JOIN from Sales to Exchange rates on the MMYYYY field.  The calculated field from the original post now becomes: 

           

          [NIO value]/IFNULL([exchange rate],31)

           

          This defaults to 31 when the exchange rate hasn't yet been populated; the calculation can be revised to use a better default.

          • 2. Re: Calculated field for currency converter
            David Núñez

            Hello Eric, I'm stuck. My two data sources would be tableau server and google sheets, I couldn't execute for calculated field properly, can you attach a sample?

             

            Thanks in advance,

            David

            • 3. Re: Calculated field for currency converter
              Eric Hammond

              Hi David,

               

              My apologies, I am also unable to join on a calculated field.  However, I verified that data blending works.

              • On the data source tab, choose Data, new data source, and connect to the spreadsheet for exchange rates.
              • Ensure that the calculated field for YYYYMM in the sales data source has the same name as the field identifying the exchange rate month.
              • Make sure that the first field added to sheet1 is from the sales source, making that datasource primary.
              • On the exchange rate data source, for the YYYYMM field, make sure that the "chain icon" is orange/connected.
              • Blending combines aggregated data, the formula will look like: SUM([NIO Value])/IFNULL(AVG([ExchangeRate (DemoExchange)].[Rate]),31)

               

              Workbook attached.

              • 4. Re: Calculated field for currency converter
                David Núñez

                Thank you!

                 

                This works great.