3 Replies Latest reply on Aug 16, 2016 2:42 PM by Joe Oppelt

# How to use excel sheet as a lookup table in the tableau calculations

Dear tableau experts,

I hit a road block with the requirement i have.I need your valuable suggestions to proceed further. Please find the xls and tableau report attached to this discussion.

I am working on six sigma Quality control charts.My report should consist of Yield from abridged table(Xls sheet).The yield can be decided on the DPMO field.The DPMO field is already calculated.

Say for example if DPMO is 100 then Yield is 99.9900%(This yield if fetched from the Xls sheet)

If DPMO is 28750 then Yield is 97.1300%

• ###### 1. Re: How to use excel sheet as a lookup table in the tableau calculations

Can't open your workbook, but I can open the excel sheet.

Your example has 60 rows.  Is that the actual table?  Or will there actually be more rows?  And will the table change?  Or will it be static?

If static and limited, I would create a calc rather than a table.

IF [DPMO] <= 3.4 then .999997 elseif

[DPMO] <= 5 then .999995 elseif

.

.

.

[DPMO] <= 900000 then .1 elseif

[DPMO] <= 902000 then .08 else 0 END

(And a parallel calc to load up [Sigma].)

• ###### 2. Re: How to use excel sheet as a lookup table in the tableau calculations

Hi Joe,

Many thanks for the response.Please find the packaged workbook developed in 9.1

The solution provided is really helpful but excel abridged table(xls) might grow depending on my DPO.

So what the solution we decided on it to calculate the Yield based on the DPO and DPMO fields.

Thanks

V

• ###### 3. Re: How to use excel sheet as a lookup table in the tableau calculations

I thought I could make this happen, but the ideas I tried didn't do it.

The problem is that you have to blend the sigma table with the data set.  I added a column to the sigma table with a value of 1 for all rows.  And added a calc to the data table with a value of 1.  That way all sigma rows would get connected to all data set rows.  Then what I had hoped to do was find the row in the sigma table that had the minimum value that was still larger than the DPMO value from the data set.

The idea failed because you have to pass aggregate values between data sources, and therefore you can do a MIN of a value that is already aggregated.

The next attempt was to approach this with the sigma table as the primary data source, but in the end I ran into the same limitations.

If I were faced with this issue I would ETL the data source data before hand to match up DPMO with Sigma levels.