1 Reply Latest reply on Apr 5, 2012 6:27 AM by Jonathan Drummey

# Creating temp tables for advanced calculations

Hello,

I am trying to see if Tableau can compute some things I need when analysing my data. Hope one of you guys can help.

Lets say my data is something like:

Program IDRegionRevenue
aNorth America\$10
aEurope\$15
aEurope\$25
bAsia\$5
bEurope\$10

As you can see I have a dimension with program IDs (a,b,c, ...) and each time something happens on a region a line with the revenue and region is added. So i could have 100's of lines for program "a" for instance (25 of them for region US, 25 for Europe, 100 for Asia, etc.).

I am trying to create a pivot table with the following:

1. For each program, we sum all the Revenue by region. So in my table above program "a" has North America = \$10 and Europe = \$40. And the total revenue of the program...so for "a" we have \$50 and "b" \$15.
2. The Region with the higher sum is then the MaxRegion for that program. On the tbale above Max Region for program "a" is Europe. For "b" is Europe again.
3. I would like a pivot table where I can see the Regions vs a grouped dimension for the revenue (ie: Revenue \$0-10, 10-20, > 20). I woul then count how many programs I have on each category.

So something like this:

\$0-1010-20>20
Europe103
North America010
Asia042

I would read this table as follows. "I have 4 programs where most of my revenue came from Asia and whose TOTAL (not only that region but all of them) revenue was between 10 and 20 USD".

As you can see this is tricky as I first need to find the MaxRegion based on the sum of revenues per region per program and then do a pivot table per Total revenue per program.

I can currently do this by adding some more columns to my initial table and then going crazy with Excel! I believe Tableau can help streamline this porcess so i am trying to see how much can I do on the program itself and how much do I need to calculate in advance.

Regards,

Yona

• ###### 1. Re: Creating temp tables for advanced calculations

Hi Yona,

I read through your description a couple of times and I'm confused. You noted that you want to identify the MaxRegion, and there's nothing in the final crosstab that shows where that is used. Also, I'm not clear on how your bins are being created. You write, "I have 4 programs where most of my revenue came from Asia and whose TOTAL (not only that region but all of them) revenue was between 10 and 20 USD", yet I don't see which 4 programs would be totaled to be in the 10-20 bin and which 2 programs would end up in the the >20 region.

Is the final crosstab showing a count of programs where the MaxRegion determines which row they fall into and the total sales for the program determines the column?

Also, some sample data would make this easier to show, if you could post that it would be great.

Jonathan