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

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.

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

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:

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

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

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.

Regards,

KV

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

Hi Kiran,