3 Replies Latest reply on Nov 11, 2016 1:52 PM by John Croft

    issue with a calculated field after data blending

    Deepak Deepak

      Hope the description below helps describe my problem - but in a nutshell, I have a calculated filed derived from variables in two different datasources (blended) and that derived field is not accurate. I am certainly new to Tableau but have used datameer, excel and SAS extensively - so you have some context on where I am coming from.

       

      Data: See attached tableau which takes data from the attached CSVs

       

      Goal: Sum of credit by month after converting the amount in credit column to USD (foreign exchange/forex conversion)

       

      What I did in the attached workbook:

      1) Imported each CSV as its own data source (I have to rely on calculated fields to join, so have to use separate datasources and then do data blending vs using a join on the datasource tab)

      2) I have forex data at beginning of each month, so have to derive calculated fields on the sample data in order to join on the month and year. So created month and year fields in each of the data source

      3) Join through Data -> Edit Relationships for month, year and currency

      4) Now created a credit_usd calculated field to convert the credit column to a credit in USD column. credit_USD = credit*FX_Value (Tableau is forcing me to use aggregate functions as the variables for this calculated field are coming from different datasources)

      5) When I summarize the data, the summation of the credit_USD from tableau is not matching up with similar analysis on excel

      6) To trouble shoot I filtered data ONLY for "currency=usd" and since forex_value is 1 for all usd values, I expect sum(credit) to be equal to sum(credit_usd) - which is not the case

       

      What am I doing wrong that the credit_USD is not represnting the accurate value?

       

      I am open to other ways of doing this PLUS can also use some education on what went wrong with my approach

       

      Thank You,

      Deepak

        • 1. Re: issue with a calculated field after data blending
          John Croft

          See Sheet two of the attached.

           

           

           

           

          I deleted your month and year calculated pills.

           

          I renamed the date pills in each to 'Date'. The blended on Currency and MY(DATE) = MY(DATE).

           

          Then created this cross tab view with this new calculation.

           

           

          So first I think you were over thinking the blending for the dates. But also forgetting to actually blend on currency. And then I think you just needed to step through the pieces.

           

          Hope this helps.

          • 2. Re: issue with a calculated field after data blending
            Deepak Deepak

            Thank you John.

             

            1. I could not open your workbook (I am running on an older version it looks like) but I recreated the steps you outlined - which is when I realized the "link" icon next to Currency is broken under the FX Values datasource. Once I activated that, everything came together as expected. My question though - despite defining currency AND the date conditions by going to "Data -> Edit Relationships", why is the link next to currency "broken"? (see screenshot) Just something unexplainable OR some reasoning behind why Tableau does it that way? I guess I should watch out for these "link" icons anytime I do data blending go forward

             

             

            2. With the way we now created the calculated field - sum(credit)*sum([FX_Value].[FX Value]), as soon as I remove the currency from the row, I think it is taking sum of credit and then multiplying with the sum of forex values. However, what we need is something like sum(credit*forex_value) i.e. summation of the products. Ofcourse tableau doesnt let me do the later because the fields are from different datasources and we have to use aggregate functions. Thoughts?

            • 3. Re: issue with a calculated field after data blending
              John Croft

              Hi -

               

              Yes you should check what it's default linking on. I'm not sure why Tableau chooses one linkage over another.

               

              I'm not sure if that shoudl be happening. I'm pasting a link to the workbook on my Tableau public. You should be able to download.

               

              Tableau Public

               

              When I drag currency and credit off the viz, the ordering of the new calculation changes but the actual numbers appear to be the same. Is there a screen shot you can show me?

               

              I even specified with additional FIXED calcs int he link above. And they also seem to show consistent result. Let me know if you have a screen shot I can dive into?