4 Replies Latest reply on Dec 17, 2013 8:21 AM by Jonathan Drummey Branched to a new discussion.

    absolute reference

    alex macharia

      Jonathan Drummey

      Hi,

      I have noted you do a lot of calculated fields. I am trying to do some excel formulas in tableau.

      In excel you can use the $ to create an absolute reference for a cell. e.g cell c3 would be $c$3.

      How can I achieve this in tableau?

      I have been trying windows_sum but I am not successful.

      Any ideas?

      Thanks

        • 1. Re: absolute reference
          Matt Lutton

          Alex:

           

          You may want to describe the scenario you are trying to work with in a bit more detail.  What are you trying to accomplish?  Can you post a sample packaged workbook or screenshots to demonstrate?  I know you ping'd Jonathan, but I am curious about what you are trying to do.

           

          Cheers.

          • 2. Re: absolute reference
            Jonathan Drummey

            There's no such thing as an absolute cell reference in Tableau, so what Matthew asked for is what's necessary to answer what I believe is the question behind the question, i.e. "How do I do X kind of calculation on Y data in Tableau?"


            Here's my boilerplate text on the Excel vs.Tableau differences:


            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 a completely different file with a reference like ='[FY2013 Physician Compensation Salary Adj.xlsx]Sheet2'!$A$2. Tableau thinks about data as a set of tables (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 use cell references to create relationships between one cell or set of cells and others require different approaches in Tableau.

             

             

            Here's a quick example: In Excel, when we want a sum we'll do =SUM(B2:B20) or something like that, whereas in Tableau we'll have a measure like Sales and then when we drag that into the view Tableau can apply the SUM() aggregation to that (and we can change aggregations with two clicks). The aggregation is based on the dimension pills in the view, so we can drag and drop dimensions as needed without having to write any cell references.

             

            Also, in Excel because of the work required to transform and pivot data, we often end up with a whole set of worksheets and tables within worksheets that are a serially nested set of calculations to slice, dice, aggregate, and re-aggregate data. 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.

             

            In Excel we can put multiple charts on a single worksheet with or without their data, which may be in other worksheets or entire other workbooks. In Tableau, we connect to all the data source(s) that we need and build worksheets, which can easily become small multiples worksheets that have a variety of different views, and then we can combine multiple worksheets into dashboards.

             

             

            Conditional formatting is another area of difference. In Excel we can assign a variety of rules to a given cell to generate KPIs, data bars, etc. In Tableau we define the rules in the data, potentially in the source or via calculated fields, and then use that data to generate the specific marks that we want. This is an area where Excel can have some advantages over Tableau in terms of precision of control over layout and design, however once set up it can be a lot easier to adjust and revise views in Tableau for new data or new views.

             

            Another difference is immediately visible in the user interfaces of the two applications. The Home ribbon of Excel that a user first sees 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 sometimes seem like 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://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

            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/

            • 3. Re: absolute reference
              alex macharia

              Thank you for such a comprehensive answer Jonathan--you went above and beyond and everything you mentioned was very relevant.

              Thanks once again for sharing your knowledge and expertise

              On the community forum they should have a red color pill for -- comprehensive answers like yours

              • 4. Re: absolute reference
                Jonathan Drummey

                Aww, thanks for the compliments, and I'm glad it was helpful!