5 Replies Latest reply on Sep 4, 2015 6:59 AM by Will Granger

    Exchange rate/currency conversion using secondary exchange rate table + level of detail

    Will Granger

      Hoping someone can shine some light on this - I've been struggling with currency conversion in Tableau, and I think it is a level of detail issue I am running into. Walkthrough below and currency conversion attached.

       

      I have not been able to attach a TWBX because of the sensitive nature of the data (we're a medical company). If anyone has any suggestions of a similar dataset I can use to repeat my steps please let me know and I'll do my best.

       

      Our company sells products in British pounds (GBP), Australian dollars (AUD), and Euros (EUR), and I need to convert AUD and EUR transactions to GBP, at the level of the average exchange rate per financial month, and by product group. At the level of financial month, I get accurate results, but when I introduce product groups, the results are way off (around 40% too high).

       

       

      Here's a walkthrough + the interesting requirements and kinks in the data that make this task complex:

       

      ---

       

      1. 1.

      Some orders have two rows but have the same order number and Amount (the order # field is called 'Vendor TX Code') . I cannot modify the database to remove these rows, so to reach revenues I use a calculated field (called 'Amount_no duplicates'):

       

      Amount_no duplicates:

           {FIXED [Vendor TX Code]: MAX([Amount])}

       

      This gives one Amount per order number and aggregates correctly.

       

      1. 2.

       

      'Amount_no duplicates' shows the value of the transaction in whatever currency it was made in. There is a Currency field showing GBP, AUD or EUR. I need to convert the results of Amount_no duplicates by linking Currency to the relevant exchange rate and multiplying ([Amount_no duplicates] * [Exchange Rate]).

       

      I have a separate Excel file which contains the exchange rates for the two currencies I want to convert to (AUD and EUR). I have averaged out the exchange rate to the month level (see third sheet of attached spreadsheet), and created a linking field. I have created identical 'Month of year' and 'Linking field' in my revenues table.

       

      1. 3.

       

      I have a financial months field (called 'Financial months') that I need to use instead of the usual date field (called 'Started').

       

      Our financial reporting is done according to [last working day of the month] --> [second last working day of the month]. So for example January 2015 runs from 31.12.2014 to 29.1.2015 and February 2015 runs 30.1.2015 to 26.2.2015. Working days are subject to public holidays, so the easiest way to deal with this has been to code the financial months in manually.

       

      Financial months:

           IF [Started]>=DATE("31/12/2014") AND [Started]<DATE("30/01/2015") THEN MAKEDATE(2015,01,01)

           ELSEIF [Started]>=DATE("30/01/2015") AND [Started]<DATE("27/02/2015") THEN MAKEDATE(2015,02,01)

           ELSEIF [Started]>=DATE("27/02/2015") AND [Started]<DATE("31/03/2015") THEN MAKEDATE(2015,03,01)

           ELSEIF [Started]>=DATE("31/03/2015") AND [Started]<DATE("30/04/2015") THEN MAKEDATE(2015,04,01)

           ELSEIF [Started]>=DATE("30/04/2015") AND [Started]<DATE("29/05/2015") THEN MAKEDATE(2015,05,01)

           ELSEIF [Started]>=DATE("29/05/2015") AND [Started]<DATE("30/06/2015") THEN MAKEDATE(2015,06,01)

           ELSEIF [Started]>=DATE("30/06/2015") AND [Started]<DATE("31/07/2015") THEN MAKEDATE(2015,07,01)

           ELSEIF [Started]>=DATE("31/07/2015") AND [Started]<DATE("28/08/2015") THEN MAKEDATE(2015,08,01)

           ELSEIF [Started]>=DATE("28/08/2015") AND [Started]<DATE("30/09/2015") THEN MAKEDATE(2015,09,01)

           ELSE [Started (timestamp removed)]

           END

       

      ---

       

      I have imported my sales data and the exchange rate table and get CORRECT results with the following set up:

       

      Untitled.jpg

       

      Amount in GBP1:

       

           SUM([Amount_no duplicates])*MAX([Monthly data Other to GBP (Copy of Exchange Rate Database)].[Exchange rate (multiply)])

       

      However, when I introduce product Groups, everything goes wrong (total numbers increase by about 40%):

       

      Untitled2.jpg

       

      Really hope I can get some help on this one. Thanks in advance!