3 Replies Latest reply on Feb 8, 2013 7:49 AM by Jim Wahl

    calculate

    andrey uvarov

      Hello.

      I have two data sources in my tepleau report. You can see example in screen.

      example data source.PNG

      Using option Data -> Edit Relationships, i create relation between data sources by field town. I need to create calculate SUM( portion * size ) as calc1. So i create this report in Tableau:

      result example.PNG

      But it is not correct. I need this result:

      correct result.PNG

      Because (0,3 * 0,3) + (0,5 * 0,15) + (0,2 * 0,1) = 0,185 - for category c1 (i want group data by field "category") ... but Tableau calculate:

      (0,3 + 0,5 + 0,2) * (0,3 + 0,15 + 0,1) = 0,55.

      How i can calculate value 0,185 ?

       

        • 1. Re: calculate
          Jim Wahl

          One approach is to use the custom SQL option when connecting to the excel sheet and doing the multiplication with SQL:

          1. Connect to data > Excel

          2. Select multiple tables and add both first datasoures

          3. Select Custom SQL and edit the custom SQL

           

          Default

          SELECT ['first datasource$'].[category] AS [category],

            ['first datasource$'].[frequency] AS [frequency],

            ['first datasource$'].[size] AS [size],

            ['first datasource$'].[town] AS [town],

            ['second data source$'].[portion] AS [portion],

            ['second data source$'].[town] AS ['second data source$'_town]

          FROM ['first datasource$']

            INNER JOIN ['second data source$'] ON ['first datasource$'].[town] = ['second data source$'].[town]

           

           

          With multiplication

          SELECT ['first datasource$'].[category] AS [category],

            ['first datasource$'].[frequency] AS [frequency],

            ['first datasource$'].[size] AS [size],

            ['first datasource$'].[town] AS [town],

            ['second data source$'].[portion] as [portion],

            (['second data source$'].[portion] *  ['first datasource$'].[size]) AS [calc1]

          FROM ['first datasource$']

            INNER JOIN ['second data source$'] ON ['first datasource$'].[town] = ['second data source$'].[town]

           

          customSQL.jpg

          • 2. Re: calculate
            andrey uvarov

            I use excel for example. I can't use sql, because my data in different sources!!!

            Are the any other decisions?

            • 3. Re: calculate
              Jim Wahl

              You could also use a table calculation, such as

              IF FIRST()=0 THEN

                  WINDOW_SUM(SUM([size]) * SUM([second data source (example data source.xls)].[portion]))

              END

               

              town needs to be on the level of detail shelf and you need to select compute using town.

               

              This looks a bit strange, but Tableau only allows aggregate calculations in the secondary data source---so while it says SUM, it's of course returning just the single value for each town. The SUM([size]) part will be evaluated for each town, so again a single value.

               

              See attached.