14 Replies Latest reply on Feb 11, 2016 8:17 AM by Alexander Mou

# Calculated Field to capture another row's value

Hi I'm wondering whether this is doable in Tableau:

Let's say my data looks like the first three columns shown below. Then how can I add a calculated field that captures the first non-null Code from the third column for the same ID across  the months?

ID_noMonthCodeResults Wanted
a0NullEX
a1NullEX
a2EXEX
a3NullEX
a4NullEX
b0NullLS
b1LSLS
b2NullLS
b3NullLS
b4OPLS

Cynthia

• ###### 1. Re: Calculated Field to capture another row's value

You could use this:  {FIXED [Id No]:MIN([Code])}

• ###### 2. Re: Calculated Field to capture another row's value

Hi Daniel!

Good to see out and about in the community again!

I have a feeling that {FIXED [Id No]:MIN([Code])} will result in a MIN(code) alphabetically and not literally the 1st occurence. So if LS corresponds to month 4 and OP corresponds to month 1 then your formula would still yield LS not OP. I don't have access to Tableau right now so I can't really test anything. But just by looking at it,  feel like this wouldn't work.

• ###### 3. Re: Calculated Field to capture another row's value

Thank you for keeping me honest Pooja...and good to be back

I was trying to do round hole/square peg and make LoD work.  May need to play with it but this will work assuming you don't find something else I missed or can write it more eloquently than I.

You will need to write the following table calc's:

WINDOW_MIN(IF NOT ISNULL(ATTR([Code])) THEN INDEX() END)  -- This one will give us the first instance of a non null

LOOKUP(ATTR([Code]),[c.calc2]-INDEX()) -- This will lookup up the code from the above calc

You'll need to edit the last calc when you put it on the text shelf:

You'll need to go into advanced for both calc's then set addressing to id, month, code and finally restarting at id:

• ###### 4. Re: Calculated Field to capture another row's value

Check out this to see if it works for you

https://public.tableau.com/views/FirstValueasMonthlyValue/Sheet1?:embed=y&:display_count=yes&:showTabs=y

Use Tableau Public to open it.

Created two calc fields, both compute using Month:

[Min Month]

WINDOW_MIN( if Not ISNULL( ATTR( [Code] )  )then ATTR( [Month] ) END)

[Month_Code]

WINDOW_MAX(if [Min Month] =ATTR([Month]) then ATTR( [Code] )END)

• ###### 5. Re: Calculated Field to capture another row's value

haha that explanation turned out to be longer than expected

• ###### 6. Re: Calculated Field to capture another row's value

IF [Month] = IF NOT isnull([Code]) then { fixed [ID_no] : min([Month]) } END then [Code] end

• ###### 7. Re: Calculated Field to capture another row's value

I tried something like that (several flavors) but couldn't get it to work with LoD.  I was hoping yours worked but it got the same null result as I was getting.

Still wondering if LoD is possible or even worth it over doing a table calc in this scenario.

• ###### 9. Re: Calculated Field to capture another row's value

hmm thanks for testing that. I feel like table calc is such an overkill for a simple ask. Maybe something like this?

IF [Month] = {FIXED [ID no] : MIN(IF NOT ISNULL([Code]) then [Month] END) } THEN [Code] END

If not, I will fiddle with it tomorrow. But I do feel like, LOD would be the way to go!

• ###### 10. Re: Calculated Field to capture another row's value

Looks like table calc is unavoidable as Alexander Mou was able to do it using both LoD/table calc.

I don't see a benefit of one over the other so it's really dealers choice IMHO.

• ###### 11. Re: Calculated Field to capture another row's value

Cynthia (and all involved)... I have an LOD solution that works for your use case. Just a little tricky because it involves nesting LOD calcs, To keep myself sane, I created the calculations in two steps.

EDIT: Hadn't seen Alexander's solution, which uses LOD/table calculation hybrid - also a viable solution

Step 1

Create an LOD calculation [First Month] that returns the First Month with a non-NULL Code for any ID. The formula is:

{ FIXED [ID no] : MIN(IF NOT ISNULL([Code]) THEN [Month] END) }

Step 2

Create a 2nd LOD calculation [First Code] that returns the Code of the First Month. The formula:

{ FIXED [ID no] : MIN(IIF([Month] = [First Month], [Code], NULL)) }

Note that MIN could also be MAX, assuming there is only one non-NULL Code for any given ID no and Month. It is just returning the Code for the month of the first LOD calculation [First Month].

Step 3

Verify the results.

Workbook attached,

-Steve

• ###### 12. Re: Calculated Field to capture another row's value

Steve,

Good trick of using LOD to replace the window function in the second step!

It does the same thing.

• ###### 13. Re: Calculated Field to capture another row's value

Thanks Steve! This is exactly what I want. Smart to make it nested. I had similar idea and was trying to look for LOD calculations that work for dimension but I didn't realize that MIN or MAX can work when there's NULL value.

Daniel and Alex, thanks for the help! I haven't used WINDOWS_ calculation before, but since I was trying to create a calculated field for future filtering/category instead of a calculation inside the table, so I assume table calculation won't work. Is that correct?

Cynthia

• ###### 14. Re: Calculated Field to capture another row's value

Table calc filters work fine.

Depends on what you want to.

On Thursday, February 11, 2016, Cynthia Wang <tableaucommunity@tableau.com>