5 Replies Latest reply on Jan 10, 2019 7:32 PM by Ken Flerlage

    Convert Local Currency to USD for Balance Sheet

    Kelvin Nguyen

      Hi guys,

       

      I'm facing an issue with converting Local Currency numbers to USD for Balance Sheet items.

       

      Let's say I have this data set:

       

      Time
      Value
      00000050
      201801100
      201802200
      201803300

       

      The first row is the ending balance before 2018 Jan. And from then, each row shows the moving value of each month. For example, if I want to get the ending balance at the end of 2018 Jan, I will need to take 50+100 and if I wanna get the ending balance of 2018 Feb, I will need to compute 50+100+200.

       

      Now, all this is in local currency and I want to be enable user to toggle between this and USD. Hence, I have another table with the exchange rate:

       

      TimeExchange Rate
      2018011.1
      2018021.2
      2018031.3

       

      So in short, if I wanna see the ending balance of Feb 2018, I will need to take (50+100+200)/1.2

       

      Is there a straight forward way to do this in Tableau, how should I join the two tables? And what are the alternatives that I have?

       

      Right now, I'm using data blending to achieve this but I believe that data blending will slow down the performance of the calculations.

       

      Thanks Gurus.

        • 1. Re: Convert Local Currency to USD for Balance Sheet
          Jim Dehner

          Hi

          currency exchange can be difficult - since you did not include your workbook I can't give a specific answer but in concept

          you would join the transaction file with the exchange rate file (use a right join where all the transaction data is included)

          then you will have an exchange rate associated with each record the transaction file

          create a calculated field that does the multiplication trans value * exchange rate -   BUT if you exchange data is not complete

          i.e there are transaction dates that don't have a matching exchange rate you would need to account for the nulls with something like   ifnull(min(exchange rate),1))

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Convert Local Currency to USD for Balance Sheet
            Deepak Rai

            Like This?

            Thanks

            Deepak

            If it Helps, Pl mark it Helpful and CORRECT to close Thread

            • 3. Re: Convert Local Currency to USD for Balance Sheet
              Ken Flerlage

              You could do a join as well, as shown below:

               

              Next, in Tableau, I'd create a calculated field that does a running sum.

               

              Running Sum

              // Running sum of value.

              RUNNING_SUM(SUM([Value]))

               

              Then another that converts this to USD:

               

              Running Sum USD

              // Running sum converted to USD.

              [Running Sum]/MAX({FIXED [Time]: MAX([Exchange Rate])})

               

              Note: I'm using a fixed LOD here so we can make sure we're getting the exchange rate as of the time on the record we're looking at. The advantage of this is that you can remove the time dimension and aggregate at a higher level and it should still be accurate.

               

              Finally, you can build your view:

              See attached.

              • 4. Re: Convert Local Currency to USD for Balance Sheet
                Kelvin Nguyen

                hey Ken Flerlage thanks for the awesome guide. However, the limit with table calculation is that it will perform on the table only and it won't calculate from the source itself.

                 

                Do you think that it will be more efficient to have the data structure this way?

                 

                Time

                Value

                FX Rate

                00000050depends on user
                201801100depends on user
                201802200depends on user
                201803300depends on user

                 

                 

                So I have one more column that is the FX rate and it will change accordingly with user's selection.

                 

                For example, if user selects 2018 Jan, it will populate the fx rate to be 1.1

                 

                 

                What do you think?

                 

                Thanks.
                Kelvin

                • 5. Re: Convert Local Currency to USD for Balance Sheet
                  Ken Flerlage

                  I'm not sure I understand. I don't think you want to be able to select a specific month's exchange rate to apply to all months as that would not give you accurate information.