8 Replies Latest reply on Jun 18, 2012 5:20 AM by Jonathan Drummey

    Data blending - Currency rates

    Monica Fonea

      I have 2 datasources:

      1) Invoices with amounts in several currencies 

      2) Currency rates per invoice


      Using data blending  I must use aggregation and then the results are not correct

      I used average rate but when viewing per Customer the total is not correct

      Appreciate your help

        • 1. Re: Data blending - Currency rates
          Jonathan Drummey

          Hi Monica,


          In order to have blends work properly you need the appropriate level of detail, which in your case would be Invoice Nbr and I'm guessing the line as well so you can identify the specific currency used for that line item on the invoice. However, the Rate datasource only has Invoice Nbr a Rate, so there's no way for Tableau to blend at the right level of detail.


          If you can't get a line number in the Rate datasource, then you could get something like a currency code into each datasource and use that for the blend instead.



          • 2. Re: Data blending - Currency rates
            Monica Fonea

            Thanks Jonathan

            I can add the line to the Invoice nbr but when using it in the calculationa from thesecondary source  I must aggregate it

            (Example Sum(Qty*Price) * AVG (SndDATASOURCE.rate)

            and then if I want to see the total by an upper level then Invoice (cUSTOMER,Prod Line,Family)

            i got the wrong result :{

            • 3. Re: Data blending - Currency rates
              Jonathan Drummey

              Hi Monica,


              If the Invoice nbr and the line from the primary datasource are both in the view _and_ you have Invoice nbr and line in the secondary data source (the rate), and when viewing the secondary datsource you see the orange/red chain link icons next to Invoice nbr and line***, then the aggregation won't matter because you will be aggregating at the level of detail that gives you the proper rate for each line item on the invoice. At that point, you could us AVG, SUM, ATTR, etc. because any would give you the correct rate - I'd suggest ATTR because it'll be really obvious if you're getting more than one rate returned from the blend.


              The data that you posted doesn't have the line in the rate datasource, so it's not available to Tableau to create the proper join at the level of detail you need.


              If you don't see the link icons, then you need to go to Data->Edit Relationships to make sure that line is a linked field for the datasources.


              Does that make sense?



              • 4. Re: Data blending - Currency rates
                Monica Fonea

                Hi Jonathan

                Thanks for your response

                I added the line - You can see in the attached example

                Customer Dell - when you see the view of all customers-> Total $ for Dell 1973$

                but if you select "keep only" you get a diferent amnt (2342$)

                All is consequence I suppose dfor using AVG

                Using Attr I get * in the customer level ..


                • 5. Re: Data blending - Currency rates
                  Jonathan Drummey

                  Ok, I see what you're describing now.


                  The problem isn't the blend (that's working), the issue is that when creating the subtotals and totals, Tableau doesn't create a total of what's visually on the view, it performs a separate calculation at the level of detail for the subtotal/total. So it's calculating the tot% at the Total level as $1571 (TotCurr) * 1.26 (Avg. rate) = 1,975.53 which is not the same as total of all Invoice Nbr/line rows for Dell, which comes out to 2689.50.


                  Why I suggested using ATTR() is that it makes this kind of miscalculation obvious because it returns * when the blend fails to return the desired results.


                  I can think of two options:


                  One is to create a separate worksheet using table calculations where you'd have the Invoice Nbr and line on the leavel of detail and generate totals per customer, then you could combine that worksheet and the new one on a dashboard.


                  Two is to use Tableau's ability to join multiple tables when creating the datasource. I noticed that both datasources had the same filename, so I extracted that from the workbook and then created a third datasource where I joined the Invoices and Rate tables (on the invoice for now, since the currency amounts were the same for each invoice). Now the Rate measure is part of the datasource so we don't need a blend, and Tableau's subtotals and totals work as expected.



                  • 6. Re: Data blending - Currency rates
                    Monica Fonea

                    Hi Jonathan ,

                    I understand ,join is not an option since the exchange rate comes from a different datasource (the attached was just an example to explain the problem) .

                    May be the option of 2 sheets in a dashboard could work

                    Thank you

                    • 7. Re: Data blending - Currency rates
                      Joe Mako

                      How about the attached? It even retains the working hierarchy drill-up/down

                      • 8. Re: Data blending - Currency rates
                        Jonathan Drummey

                        Hi Joe,


                        Thanks for all your posts yesterday! This one especially stuck out for me, I think I'd seen you duplicate a dimension to "preserve" the level of detail once before, and had forgotten about that technique, it's brilliant!