14 Replies Latest reply on Nov 29, 2013 8:56 PM by Doug Stanley

If 'this, then sum' and subtract from 'that...'

Relative newbie here. Packaged workbook attached. Thanks in advance for any help provided.

I'm working with trade data for 38 named countries and the 'World'. I need to produce an aggregate value for the named countries, then subtract that from the World value (to prevent double counting) and ultimately arrive at a "Rest of World" figure that provides the correct bubble in the associated packed bubbles chart.

I've tried multiple combinations of field calcs and groups. I get close, but not quite where I need to be. Driving me nuts.

• 1. Re: If 'this, then sum' and subtract from 'that...'

SUM(IF CONTAINS([Partner], "World") then [Value] END) // this should give you the "World" value

SUM(IF NOT CONTAINS([Partner], "World") then [Value] END) // this should give you the "rest of the world" value

Then, it is just a matter of subtracting the two calculated fields.

This assumes that none of the other countries have "World" as part of their name.  I hope this helps, I didn't test anything in your workbook but thought this might get you on your way.

• 2. Re: If 'this, then sum' and subtract from 'that...'

Matthew,

Thanks for the reply. I've had good luck with many calcs, but still

struggling with this one.

I'm missing something on the syntax when I try to apply your approach.

Tableau asks for 'END' but no matter where I put it it doesn't like it.

Apologies for such a basic question.

• 3. Re: If 'this, then sum' and subtract from 'that...'

Sorry, I forgot the END:

SUM(IF CONTAINS([Partner], "World") then [Value] END)

I just confirmed this works fine in your workbook.

1 of 1 people found this helpful
• 4. Re: If 'this, then sum' and subtract from 'that...'

Of course, I should have known that. It works as revised.

Now the next very basic question: Returning the correct value for the 38

countries and rest of the world would require a third calc that builds on

these two?

Again, many thanks for the help.

• 5. Re: If 'this, then sum' and subtract from 'that...'

Yes, you can now use those fields however you like.  If you wanted to subract them, you'd simply write another field like:

[World] - [Rest of World]

• 6. Re: If 'this, then sum' and subtract from 'that...'

This gets me soooooo close to the answer:

As you suggested:

• SUM(IF CONTAINS([Partner], "World") then [Value] END) gives me the value for World.

• SUM(IF NOT CONTAINS([Partner], "World") then [Value] End) gives me the Aggregate of the Named Countries
• So [World] - [Aggregate of the Named Countries] gives me an Adjusted World Value

This works in the aggregate. But I need to list it out by country and included the Adjusted World Value under the name "Rest of World," i.e.:

USA                   10

UK                     12

Brazil                  14

Rest of World         8

Thanks for any further assistance.

• 7. Re: If 'this, then sum' and subtract from 'that...'

You can write calcs for each country if you like:

SUM(IF CONTAINS([Partner], "USA") then [Value]) // this should give you the "USA" value

1 of 1 people found this helpful
• 8. Re: If 'this, then sum' and subtract from 'that...'

What would be the syntax for stringing it all together, i.e. getting a column that contains the names of the 38 countries and the world? I've tried variations of the below to no avail:

SUM(IF CONTAINS([Partner], "Country1") then [Value])OR

SUM(IF CONTAINS([Partner], "Country2") then [Value])or

SUM(IF CONTAINS([Partner], "Country3") then [Value])

end

• 9. Re: Re: If 'this, then sum' and subtract from 'that...'

Doug you're creating a whole lot more work for yourself than you need to. You really only need to use a Group on the Partner field and let Tableau do the rest:

The easiest way to do this is create single country groups for US UK & Brazil, and then check the 'Include Other' at the bottom of the dialog box. Then rename it. (See attached.)

• 10. Re: If 'this, then sum' and subtract from 'that...'

Shawn, thanks for the reply. I don't think it's only a grouping exercise, although that may be part of the ultimate answer.

I have 38 named countries, each with a corresponding value, and 1 World value.

My need is simple but has stumped me to this point: List each of the 38 countries and a corresponding value. Calculate and display a "Rest of the World" value by subtracting the sum of the 38 from the single World value provided.

• 11. Re: If 'this, then sum' and subtract from 'that...'

Doug, sorry about that, I didn't notice the World Country. Edit your connection like this:

SELECT [Import Export].[Indicator] AS [Indicator],

[Import Export].[Location] AS [Location],

[Import Export].[Location code] AS [Location code],

[Import Export].[Partner] AS [Partner],

[Import Export].[Value] AS [Value],

[Import Export].[Year] AS [Year]

"38 Countries" AS [Type]

FROM [Import Export]

UNION

SELECT [Import Export].[Indicator] AS [Indicator],

[Import Export].[Location] AS [Location],

[Import Export].[Location code] AS [Location code],

[Import Export].[Partner] AS [Partner],

[Import Export].[Value] AS [Value],

[Import Export].[Year] AS [Year]

"World" AS [Type]

FROM [Import Export]

Then you'll be able to use the Type field to create the calculation need. If you run into trouble post a workbook with this connection and I'll help you work it out.

--Shawn

• 12. Re: If 'this, then sum' and subtract from 'that...'

Shawn, Tableau doesn't like that SQL...

• 13. Re: If 'this, then sum' and subtract from 'that...'

Sorry I forgot to add in the commas. See if this works:

SELECT [Import Export].[Indicator] AS [Indicator],

[Import Export].[Location] AS [Location],

[Import Export].[Location code] AS [Location code],

[Import Export].[Partner] AS [Partner],

[Import Export].[Value] AS [Value],

[Import Export].[Year] AS [Year],

"38 Countries" AS [Type]

FROM [Import Export]

UNION

SELECT [Import Export].[Indicator] AS [Indicator],

[Import Export].[Location] AS [Location],

[Import Export].[Location code] AS [Location code],

[Import Export].[Partner] AS [Partner],

[Import Export].[Value] AS [Value],

[Import Export].[Year] AS [Year],

"World" AS [Type]

FROM [Import Export]

• 14. Re: If 'this, then sum' and subtract from 'that...'

It did turn out that grouping was the answer. I grouped each of the 38 named countries in single-member groups, then grouped ~150 countries as "Rest of World." This produced the results I needed. Thanks to Shawn and Matthew for the help. Much appreciated.