6 Replies Latest reply on Dec 28, 2015 12:53 PM by Baris Sagiroglu

# Assign value to a cell based on a different cell in the same column

Hi,

I'm trying to create a table that would show the Quantity by Level by Date. In the attached workbook, the "As is" sheet shows the data without any manipulation. The "Using multiples" sheet tries to update the Level0 and Level1 values based on the value from Level2. As you can see, using "exclude"/"include" I managed to do this but it fails when Level0 or Level1 is null when you filter the data. For example, in below table Level0 and Level1 are calculated by multiplying Level2 value by 3 and 1.5 respectively. For April because there was no value for Level1, the calculation of Level2Value*1.5 doesn't happen.

 Date Level December January February March April May Level0 63.0 9.0 3.0 39.0 6.0 Level1 31.5 4.5 1.5 19.5 Level2 21.0 3.0 1.0 13.0 2.0 Level3 186.0 14.0 83.0 65.0 Level4 90.0 160.0 3.0 Level5 383.0
• ###### 1. Re: Assign value to a cell based on a different cell in the same column

Merhaba Baris,

IF INDEX()=1 then LOOKUP(SUM([Quantity]),2)*3 elseif INDEX()=2 then LOOKUP(SUM([Quantity]),1)*1.5 else SUM([Quantity])  end

Click on the Pill, then from the context menu select Compute Using > Level

• ###### 2. Re: Assign value to a cell based on a different cell in the same column

Merhaba Bora,

Thank you for the help. I'm a newbie so could you provide more detail? Should I use your formula in the same calculated field I've created or new field I should create? Thanks.

• ###### 3. Re: Assign value to a cell based on a different cell in the same column

You need to replace the existing calculated field that returns the values like 63.0 etc. in your table with this new formula.

• ###### 4. Re: Assign value to a cell based on a different cell in the same column

Hmm, it fixes some columns but not all (please see below; May column). I think I can solve this issue if I could push Tableau to have 0 (zero) in the cells instead of Null. Any ideas? Thank you.

• ###### 5. Re: Assign value to a cell based on a different cell in the same column

Yes. That should do the trick.

• ###### 6. Re: Assign value to a cell based on a different cell in the same column

Awesome. I'll have to do some QA but I think you answered my question; will mark once QA is done. Thank you.