8 Replies Latest reply on Feb 11, 2014 2:42 AM by L M

# lookup function issue

Dears Sirs, I'm a newbie and I was trying Tableau Desktop for a simple calculation, but there is something I don't understand.

In my example (see table) I have a calculated field '%' which is defined as '[s]/[i]' with number format as percentage with 2 decimal values.

I have another calculated field 'increment' which is defined as

ZN(SUM([%])) / LOOKUP(ZN(SUM([%])), -1) -1

, computed using "Pane Down" and with number format as percentage with 2 decimal values.

I don't understand how it gets value '4.05':

9.81 / 9.43 -1 = 0.040296

so I should get 4.03.

This is my table:

si%increment
221882353399.43%
230612350869.81%4.05%

Is there anything I don't understand?

Thanks

• ###### 1. Re: lookup function issue

If you can post a sample packaged workbook (.twbx) example, I'd be happy to look over your work.  Its very difficult to know whats going on without seeing the actual data/workbook.

• ###### 2. Re: Re: lookup function issue

Hi Matthew,test file is attached.

Thanks!

• ###### 3. Re: Re: lookup function issue

The reason you are getting this is that the % values have been rounded to two decimal places.  The raw % values are actually different (like 9.4281.... and 9.8096....) and when the raw values are divided, the result is what you are seeing.

• ###### 4. Re: Re: lookup function issue

Thanks Matthew, sorry for my silly question!

• ###### 5. Re: lookup function issue

Here's a version where I've explicitly rounded your percentage calc--I also changed your calculation to be:

SUM(i)/SUM(s)

Which is what you really wanted, I believe.  WIth the % field explicitly rounded to 2 decimal places (actually 4, since we're dealing with a decimal/percentage value here), the calculation will result in 4.03% as you expected.  Example attached.

But you were getting the correct results before--4.05% is actually the accurate number based on the raw values in your data.

• ###### 6. Re: lookup function issue

Thanks.

I don't understand very well what's the difference between a measure defined as

[i]/[s]

and

sum[i]/sum[s]

The second one, how it could be referred to a single row, instead of a partition?

• ###### 7. Re: lookup function issue

if you have row level data, sum(i)/sum(s) is same as [i]/[s]. But when you aggregate your data, sum(i)/sum(s) will first count sum(i) and sum(s) and after that divide sum(i) with sum(s). So you will get weighted average.

If you use  [i]/[s] and aggregate your data, it will just sum those [i]/[s] together from each row. If you select avg instead of sum, you will get average of averages.

• ###### 8. Re: lookup function issue

Thanks