5 Replies Latest reply on Jun 19, 2014 9:04 AM by Kiran v Branched from an earlier discussion.

    absolute reference in tableau like in excel

    alex macharia

      Matthew Lutton Jonathan Drummey

      I have been able to answer my original question--how can you perform an absolute reference calculation in tableau as you would in excel.
      In excel you reference a cell, e.g B2 in the example below by $B$2. You can then click on the plus sign to apply the B2 as the absolute reference down the table.

      screenshot absolute reference.PNG.png

      In Tableau, you can achieve the same thing by:

      1. Summing the cell reference- in this case B2 or 2,000 along table(down)

          Total(sum(IIF([Attempt]="First time renewal",[Totals],0)))

       

        2. Dividing the success column with the absolute reference column

            Sum([Success])/[First time renewal]

          Note that First time renewal above = Total(sum(IIF([Attempt]="First time renewal",[Totals],0)))

       

      I have attached the tableau and excel worksheets

      Thanks

        • 1. Re: absolute reference in tableau like in excel
          Matt Lutton

          This is very good work, particularly if you are new to Table Calcs.    Note that you can remove the First Time Renewal calc from the view, and you will still get accurate results:

           

          Untitled.png

          • 2. Re: absolute reference in tableau like in excel
            Jonathan Drummey

            Hi Alex,

             

            I'm glad you figured it out! That's definitely a way to get there, and probably the way I'd approach it because it's explicitly referencing the value of the Attempt dimension, so if the sort of the view changes the table calc results wouldn't change. One optimization would be to put the IIF([Attempt]="First time renewal",[Totals],0) into a separate calculated field, that way if you are using a Tableau data extract that field will be materialized in the extract and be faster.

             

            There are often multiple routes to the same solution, here are three others:

             

            1. Another way to get a specific reference within a partition is to use the LOOKUP() function, like:

             

            LOOKUP(SUM([Totals]),FIRST()) with a Compute Using of Attempt or Table (Down) would return the value of SUM([Totals]) for the first row (address) in the partition.

             

            2. In this case, where you're wanting to return the value of the first row to every other row, another option would be to use:

             

            PREVIOUS_VALUE(SUM([Totals]))

             

            Again with a Compute Using of Attempt or Table (Down).

             

            3. You could also set up a data blend with a duplicated data source, where the secondary source is filtered to just the First time renewals and Attempt is not one of the linking fields. Then you could use the Totals from the secondary source with an accurate result for every row. I've used this at times when table calculations would be too slow.

             

            Cheers,

             

            Jonathan

            • 3. Re: absolute reference in tableau like in excel
              Kiran v

              Hi Jonathan,

               

              Can I use your logic in this case please see my explanation below.

               

              I am trying to solve an error problem which I have done in excel a few times in the past but can't get it to work in tableau. Mock data consists of days out which goes out 118 days out. So when it comes to day 0 that means 'on the day of '.


              Units sold- # of units sold e.g. on day 5, 121 units were sold

              projected units to be sold- is my forecasted units to be sold. e.g. day 5 i am forecasting 198

              then i have deviation and absolute of that


              Where I am stuck is the absolute error. What I am looking for is:-


              Day 5- 198 forecast units and it should refer to what was actually sold ' on the day of ' which is 168 units

              hence 198- 168= 30 units variance

              then that 30/168= 17.8% is my forecast error


              And this should apply to all days in the ' projected units to be sold ' refer to the final number on day '0' which in this case is 168 in units sold. so all numbers in forecast column to one final number in units sold.


              Please any help is appreciated.


              Regards,

              KV

              • 4. Re: absolute reference in tableau like in excel
                Vaishali Kumar

                Hi Kiran,

                 

                I have replied to your original thread.

                 

                Thanks,

                Vaishali

                • 5. Re: absolute reference in tableau like in excel
                  Kiran v

                  Thank you for the solution.