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

# calculate

Hello.

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

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:

But it is not correct. I need this result:

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

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]

• ###### 2. Re: calculate

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

Are the any other decisions?

• ###### 3. Re: calculate

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.