7 Replies Latest reply on Oct 21, 2013 9:14 AM by Shawn Wallwork

# Calculation field in excel

Hi,

Is it possible to build the "Calc field" in Tableau Software? (Please see the attached Excel file)

note: Calc=B2+B3+B4-B5-B6

• ###### 1. Re: Calculation field in excel

Well sure, but this isn't really the way Tableau works or 'thinks'. To get the most out of Tableau start trying to figure out how Tableau does things, NOT how to make Tableau do things the way Excel does things.

--Shawn

• ###### 2. Re: Calculation field in excel

Hi,

One other question ...

If a calculation formula is as follows:

Calc = B2 / B3

What is the solution?

• ###### 3. Re: Calculation field in excel

IF FIRST()=0 THEN LOOKUP(SUM([value]), FIRST())/LOOKUP(SUM([value]),FIRST()+1) END

Then put the Type field on the Detail shelf and set the compute using to Type.

--Shawn

• ###### 4. Re: Calculation field in excel

Shawn gave one answer that would be dependent on a specific layout of the view. The other answer is, "it depends".

Excel and Tableau approach data very differently. Excel starts out with a grid of cells, each cell exists independently of the other cells and any cell (or cell formula) can arbitrarily refer to another cell on any other worksheet or even completely file with a reference like Sheet2!A2. Tableau thinks about data as a table (or a cube if using a multidimensional data source), where there are columns of data with a defined datatype (aka fields) and rows of values, then as we move pills in and out of a view VizQL interprets our actions to dynamically create queries against the data. So things like VLookup and Index-Match that are done in Excel to create relationships between one cell or set of cells and another require different approaches in Tableau.

Also, in Excel because of the work necessary to transform and pivot data, we often end up with a whole set of worksheets and tables within worksheets that are a serially/set of calculations to slice, dice, aggregate, and re-aggregate data to get to our desired view. In Tableau, much of this work can be done "all at once" via the arrangement of pills in the view, the aggregations used, calculated fields and where necessary table calculations to re-aggregate along different dimensions.

Another difference is immediately visible in the user interfaces of the two applications. The Home ribbon of Excel is full of options for formatting cell contents. This encourages users to create a worksheet that is formatted for presentation, and this can make it much harder to re-use the data contained in the worksheet for other analyses. Tableau is all about the data, when you start up Tableau the first option in the upper left is "Connect to data".

So, when coming from an Excel background, there's a transition to be made where it can seem like sometimes we need to do more work to prepare our data for use in Tableau, on the flip side we're able to work more quickly in Tableau once we've made that effort.

Some places to help get started are:

http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

http://www.theinformationlab.co.uk/2012/01/31/tableau-for-excel-users-part-1-recreating-the-pivot-table/

http://www.theinformationlab.co.uk/2012/03/27/tableau-for-excel-users-part-2-calculated-fields/

http://www.theinformationlab.co.uk/2012/05/15/tableau-for-excel-users-part-3-data-blending/

http://www.tips-for-excel.com/2013/07/using-excel-functions-in-tableau/

• ###### 5. Re: Calculation field in excel

Shawn Wallwork wrote:

IF FIRST()=0 THEN LOOKUP(SUM([value]), FIRST())/LOOKUP(SUM([value]),FIRST()+1) END

Then put the Type field on the Detail shelf and set the compute using to Type.

--Shawn

Hi Shawn,

I've tested your code but unfortunately it did not work....

• ###### 6. Re: Calculation field in excel

Hi,

You haven't said anything about how it didn't work or given details of your view, please post a packaged workbook with some sample data so we can figure out what is going on.

Jonathan

• ###### 7. Re: Calculation field in excel

In the attached the formula is 'working'. But like Jonathan pointed out everything in Tableau can affect everything else. Formulas are not static and in a single cell like Excel. Formulas are dynamic and change depending on what else is in the view, and how these formulas (calculated fields) are used in the view. This is especially true of table calculations.

Without knowing what other fields (pills) are in your view, there really isn't any purpose in continuing this discussion. I can keep giving you 'more tips' but without a better understanding of the big picture of what you are trying to accomplish I'm really just wasting your time, and ultimately doing you a disservice. If you post a packaged workbook of what you are really trying to accomplish, I'll be happy to show you how to do it.

Cheers,

--Shawn