
1. Re: Calculation field in excel
Shawn Wallwork Oct 20, 2013 1:44 PM (in response to A Saeedi)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.
Your solution attached.
Shawn

Not T Thinking.twbx.zip 9.1 KB


2. Re: Calculation field in excel
A Saeedi Oct 21, 2013 1:47 AM (in response to Shawn Wallwork)Hi,
Thanks for your answer
One other question ...
If a calculation formula is as follows:
Calc = B2 / B3
What is the solution?

3. Re: Calculation field in excel
Shawn Wallwork Oct 21, 2013 4:55 AM (in response to A Saeedi)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
Jonathan Drummey Oct 21, 2013 5:19 AM (in response to A Saeedi)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 IndexMatch 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 reaggregate 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 reaggregate 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 reuse 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/preparingexcelfilesanalysis
http://kb.tableausoftware.com/articles/knowledgebase/addinreshapingdataexcel
http://www.theinformationlab.co.uk/2012/03/27/tableauforexceluserspart2calculatedfields/
http://www.theinformationlab.co.uk/2012/05/15/tableauforexceluserspart3datablending/
http://www.tipsforexcel.com/2013/07/usingexcelfunctionsintableau/

5. Re: Calculation field in excel
A Saeedi Oct 21, 2013 8:46 AM (in response to Shawn Wallwork)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....
Please get more tips

6. Re: Calculation field in excel
Jonathan Drummey Oct 21, 2013 9:11 AM (in response to A Saeedi)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
Shawn Wallwork Oct 21, 2013 9:14 AM (in response to A Saeedi)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

Not T Thinking2.twbx.zip 9.8 KB
